Show data sorted by parent objects

Question

Hey guys,

i have a problem and don't really know, what to search for to find a solution.

Description:

I have a mysql table with the following colums "id", "name", "value", "parent" with "id" as primary key. "id" is a autincreament number, "name" is the name of the category, "value" is the value of the category and "parent" is the parent name(category) for this row. How can i show this table in the correct order, means sorted by their parent name.

example:

sql:

id | name | value | parent

1 | n1 | bla1 |

2 | n2 | bla2 | n3

3 | n3 | bla3 | n1

html:

n1 bla1

n3 bla3

n2 bla2

Please help me out with some codesnippet or helpful link...any help apreciated. Thanks in advance for your help.

Answer 1

SELECT * FROM tableName ORDER BY parent, name;

Answer 2

You can sort any table using the ORDER BY clause in SQL. Tables have no "correct" order; they're ordered exclusively by that clause, which you must include in any query that you expect to produce results sorted a particular way. In this case you'd write:

SELECT * FROM your_table ORDER BY parent;

As a side now, you'd get more reliable results by setting parent to the id of the parent, and not the name, so that if the name ever changes you won't break the parent/child relationship. You can then use a JOIN to figure out the parent's name.

Answer 3

Select id,name,value,parent from MyTable order by parent asc

Will sort in ascending order

Select id,name,value,parent from MyTable order by parent desc

Will sort in descending order

Answer 4

thanks guys, the hint from voteydisciple got me on the right track. :D i love this site...i had 2 questions and both got answered within seconds :D