php mysql alternative to using same query again inside a loop

Question

Possible Duplicate:
Is it possible to query a tree structure table in MySQL in a single query, to any depth?

I have an admin area I created that pulls data from the mysql database using php and display the results in a table. Basically it shows a parent category, then the first sub category below it, then the third level sub category/subject.

It works perfectly but as I am new to mysql and php I am sure that it the code needs to be improved in order to save db resources as while building the table I use 3 while loops and in each loop make a mysql query which I am sure is the wrong way to do it.

Can somebody offer me some assistance for the best way of doing this?

Here is the code:

           $query = mysql_query("SELECT * FROM categories WHERE
parent_id is null
order by cat_id asc;", $hd)
or die ("Unable to run query");       

while ($row = mysql_fetch_assoc($query)) {
echo '<tr style="font-weight:bold;color:green;"><td>'. $row    ['cat_id'].'</td><td>'.$row['cat_name'].'</td><td>'.$row    ['parent_id'].'</td><td>'.$row['active'].'</td><td>'.$row    ['url'].'</td><td>'.$row['date_updated'].'</td></tr>' ;

$query2 = mysql_query("SELECT * FROM categories WHERE
                (active = 'true' AND parent_id = ".$row    ['cat_id'].")
                order by cat_id asc;", $hd)
      or die ("Unable to run query");
while ($row2 = mysql_fetch_assoc($query2)) {
echo '<tr style="font-weight:bold;"><td>'. $row2['cat_id'].'</td><td>'.$row2    ['cat_name'].'</td><td>'.$row2['parent_id'].'</td><td>'.$row2    ['active'].'</td><td>'.$row2['url'].'</td><td>'.$row2    ['date_updated'].'</td></tr>' ;
    $query3 = mysql_query("SELECT * FROM categories WHERE
                (active = 'true' AND parent_id = ".$row2    ['cat_id'].")
                order by cat_id asc;", $hd)
      or die ("Unable to run query");
      while ($row3 = mysql_fetch_assoc($query3)) {
echo '<tr><td>'. $row3['cat_id'].'</td><td>'.$row3['cat_name'].'</td><td>'.$row3    ['parent_id'].'</td><td>'.$row3['active'].'</td><td>'.$row3    ['url'].'</td><td>'.$row3['date_updated'].'</td></tr>' ;

}
}
}

EDIT

Ok so I did a bit of research and this is where I am:

Probably for a small database my approach is fine.

For a bigger database using an array to store the data would probably mean I need to use a recursive approach which might use up too much memory. Would love to hear what people think, would it still be better than looping db queries in the nested while loops?

I found the following thread where there is an answer to do this without reccursion and with only one query. Not sure if I need to add a position column to my current design: How to build unlimited level of menu through PHP and mysql

If I rebuild the design using the nested sets model instead of adjacency model then the mysql query would return the results in the required order however maintaining the nested sets design is above my head and I think would be overkill.

That's it. If anyone has any input on top of that please add to the conversation. There must be a winning approach as this kind of requirement must be needed for loads of web applications.

http://stackoverflow.com/questions/169817/is-it-possible-to-query-a-tree-structure-table-in-mysql-in-a-single-query-to-an — triclosan, Nov 29 '12 at 19:35

score 1 · Answer 1 · answered Nov 29 '12 at 19:43

I would think you could do something like this:

SELECT * FROM categories
WHERE active = 'true'
ORDER BY parent_id, cat_id

This would give you all your categories ordered by parent_id, then by cat_id. You would then take the result set and build a multi-dimensional array from it. You could then loop through this array much as you currently do in order to output the categories.

While this is better from a DB access standpoint, it would also consume more memory as you need to keep this larger array in memory. So it really is a trade-off that you need to consider.

Thanks for the answer, regarding the array, I thought of that but also thought of the fact that I would be using memory instead of lots of queries. — Allon, Nov 29 '12 at 21:15

lcHatter · Answer 2 · 2012-11-30T16:37:14.597

0

There is a lot to fix there, but I'll just address your question about reducing queries. I suggest getting rid of the WHERE clauses all together and use if statements within the while loop. Use external variables to hold all the results that match a particular condition, then echo them all at once after the loop. Something like this (I put a bunch of your stuff in variables for brevity)

//before loop
$firstInfoSet = '';
$secondInfoSet = '';
$thirdInfoSet = '';

//in while loop

if($parentID == NULL)
{
  $firstInfoSet.= $yourFirstLineOfHtml;
}

if($active && $parentID == $catID) // good for query 2 and 3 as they are identical
{
  $secondInfoSet.= $yourSecondLineOfHtml;
  $thirdInfoSet.= $yourThirdLineOfHtml;
}

//after loop
echo $firstInfoSet . $secondInfoSet . $thirdInfoSet;

You can now make whatever kinds of groupings you want, easily modify them if need be, and put the results wherever you want.

--EDIT-- After better understanding the question...

$query = mysql_query("SELECT * FROM categories order by cat_id asc;", $hd);
$while ($row = mysql_fetch_assoc($query)){
   if($row['parent_id'] == NULL){
      //echo out your desired html from your first query 
   }
   if($row['active'] && $row['parent_id']== $row['cat_id']){
      //echo out your desired html from your 2nd and 3rd queries
   }
}

edited Nov 30 '12 at 16:37

answered Nov 29 '12 at 20:21

lcHatter

120
9

Thanks for the answer, I am not sure I understood. Also from what I could understand, it looks like I would then only show each info set one after the other when I need to display the parent ID, then display the first sub cat under that, then iterate through the sub cats (third level) of that, then when they end check if there are more categories under that parentID etc... Not sure if schoing it all at once would do that? – Allon Nov 29 '12 at 21:10
Sorry, missed that the whiles were nested. Just put the echo lines in each of the if statements. No need to group them up. – lcHatter Nov 29 '12 at 21:31
Ok, I think I get it. So you are saying that I should access the DB once and then loop through the results? This would use more memory as mentioned above and also I would need to place it all in an array as using mysql_fetch_assoc would not work as I would need to keep going up and down the rows results. Maybe I misunderstood? – Allon Nov 29 '12 at 21:38
You were already looping through the results, three times. This way you only loop through them once. See Edit – lcHatter Nov 30 '12 at 16:37
Thanks, this seems to work if I have only 2 levels but when doing three levels I still cannot get it to work even when running three different queries prior to looping. Without looping I cannot get it to work at all... Are you sure what you suggested should work? I can post my updated code if you have the patience to have a look :) – Allon Dec 01 '12 at 01:44

php mysql alternative to using same query again inside a loop

2 Answers2