Linked lists in mysql/php

[rawb]

I have a question about building a linked list structure in mysql/php.

 

I have a database laid out like:

 

id          name                next (references next row's id)    start

1          example1            2                                            1

2          example2            5                                            0

5          example3            4                                            0

4          example4            NULL                                        0

 

Basically, I want to start with the row that has the value of column 'start' set to 1, and then continue to the row with the id listed in the current row's 'next' until next=NULL.  This means a lot of separate queries to grab the information from each row and then query the db again for the information from the next row.  The best way that I can think of doing this would be to query the database just once and store the information in some type of array structure, and then re-order it using a php loop.  This can potentially require a lot of overhead for PHP...

 

My question is really this:  Is there a better way to accomplish the task above (perhaps a magical mysql query) or a better/similar structure that would accomplish the same thing?  I can do it with an integer value and place the item in it's appropriate integer place, but that would require an UPDATE query to every item after an inserted item in a list to add one to it's int value and show that it's gone 'down' a spot in the list.  To really simplify here:  How do you order items in mysql?  Any help is appreciated!

[secoxxx]

ORDER BY

 

is a way to order with mysql

 

SELECT * FROM `table` ORDER BY `id`

 

 

so on

[rawb]

ORDER BY

 

is a way to order with mysql

 

SELECT * FROM `table` ORDER BY `id`

 

so on

 

I'm asking more about the structure of the data itself.  I want the order to be something that I can change, like to move and item 'up/down' a list.  If I want to order rows in a list with 5000 items and I've got an integer field for the row's 'placement', then when I insert a new row and place it at #50, I've got to update 4950 rows to add one to each of their 'placement' fields.

 

With the structure I've laid out above, I've only got to modify 2 rows when I insert any item.. but I've got to either send a different query for each row when I read the list out to get each item's next item, or I've got to use one query and store the whole table in an array and use php to reorder it - which might end up being slow too...

[scotchegg78]

Its a good question, insterested to see if you get an answer.

I wonder if you could do some sxort of subquery recursion through the table starting with your start flagged field and then the results would somehow come out in the order discovered and not a field order?

[fenway]

You basically want a sortorder... easiest way to do this is to guess how many items will be on the list, multiply by 100, then use increments of 50 between successive list items. This allows you to play around with renumbering easily for just one or two, or re-do the whole thing.

[rawb]

You basically want a sortorder... easiest way to do this is to guess how many items will be on the list, multiply by 100, then use increments of 50 between successive list items. This allows you to play around with renumbering easily for just one or two, or re-do the whole thing.

 

Thanks for your reply.  This does seem plausible, but might not be scalable.. what if I need to add many more than 50 items between items?  Is there really no better way?

[fenway]

You can always simply update all of the sortorders -- and by that I mean re-create them on every save -- and then there's no incrementing.  The 50-style increment is simpy for ease of maintenance, especially if you want to change them by hand.