hi
i have table of links. there is a field in a table called "order".
i use it to manually specify the order in which these links are displayed
e.g. $qr="SELECT * FROM links ORDER BY order ASC";
links | order
---------------
link1 -> 4
link2 -> 1
link3 -> 3
link4 -> 5
link5 -> 2
...
the "problem" is that if i want to add new link say in position 3 then i should either
- increase value of "order" by 1 for all links where value of "order">=3
or
- decrease value of "order" by 1 for all links where value of "order"<=3
i saw someone's code that deals with this situation by selecting appropriate rows and then loop over them each time updating value of "order" field...
somehow, i don't like this method.it is good for small number of rows.
Maybe someone knows other ways more efficient ways to do manual ordering?
manual ordering of rows
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
No need for a loop.
Lets say the value of the new row you want to add is 4.
Lets say the value of the new row you want to add is 4.
Code: Select all
$sql = "UPDATE `links` SET `order` = `order`+1 WHERE `order` >= 4";
//run the query
$sql = "INSERT INTO `links` SET `order` = 4";
//run the queryok, but the real problem is that if table has a few thounds rows then
updating each time a few hundred rows is probably will be very very slow...
so i'm looking for ways for manual ordering at 'constant time'...
i could for example use floats for "order" values and then each time a new row is added i'll take values of 'order' of adjasent rows and calculate:
newrow_Order = (previous_Row_Order + next_Row_Order)/2;
i guess doing this is much faster than updating a few hundredths rows
(on average)...
maybe someone knows a better way?
updating each time a few hundred rows is probably will be very very slow...
so i'm looking for ways for manual ordering at 'constant time'...
i could for example use floats for "order" values and then each time a new row is added i'll take values of 'order' of adjasent rows and calculate:
newrow_Order = (previous_Row_Order + next_Row_Order)/2;
i guess doing this is much faster than updating a few hundredths rows
(on average)...
maybe someone knows a better way?