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

[Dean C]

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]

Nice job with that one Xenon.

[Xenon]

thx Matt

[sabret00the]

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]

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

[cinq]

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]

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]

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]

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

[cinq]

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 ?