[Link14716]

Originally Posted by 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?

To do that, you need to have it update the "vinfo" field (create it as a TEXT column) whenever an update takes place.

First, have it make an array with all the vendor information. Let's say you had the array in the $vendor variable. You'd need to do something like this (assuming $categoryid is the category's id)

PHP Code:

mysql_query("UPDATE category SET vinfo='".addslashes(serialize($vendor))."' WHERE id = $categoryid"); 


Then, after it is retrieved, use this code to make it an array again (assuming $category is the array created from the query):

PHP Code:

$category['vinfo'] = unserialize($category['vinfo']);