select rows where parent = id

[asmith20002]

Hi,

I have a table like this :

id name parent
1 sport 0
2 football 1
3 basketball 1
4 food 0
5 pizza 4
6 pasta 4


I want to make menu out of them. So that parent 0 will be the main item in the menu and others will be the sub menus.

I want to know, How can I select them so that It gives me the parent 0, then its sub menus, then gives me the next parent 0, then its sub menus.

I can do it with 2 queries. for example

select * from table where parent = 0
then in the while loop with php, I put :
select * from table where parent = $row['id']

But it will make so many queries.

I want to know if I can get it all only by one query.

Possible?
Thanks

[tattooedscorpdc]

Yes this is possible... look into creating a stored procedure that runs these loops for you --or-- make a series of drop boxes that will populate with each selection... so when someone selects "Sports" form the primary drop box have an AJAX call go out to the server and then populate the secondary drop box with the appropriate parent... this query of course would have a dynamic value in the WHERE clause

Hope this helps

Larry D
MS Architect Evangelist

[Phill Pafford]

I did see this for MySQL, it could help: The Link (http://forums.mysql.com/read.php?52,71061,198134#msg-198134)