PHP DB - populating multi-dimensional array

Author

populating multi-dimensional array

Matt Anderton

2007-04-11, 7:57 am

long-time reader, first-time poster.

I am trying to use PEAR's 'hierselect' in HTML_Quickform.

I have category and subcategory tables. I want to populate a
drop-down based on category, then populate the subcategory based on
the user's category selection.

mysql> desc category;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| cat_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc subcategory;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sc_id | tinyint(4) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| cat | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

here's what I *THINK* the code should look like:
****************************************
****************************************
********************

9 $form = new HTML_QuickForm('catForm','POST');
10
11 $form->addElement('header', null, 'Add a New Category');
12
13 $main = array();
14 $secondary = array();
15
16 $query1 = "SELECT * FROM category";
17 $result1 = $db->query($query);
18 while ($result->fetchInto($row1)) {
19 $main[($row1[0])] = $row1[1];
20 }
21
22 for ($i = 0; $i < count($main); $i++) {
23 $query2 = "SELECT * FROM subcategory WHERE cat = " . $i;
24 $result2 = $db->query($query2);
25 $while ($result2->fetchInto($row2)) {
26 $secondary[$i][] = $row2[1];
27 }
28 }
29
30 $sel =& $form->addElement('hierselect', 'cats', 'Categories: ');
31 $sel->setMainOptions($main);
32 $sel->setSecOptions($secondary);
33 $form->addElement('submit', 'btnSubmit', 'Submit');
34
35 $form->display();
****************************************
****************************************
************************

but I keep getting "unexpected '[' on line 25"

is there a problem with populating the $secondary array that way?

thanks,
matt

Roberto Mansfield

2007-04-11, 6:58 pm

Matt Anderton wrote:
> long-time reader, first-time poster.
>
> I am trying to use PEAR's 'hierselect' in HTML_Quickform.
>

A few things:

1. SQL style: avoid "SELECT *" and list your field names in the specific
order you want them. What you have works, but you are assuming the field
order in your statement. It isn't self documenting either. In a w

,
month, year -- will you or the next programmer know what fields *
represents?

2. This may be a typo in your post, but line 25 uses "$while" instead of
"while". Also in line 17, your use $result1, but just $result in the
next line.

3. For clearer code, use the same field name for foreign keys in your
tables. So in subcategory, use "cat_id" instead of "cat". It is also a
good idea to keep the field types identical when matching on keys.

3. Your code would be cleaner with a join:

$query1 = "SELECT cat.cat_id,
cat.name AS cat_name,
subcat.sc_id,
subcat.name AS subcat_name
FROM category cat,
subcategory subcat
WHERE subcat.cat = cat.cat_id
ORDER BY cat.name, subcat.name";

$result = $db->query($query);
while ( $result->fetchInto($row) ) {
$main[$row[0]] = $row[1];
$secondary[$row[0]][$row[2]] = $row[3];
}

Hope that helps.
Roberto

Sponsored Links