Mysql query parent_id order problem

SELECT * FROM `websiteadmin_pm_categories` WHERE `username`='demo' LIMIT 0, 30 ; 

Output now:

id parent_id active_en name_en description_en link_en username
47 0 1 DVD     demo
48 0 1 Spill     demo
49 0 1 Utstyr     demo
50 49 1 PC     demo
51 47 1 Barnefilmer   demo

Should be:

    id parent_id active_en name_en description_en link_en username
    47 0 1 DVD     demo
    51 47 1 Barnefilmer   demo
    48 0 1 Spill     demo
    49 0 1 Utstyr     demo
    50 49 1 PC     demo

The problem here is ID and PARENT_ID ,t开发者_高级运维he PARENT_ID should come out under the ID they belong to.

I am not sure if such a solution is possible. I would be quite surprised to see if this is implemented by default in any database.

THis kind of solution should be present in the Display logic rather than queries, as the data is independent of the relation between ID and PARENT_ID

This query does exactly what you want it to, but this one only does a one-level parent/child hierarchy... if you want deeper nesting, it gets more complex, but CAN be done to a third level (I actually did this earlier today).

select *
   from 
      websiteadmin_pm_categories w1
         left join websiteadmin_pm_categories w2
            on w1.parent_id = w2.id
   WHERE
      w1.username = 'demo'
   order by
      case when w1.parent_id = 0 then w1.id else w1.parent_id end,
      w1.parent_id