Order the rows of a MySQL result based on a "next_id" field

I'm currently working with a database table that is structured as follows:

 ______________________________
|  id  |  content  |  next_id  |
|------|-----------|-----------|
|  1   |  (value)  |     4     |
|  2   |  (value)  |     1     |
|  3   |  (value)  |   (NULL)  |
|  4   |  (value)  |     3     |
 ¯¯¯¯¯¯¯¯¯¯¯开发者_如何学C¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

The value of the next_id field defines the id of the row of data that should follow it. A value of NULL means that no row follows it.

Is there a way I can query the database in such a way that in the resulting rows will be ordered using this method? For example, in the case I gave above, the rows should be returned ordered so that the ids are in this order: 2, 1, 4, 3. I'm looking for a solution that can do this regardless of the number of rows in this sequence.

I know that it is possible to reorder the results after retrieving them from the database (using the programming language I'm working with), but I'm hoping that there is a way that I can do it in SQL.

I can't see a solution without as many self-joins as you have rows. Instead I would build a nested set out of it in a temp table using push down stack algorithm and then retrieve a full tree.

I've got something that's close.

/*one select to init the @next variable to the first row*/
select @next:= id from table1 order by isnull(next_id) asc, next_id asc limit 1;


select distinct a.id, a.next_id from table1 b
  inner join
  (
    select @rank:= id as id, @next:= next_id as next_id from table1
    where id = @next
  ) a
  on (b.id = b.id);

This outputs

+----+---------+
| id | next_id |
+----+---------+
| 2  | 1       |
| 1  | 4       |

And then stops. If only I could find a way for it to continue....

Anyway this sort of force feeding values into a query is dodgy enough when doing ranking, let alone this sort of stuff, so maybe I'm going down a dead end.