How does vb do the sub-form bits? [Archive]

tgmorris

01-01-2005, 09:06 PM

Xenon

01-01-2005, 09:41 PM

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

01-01-2005, 10:10 PM

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

01-02-2005, 05:32 AM

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)

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):

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

tgmorris

01-02-2005, 08:01 AM

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)

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):

$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

01-03-2005, 05:38 AM

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.