0

I have a categories table in my database with the following structure:

id | parent_id | name

Top level categories have a parent_id of 0, where as all other categories can be children of other categories. There are parents of categories, grand-parents, great-grandparents, etc.

This is my current SQL statement:

SELECT * from `categories` ORDER BY `parent_id`;

I want to see something like the following in my select box:

MOVIES
MOVIES -> ACTION
MOVIES -> ACTION -> ENGLISH

Is there an SQL statement that can handle this recursion in the categories?

user353gre3
  • 2,747
  • 4
  • 24
  • 27
Justin
  • 623
  • 1
  • 10
  • 24
  • possible duplicate of [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) – Bill Karwin Apr 27 '14 at 19:43
  • If you want to display all categories, just get all and associate with their parent. If you want to get the hierarchy, you should do a query recursively if parent != 0 ... – Loenix Apr 27 '14 at 19:46
  • no, you cannot do this in SQL. I'm reluctant to post this as an answer though. People don't like to hear the truth if its negative. you have to first fetch all the data and then build a proper recursive data structure from it. – Michael Apr 27 '14 at 20:26
  • Have a look at [**this**](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) – david strachan Apr 27 '14 at 21:28

0 Answers0