manual ordering of rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

manual ordering of rows

Post by newmember »

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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

No need for a loop.
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 query
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

ok, 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?
Post Reply