Reordering DB Content. Help Appreciated.

[ta_da]

Hi,

I am working on a small content management system for a client who would like to have the ability to reorder the display sequence of content items as he sees fit from the CMS. Currently I am reordering them manually using an INT field called 'Seq' which I have to set by hand in a numeric form field on the CMS. This is obviously not ideal because subsequent rows need to be updated when something is moved or inserted to a new position - which is a pain in the ***. :)

Ideally I would like to have UP / DOWN arrows similar to the joomla panels which reorder the clicked row in realtime as the script is updated.

Does anyone have a tidy PHP/MYSql solution for this problem? I know its a solution that is out there and used widely but I am a bit unsure where to start in implementing it myself.

Thanks again

T

[Scally_Ally]

The way i do stuff like that (if i have understood you correctly) is to have another column in the table called position - where 1 is the highest and n is the lowest

Say the you have 10 rows, where position is starting at 1 and ending at 10 (with all values in between taken), when you hit the button to move one up you should take 1 off its position and then add 1 onto the one that previously held that position, shunting it down.

Then you can retrieve these results ordering by the position column

There are loads of ways to do it - thats just the way i do it.

Hope that helps
Ally

[edited by: Scally_Ally at 2:09 pm (utc) on Mar. 5, 2007]

[joelgreen]

1. assign each row a number (you have it now).
2. when moving - just swap numbers on rows.

example:

#1 line 1
#2 line 2
#3 line 3

now you want line # 3 to be on the second place (i.e. exchange #3 with #2). so you update #2 and #3 numbers only.

#1 line 1
#2 line 3
#3 line 2

[ta_da]

Thanks Ally.

You know, thats a far simpler and more effective solution than I had thought it was going to take :) cheers. I was thinking about it too complicated. DOH! Bear with me here and walk through my understanding of this with my rudimentary solution.... perhaps I shouldnt be doing so many sql queries.

So basically, as I understand it now, all I have to do is swap the chosen row with the adjacent row in whatever direction is chosen using dynamic links in my listing, for example....

"listing.php?$id=Xposition=Y&direction=up"

Then have some code on the listing page like...

$id = $_GET['id']
$position = $_GET['position'];
$direction = $_GET['direction'];
$newposition = $position;

if ($direction == "up") {
$newposition++;
} else {
$newposition--;
}

then some Sql code to reorder...

$otherid = "SELECT ID from tablename WHERE Position = $newposition"

"Update tablename SET Position = $newposition WHERE ID = $id"

"Update tablename SET Position = $position WHERE ID = $otherid.

I have no idea if that is a decent way to do it, but you can let me know. I intially thought I had to loop through the whole table and reorder stuff but as I see now thats jsut stupid :D

Thanks again.

[Scally_Ally]

Yeah you got that dead on there - just update the 2 rows, cause they are gonna be the only 2 changing.

You only need the one ID aswell cause you can work off the position, so that makes it just 2 SQL queries.

"Update tablename SET Position = (position+1) WHERE Position = $position"

THEN

"Update tablename SET Position = (position-1) WHERE ID = $id"

Do the moving of the row where you dont know the ID first, you can then affect the other row by referencing the ID.

Ally

[ta_da]

Thanks again. Its working great :)

:D