vb.org Archive
Page 1 of 3 1 23
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Query Loop within Query Loop .... Need Optimization help (https://vborg.vbsupport.ru/showthread.php?t=74049)

cinq 01-09-2005 05:55 AM
Query Loop within Query Loop .... Need Optimization help
 
Here's something I need help on :

PHP Code:
# Categories and subcategories column
$parentcatquery = $DB_site->query("SELECT * FROM " . TABLE_PREFIX . "articles_category WHERE parentcategory=0 ORDER BY categoryorder");
if ($DB_site->num_rows($parentcatquery)>0)
{
    while($parentcatrow=$DB_site->fetch_array($parentcatquery))
    {
        $parentid = $parentcatrow["articles_categoryid"];
        $catname = $parentcatrow["categoryname"];
        $parentcat = $parentcatrow["parentcategory"];

        eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit') . '";');
        eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit_pre') . '";');

           $subcatquery = $DB_site->query("SELECT * FROM " . TABLE_PREFIX . "articles_category WHERE parentcategory = $parentid ORDER BY categoryorder");
        if ($DB_site->num_rows($subcatquery)>0)
        {
            while($subcatrow=$DB_site->fetch_array($subcatquery))
            {
            $subcatid=$subcatrow["articles_categoryid"];
            $subcatname=$subcatrow["categoryname"];
            eval('$catlistbit .= "' . fetch_template('vbArticles_childcatbit') . '";');
            }
        }
    eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit_post') . '";');
    }
} 
I am trying to get the categories and then the subcategories of the parent category to display.
Is there anyway I can change things around to optimize what I am trying to do ?

My logic for the code above is:

I get the parent categories first ( those with parentcategory=0 meaning they are the parent categories already ), and for each of those parentcategories, query again for those categories whose parentcategory = the id of those parent categories.

Obviously this isn't as efficient as it can be.
As it is, this is probably the code portion contributing to the massive amount of queries on the page.

Any help would be most appreciated.

cinq 01-09-2005 05:57 AM
A possible solution was offered by rake here, but I don't quite get it.

A solution could potentially teach me how to deal with handling queries in nested while loops ( which I keep using now in most of my hacks because I do not know otherwise ..... )

Could anyone take the time to explain this to me or offer a better solution to my code, please ?

Thanks ! :D

Dean C 01-09-2005 10:58 AM
You're best off storing a cache of the subcategories in your article_category table :)

cinq 01-09-2005 11:01 AM
I don't understand Dean :(

Dean C 01-09-2005 11:22 AM
Ok do you know how serialization works? Basically when managing your categories from the admincp you run a query like this:

[sql]
SELECT categoryid, title FROM category_table WHERE parentid = $categorid
[/sql]

Then look through it like so:

PHP Code:
while($subcats = $DB_site->fetch_array($query))
{
       $array .= $subscats;
}

$subcats2 = serialize($array);

$DB_site->query("UPDATE category_table SET subnodes='" . addslashes($subcats2) . "' WHERE categoryid=$categorid"); 

cinq 01-09-2005 12:08 PM
Still not too sure what you mean, even after going through php.net's serialize function.
Will read up more on this first, thanks for pointing me in some direction :)

Dean C 01-09-2005 12:28 PM
Well basically it takes an array and stores it as a string. You can place the string in the database and then unserialize it back into an array once you've pulled it back out the database. vB3 uses it in a lot of places so it's a good idea to read up a bit on how it works :)

Just write a test script:

PHP Code:
$array = array('yes', 'no', 'why', 'word');
$temp = serialize($array);
echo $temp;
$temp = unserialize($temp);
print_r($temp); 

Xenon 01-09-2005 01:45 PM
take a look at that code, that should do what your code di, but just using one query:

PHP Code:
# Categories and subcategories column
$parentcatquery = $DB_site->query("
    SELECT *
    FROM " . TABLE_PREFIX . "articles_category
    WHERE parentcategory=0 ORDER BY categoryorder
");
$categories = $DB_site->query("
    SELECT *
    FROM " . TABLE_PREFIX . "articles_category
    ORDER BY categoryorder
");
$cat_cache = array();
while ($cat = $DB_site->fetch_array($categories))
{
    $cat_cache[$cat['parentcategory']][] = $cat;
}
// parent categories
foreach ($cat_cache[0] AS $parentcatrow)
{
       $parentid = $parentcatrow["articles_categoryid"];
        $catname = $parentcatrow["categoryname"];
        $parentcat = $parentcatrow["parentcategory"];

        eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit') . '";');
        eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit_pre') . '";');

        // now do subcategories
        foreach ($cat_cache[$parentid] AS $subcatrow)
        {
            $subcatid=$subcatrow["articles_categoryid"];
            $subcatname=$subcatrow["categoryname"];
            eval('$catlistbit .= "' . fetch_template('vbArticles_childcatbit') . '";');
        }
    eval('$catlistbit .= "' . fetch_template('vbArticles_categorybit_post') . '";');
} 

cinq 01-09-2005 02:00 PM
It works ! Thanks Stefan :D

But to understand the code better, could you explan what this bit does :
PHP Code:
$cat_cache = array();
while ($cat = $DB_site->fetch_array($categories))
{
    $cat_cache[$cat['parentcategory']][] = $cat;
} 
How does the $cat_cache array look like ? I find it hard to visualize :(

p.s. 36->11queries, 41->13queries, woot ! :D

Xenon 01-09-2005 02:09 PM
the $cat_cache array is a cache of all categories you have or better its an array of arrays of categories and looks like that:

$cat_cache
0 => array of categories with the parentid 0
1 => array of categories with the parentid 1

and so on.


All times are GMT. The time now is 10:57 AM.
Page 1 of 3 1 23
Show 40 post(s) from this thread on one page

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.01110 seconds
  • Memory Usage 1,772KB
  • 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
  • (5)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)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
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete