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

[cinq]

Here's something I need help on :


# 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 (https://vborg.vbsupport.ru/showpost.php?p=591355&postcount=31), 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]

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:


SELECT categoryid, title FROM category_table WHERE parentid = $categorid


Then look through it like so:


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:


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

# 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 :D

But to understand the code better, could you explan what this bit does :

$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]

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]

Change this:

while ($cat = $DB_site->fetch_array($categories))
{
$cat_cache[$cat['parentcategory']][] = $cat;
}


To This:


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]

..
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]

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 :


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

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
$author_cache['author']
is not filled like that.

$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.
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]

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

If i were to perform another :


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]

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]

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

[cinq]

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.


$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]

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