Tom M
01-01-2005, 05:21 PM
I'm integrating/converting an existing review system I have from Perl to PHP in order to integrate it with 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.
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?
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?