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)

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

Nice job with that one Xenon. ;)

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

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

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()".

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'

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.

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

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

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (8)bbcode_php_printable (1)bbcode_quote_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
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01160 seconds Memory Usage 1,765KB 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 (8)bbcode_php_printable (1)bbcode_quote_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
Messages: