Mysql: get all related rows in a table

Question

I have a table structure

like this

id | title | next 1 | test | 5 2 | test | 0 3 | test | 0 4 | test | 0 5 | test | 3 

Now that you see 1 point to the next point 5 and 5 points to the next point 3 and 3 marks the end

I need a query from which I can get 1, 5, 3 in turn in one column and their name also

as

 result | title -------------- 1 | test 5 | test 3 | test -------- 

please, help. I don’t even know how to get started with such a request.

Answer 1

What you need is a tree query - check Is it possible to query a tree structure table in MySQL in a single query at any depth?

Answer 2

What you want to do here, join the table.

 SELECT * FROM `table` AS `child` JOIN `table` AS `parent` ON `parent`.`next` = `child`.`id` 

You need to provide both copies of the table with your own alias (here: parent and child), because otherwise you will encounter unique problems.

Answer 3

One way is to create a loop that repeats a simple query. I could give an example here. Do you use PHP?