Specify the order of the output

[ice_cracker]

Hi,
For a menu system on a friends website (php/mysql n00b) they have a table with the fields:
ID (mediumint auto_increment), link_title (text), link_url (text)
and then when the menu is called and it pulls the info from the table and sorts the entries by the 'id' so the newest entry is at the bottom of the list, however they want me to change it so they can specify where abouts in the list each link appears, so instead of listing them as 1, 2, 3, 4, 5, 6 they can choose how to list them i.e. 6, 3, 2, 5, 1, 4

I thought about just telling them to change the ID's of each row so they are in the order they want, but if they wanted to add a link between 2 and 3 that would mean having to re-number all the entries 3 and over (i.e. quite a bit of work)

What would be the best way to do this? if it's possible. Adding more fields to the table isn't a problem, as is creating another table which has a list of what order the links should go in.

What do you guys suggest?

Thanks 🙂

(maybe this should have gone in one of the php forums 😕)

[Danicek]

If you want to do it in database, only way to avoid "renumbering" each time you enter new entry "in the mid", you must use new collumn with value of id of previous menu item.
So you will have

id previous_id
1,0
2,1
3,2
4,3

And if you want to add something in the middle:
1,0
2,1
3,5 updated
4,3
5,2 new

[ice_cracker]

Thanks for the reply,
However I'm a little lost as to how that would work... could you explain it a bit more?

🙂

[Danicek]

You add new column to your table. This collumn will contain id of previous menu item.
This mean that in begining you will write your menu items in database in order you want them to display:

id, previous_id
1,0
2,1
3,2
4,3
5,4
6,5
7,6
8,7
9,8,
10,9

This means that menu item with id 2 will go after menu item with number 1. Menu item with number 3 will go after menu item with number 2 and so on.
You also need special value for first menu item, because it will have no "previous item" (you can use 0 like in my example).

Now, lets say you need to add menu item that will show as fifth. If you will not use previous_id, you need to do 10 updates and 1 insert (= 11 operations).
But if you have previous_id in your table, you need to do just 2 operations - update currently fifth menu item and tell him that it will go after new menu item and insert new menu item and tell him that it will be fifth (it means that it will go after 4th). So your table will look like this:

id, previous_id
1,0
2,1
3,2
4,3
5,11 -- updated
6,5
7,6
8,7
9,8,
10,9
11,4 -- inserted

SQL something like this:

update menu
set previous_id = 11
where id = 5

insert into menu
values(11,4)

[ice_cracker]

Ah right, got you not 🙂

That should work, thanks 😃