vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   How does vb do the sub-form bits? (https://vborg.vbsupport.ru/showthread.php?t=73634)

tgmorris 01-01-2005 09:06 PM
How does vb do the sub-form bits?
 
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

Code:
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 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
Quote:
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

Code:
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 01-02-2005 08:01 AM
Quote:
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 01-03-2005 05:38 AM
Quote:
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.


All times are GMT. The time now is 02:09 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01064 seconds
  • Memory Usage 1,752KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (4)bbcode_php_printable
  • (3)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (6)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete