Category hierarchy (in order) using PHP MySQL

Question

I try to ORDER all categories and subcategories in the hierarchy:

The main thing is how to get them from MySQL ORDERLY (using the POSITION field)

• Cat A → position 10
  • Sub-Cat 1 → item 10
  • Sub_Sub_Cat 1 → position 20
    • Sub_Sub_Cat 2 → position 10
  • Sub_Cat 2 → position 30
• Cat B → Position 20
• Cat C → position 30

MySQL code:

CREATE TABLE IF NOT EXISTS `categories` ( `category_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `position` smallint(5) unsigned, `parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0' PRIMARY KEY (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; 

Answer 1

Do you want to cross a tree using SQL? This is not possible in the adjacency list model; you must use the nested sets model . Then you can simply ORDER BY left to get the whole tree in the correct order.