Query Loop within Query Loop .... Need Optimization help

[cinq]

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]

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 !

[Dean C]

You're best off storing a cache of the subcategories in your article_category table

[cinq]

I don't understand Dean

[Dean C]

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]

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]

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]

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]

It works ! Thanks Stefan

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 !

[Xenon]

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.