vb.org Archive - Query Loop within Query Loop .... Need Optimization help

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.

Dean C

01-09-2005 02:13 PM

Change this:

PHP Code:


		
			
while ($cat = $DB_site->fetch_array($categories))

{

    $cat_cache[$cat['parentcategory']][] = $cat;

}

To This:

PHP Code:


		
			
while ($cat = $DB_site->fetch_array($categories))

{

    $cat_cache[$cat['parentcategory']][] = $cat;

} 

echo '<pre>';

print_r($cat_cache);

echo '</pre>';

You'll be able to see how the array looks and works better then (only for development purposes of course :)

Link14716

01-09-2005 09:26 PM

Nice job with that one Xenon. ;)

Xenon

01-09-2005 09:43 PM

thx Matt :)

sabret00the

01-09-2005 10:18 PM

you know i have one i need to optimise too, but i can't figure it out, although was thinking that i'd be easier to do it via the datastore as the query in the loop takes data provided by the loop in order to return a result and it's even got a loop in the loop :-S

it's from the vBFriends hack

cinq	01-10-2005 01:29 AM

Quote:

Originally Posted by Dean C

..
You'll be able to see how the array looks and works better then (only for development purposes of course :)

Thanks Dean, made it much easier for me to visualize :D

cinq	01-10-2005 03:39 AM

Ok using similar code, I am trying to show a list of authors, and their respective written articles.

This is the code i am trying to use :

PHP Code:


		
			
$authorquery = $DB_site->query("

        SELECT * FROM " . TABLE_PREFIX . "articles_article a

        LEFT JOIN " . TABLE_PREFIX . "user u

        ON a.author=u.username

        ORDER BY a.author asc

        ");



$artbyauthorquery = $DB_site->query("

        SELECT articles_articleid, title, author

        FROM " . TABLE_PREFIX . "articles_article

        ORDER BY publishdate desc

        ");



$author_cache = array();

while ($author = $DB_site->fetch_array($artbyauthorquery))

{

    $author_cache[$author['author']][] = $author;

}



// authors

foreach ($author_cache['author'] AS $author)

{

           $userid = $author["userid"];

        $author = $author["author"];



        // articles by the authors

        foreach ($author_cache[$author] AS $article)

        {

            $arttitle=$article["title"];

            $artid=$article["articles_articleid"];

            eval('$authorartbit .= "' . fetch_template('vbArticles_authorartbit') . '";');

        }

     eval('$authorbit .= "' . fetch_template('vbArticles_authorbit') . '";');

}



    $navbits = construct_navbits(array('' => $vbphrase['vbarticles']));

    eval('$navbar = "' . fetch_template('navbar') . '";');

    eval('print_output("' . fetch_template('vbArticles_authorlist') . '");');

}

However it gives me an error at the line :

PHP Code:


		
			
foreach ($author_cache['author'] AS $author)

Something to do with the 'author' there I think. I need to put in the value there which is the name of the authors, correct ?

foreach works with strings in the array too, so how come this is not accepted ?
error is "Invalid argument supplied for foreach()".

Marco van Herwaarden

01-10-2005 08:30 AM

Hmm didn't analyze the script, but at first glance it looks like

PHP Code:


		
			
$author_cache['author']

is not filled like that.

PHP Code:


		
			
$author_cache[$author['author']][] = $author;

Fills it like "array['name of author']" and not with the rowname 'author'

cinq	01-10-2005 10:10 AM

Thanks Marco, but nope, I don't think that would work either.
Because, the $author['author'] is the result of the query on articles by authors, not the authors query.

Marco van Herwaarden

01-10-2005 10:18 AM

Yes i know, maybe i am wrong.

PHP Code:


		
			
while ($author = $DB_site->fetch_array($artbyauthorquery)) 

{ 

    $author_cache[$author['author']][] = $author; 

}

Would give something like the following (assuming some about the author table now):
$author_cache['jim'][0] = array('jim','doe','UK',3,......)
$author_cache['john'][0] = array('john','smith','germany',4,......)

now you ask for the entry "$author_cache['author']" unless there is an author named 'author', this would give no results.

Maybe i am looking complete wrong now, but just shoot me ;) i am wearing a bullet proof vest :D

cinq	01-10-2005 10:22 AM

yep you're right. You got me thinking ..
I am trying to find a way about it.

If i were to perform another :

PHP Code:


		
			
while ($authorsname = $DB_site->fetch_array($authorquery))

{

    $authorname_cache[] = $authorsname ;

}

This would give me an array of the author's names in $authorname_cache[].
How could I use this in the foreach statement, any idea ? :)

Marco van Herwaarden

01-10-2005 10:44 AM

this would give you an array of all the rows in the author table.

You could enumerate them with a

foreach ($authorsname as $key=>$values)

this will give you the index to the array in $key and an array of associated values in $value.

You then could use a $authorsname[$key]['name'] for example, or a $value['name'].

Marco van Herwaarden

01-10-2005 12:05 PM

lol, would really not know, didn't go into the function if this.

cinq	01-10-2005 12:28 PM

This thread is really messy now :D
But anyways, thanks to everyone for pointing me in the right direction and taking the time to explain stuff :)

For the author list feature, this is the final code I am employing, just for anyone's reference.

PHP Code:


		
			
    $authorquery = $DB_site->query("

        SELECT DISTINCT a.author, u.userid

        FROM " . TABLE_PREFIX . "articles_article a

        LEFT JOIN " . TABLE_PREFIX . "user u

        ON a.author=u.username

        ORDER BY a.author asc

        ");



    $articlequery = $DB_site->query("

        SELECT a.articles_articleid, a.title, a.author

        FROM " . TABLE_PREFIX . "articles_article a

        ORDER BY publishdate desc

        ");



    $article_cache = array();

    while ($article = $DB_site->fetch_array($articlequery))

    {

        $article_cache[$article ['author']][] = $article;

    }



    $authorname_cache = array();

    while ($authorsname = $DB_site->fetch_array($authorquery))

    {

        $authorname_cache[] = $authorsname ;

    }



    foreach ($authorname_cache as $key=>$values)

    {

        $author = $values['author'];

        $userid = $values['userid'];

        $authorartbit = "";

        foreach ($article_cache[$author] AS $article)

        {

            $arttitle=$article["title"];

            $artid=$article["articles_articleid"];

            eval('$authorartbit .= "' . fetch_template('vbArticles_authorartbit') . '";');

        }

        eval('$authorbit .= "' . fetch_template('vbArticles_authorbit') . '";');

    }

49 -> 7 queries :)
pheww, finally :)

Marco van Herwaarden

01-10-2005 12:45 PM

Well done, and if i have time (and still remember ;)) i will see if i can find anymore optimisations.

All times are GMT. The time now is 04:43 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01375 seconds Memory Usage 1,864KB 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 (14)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (24)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
Messages: