[tgmorris]

I'm integrating/converting an existing review system I have from Perl to PHP in order to integrate it with vBa CMPS. At the moment I'm trying build a query that will return a list of categories and for each category there should be an array of vendors for that category. This is basicall the same as what vb does when displaying sub forums under categories. I've found the templates used but not where the query is done.

My tables are currently set up like

mysql> describe category;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     |      | PRI | NULL    | auto_increment |
| name   | varchar(50) | YES  |     | NULL    |                |
| active | char(1)     | YES  |     | N       |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe vendor;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      |      | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| url      | varchar(75)  | YES  |     | NULL    |                |
| address  | varchar(100) | YES  |     | NULL    |                |
| phone    | varchar(15)  | YES  |     | NULL    |                |
| category | int(11)      | YES  |     | NULL    |                |
| active   | char(1)      | YES  |     | N       |                |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

My existing query looks like

SELECT category.id, category.name, COUNT(vendor.id) AS count
FROM category, vendor
WHERE category.id = vendor.category
AND category.active = 'Y' AND vendor.active = 'Y'
GROUP BY category.id, category.name
ORDER BY category.name;

The existing query returns, as expected

Array ( [id] => 8 [name] => Accessories [count] => 4 )

What I'd like to see is each row that is returned contain something like

Array ( [id] => 8 [name] => Accessories [count] => 4, [vinfo] => Array ( sorted list of vendor id's and names ) )

so I don't need to then execute a bunch of additional queries to get the vendor names by category. It seems like this should be possible as it looks like vb does something similar when sub-forums are listed under categories. However I haven't had any luck in locating that code.

Any solutions or a pointer to where to find the solution?