what is the best order positioning technique?

[ALKateb]

Hello everybody!
i am having hard time trying to figure out the best way to assign an order position to my items in the database

i was doing it this way:
as u would expect a field in the database (item_position)
and then when querying for it i will do the ordering by that field

then i make up and down button in the admin area

let's say i have 3 products 1,2 and 3
so when he clicks (up) on the second item the code will update the first and the second record so it will add 1 to the first and subtract 1 from the second! two simple queries will do the work

that seems ok so far! but the problem is when he deletes an item! this will leave a gap between the items!

i could do it by subtracting 1 from all items in database as long as this item has larger position order than the deleted item! but this sounds too stupid solution to me!
it's not smart at all to do few thousands of queries each time a record has been deleted!

so how do u think i can work this out? what's the best way to do this order thing!

i don't know i was thinking (what's wrong with leaving the gap between items) but it seems like it might cause me some problem later? or it wont! i don't know actually!

my way might be wrong! some advices please!
thanks in advance

[jd01]

I would think you could change your SELECT a bit and rather than doing the math with PHP do something like:

To get the next record:
SELECT * FROM table WHERE id > $CurrentID ORDER BY id LIMIT 1

To get the previous record:
SELECT * FROM table WHERE id < $CurrentID ORDER BY id DESC LIMIT 1

To get both records:
(SELECT * FROM table WHERE id > $CurrentID ORDER BY id LIMIT 1)
UNION
(SELECT * FROM table WHERE id < $CurrentID ORDER BY id DESC LIMIT 1)
ORDER BY id;

You would not need to worry about gaps in the IDs this way...
(If I'm understanding your situation correctly.)

ADDED: item_position = $CurrentID / id in my example, so you would check for the next highest 'item_position' compared to the current 'item_position' and the next lowest 'item_position' compared to the current 'item_position'.

[ALKateb]

Ah! thanks a lot for the explanation i did not think of it this way!

so all i have to do is so query for the next or previous item! and if he is moving up the item it will switch the values between the current and the next item
same for the previous item!
that's good so far but i also wanted to ask about what if i wanted to move an item to specific position .. how could this be done?
for example i want to move item number 10 to the second position! but still i dont want the item in the second position to go back to the 10th!
i was thinking of leaving 10 between positions for this so it will be like 10 - 20 - 30...etc instead of 1 - 2 - 3
but again it does not sound good
so how could this be done then?
thanks again

[jd01]

That really depends quite a bit on your exact application...

If you only show 10 out of 100 items at a time and need to select more at some time the answer will be different than if you select all items at once... Do you want a 'display order' for a single visitor or to change the order for everyone?

If you want a 'display order', you might be best to set a 'display order' field for each individual user, and rather than updating the entire database, just update the individual visitors display order, then select 'up' or 'down' based on the display order field, rather than actual database order... It's the same concept, except you select the entire display order at once and store it in a cookie or something, then base your database interaction on the display order... Save it to the DB when they change it.