How to get categories tree without looping queries?

Question

I have this query:

$query='select id, bg_category from products_categories where 
visible="1" and parent="0" group by bg_category order by bg_category ASC';

You can imaging from this point I was looping $num_rows going to the child category and than to its child category.. It was working anyway because the number of categories was bellow 20. But now the categories are over 130 and this way of generating category tree is waste of server resoure.. I am now thinking of changing this and looking for php solution that will execute only 1 query. I found this here: Build a tree from a flat array in PHP but couldn't understand / make it work:

$q = mysql_query("SELECT id, parent_id, name FROM categories");
while ($r = mysql_fetch_row($q)) {
$names[$r[0]] = $r[2];
$children[$r[0]][] = $r[1];
}

function render_select($root=0, $level=-1) {
global $names, $children;
if ($root != 0)
echo '<option>' . strrep(' ', $level) . $names[$root] . '</option>';
foreach ($children[$root] as $child)
render_select($child, $level+1);
}

echo '<select>';
render_select();
echo '</select>';

How is this above supposed to work? What I need to pass with render_select(); ?

Also if this solution works, would it save server resource instead of looping queries? Thank you in advance for your help

EDIT: My database has the following category structure:

#id        #bg_category      #parent
1          electronics         0
2          Phones              1
3          Smartphones         2
4          normalphones        2

Output like this:

<ul>
<li>electronics
<ul class="sublevel">
<li>Phones
<ul><li>Smartphones</li>
</ul></li></ul></li>
</ul>

and etc.. hope this helps

Can you post a few rows of sample data from the categories table and the expected output? You may be able to do this in sql and just echo the results of one query. — Brian DeMilia, Aug 03 '14 at 13:52
Brian, please see edited post, I think can not be done with 1 query — Europeuser, Aug 03 '14 at 14:02
MySQL doesn't support recursion explicitly. You have few choices. Do the looping in an application or stored procedure. Or store the path of the category from the top to a given category in each row. (Or switch to a database that supports recursive CTEs.) — Gordon Linoff, Aug 03 '14 at 14:03
How about the copied code above - do you think it is working and why I can not make it working, what I am missing? — Europeuser, Aug 03 '14 at 14:05
@Europeuser how do you want the output to look based on that data? — Brian DeMilia, Aug 03 '14 at 14:12

score 0 · Accepted Answer · answered Aug 03 '14 at 15:03

If it's always just up to 2 levels deep you may be able to use the query below. In php you could check to see if the bg_category on the current row is the same as on the previous row to either show or not show the column, same with child1 since they can have multiple child2s.

I added some additional categories and subcategories, out of order, for illustration.

I used 3 underscores indicating 1 level deep and 6 underscores indicating 2 levels deep, but you can actually put some of the html tags into the sql to have the query generate for you what you would put at each level.

This should work despite the order of the the rows in the database -- determined by the id field -- but like I said it will only work 2 levels deep.

Although it looks pretty bad, you could set up some views to simplify the query you would actually run. Of course, this still assumes you're only going 2 levels deep. If you have subcategories upon subcategories of subcategories, sql might not be the way to go.

Fiddle: http://sqlfiddle.com/#!2/630c6/18/0

select w.bg_category, w.child, x.child as child2

from(

select x.bg_category, y.bg_category as child

from(

select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) x

join

(

  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) y on x.id = y.parent

  ) w

left join

(

select x.bg_category, y.bg_category as child

from(

select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) x

join

(

  select id, bg_category, parent
from tbl
where parent = 0
union all
select id, concat('___',bg_category), parent
from tbl
where id in (select parent id from tbl)
and parent <> 0
union all
select id, concat('______',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent not in (select id from tbl where parent = 0)
union all
select id, concat('___',bg_category), parent
from tbl
where id not in (select parent from tbl)
and parent in (select id from tbl where parent = 0)

  ) y on x.id = y.parent

  ) x on w.child = x.bg_category

where substr(w.bg_category,1,1) <> '_'

Brian, thank you so much for your help ! Your answer is accepted as correct since it does the job. I am afraid my category level is over 3 and that's why I am working on another solution with php recursion. I am still testing but it is 5 times faster than the queries variants, and it has only 1 mysql.. Still I accept your answer and once again BIG thank you for helping me! — Europeuser, Aug 03 '14 at 15:08
@Europeuser no problem, unfortunately w/ mysql you don't really have the 'hiearchial retrieval' methods that are available in other databases. Even if it were to just have the with clause it would make things easier so as to avoid repetitive inline views, but even if it did it would just make it easier to handle a limited # of subcategories. Oh well, good luck :) — Brian DeMilia, Aug 03 '14 at 15:13
thanks, last questions do you thing recursive function will save more resource than hundres mysql-s? — Europeuser, Aug 03 '14 at 15:23
@Europeuser it would probably be better in php, especially if you have more than 2 levels. If it were just 2 levels you could stick some of the above sql into views and then query those views, and/or populate actual tables w/ the sql and index those tables for your final query. But it would get complicated. I'm not as familiar w/ php but I do imagine it would be simpler. — Brian DeMilia, Aug 03 '14 at 15:41

How to get categories tree without looping queries?

1 Answers1