How does vb do the sub-form bits?

[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?

[Xenon]

well, there is no real difference between categories and subforums

each forum can be a category, so to define subforums vb uses a parentid value.

[AN-net]

if your inquiring about hierarchail data storage with vBulletin then you won't really find it since vBulletin caches all parents and children when ever the forum order is updated, a forum deleted, or a new forum is added

[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']); 


[tgmorris]

Originally Posted by Link14716

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']); 


So, it sounds like I can't really collect this information "on the fly"? I need to add a field to the category table and update it each time the vendor information changes, correct? I can then unpack the vendor information when I retreive the category information.

[Link14716]

Originally Posted by tgmorris

So, it sounds like I can't really collect this information "on the fly"? I need to add a field to the category table and update it each time the vendor information changes, correct? I can then unpack the vendor information when I retreive the category information.

Well, you can collect the information on the fly, but not without another MySQL query.