[php]Setting display order for individual records?

[Paul Ferrie]

Hi guys,

Right ok lets see if i can get this out my head.

DB setup
---
mID = autoincrement ID
mName= string info
mImg = string info
mOrder = number
---
Right ok so lets say i have 10 records
so mOrder would be 1-10 for each record.

Right so some time down the line i might want to change the mOrder for record 8 to 1. So record 1 would need to have the mOrder updated to 2 and so on for each other record

How do i do this?

Thanks for any help suggestions

[freddycodes]

I assume you already know how to set the new mOrder for row 8.

So do that first and then update all records with a mOrder greater than or equal to the order you just set whose key doesn't match the row's key you just changed.

Sample
update foo set mOrder = 2 where mID = 10; update foo set mOrder = mOrder + 1 where mID <> 10 && mOrder >= 2;

[Paul Ferrie]

$mOrder = $_GET['mOrder'];
$mID = $_GET['mID'];

$query1 = "UPDATE displays SET mOrder='$mOrder' WHERE mID='$mID'";
if(!mysql_query($query1)) {
fail("Error 1 updating table information");
}
$query2 = mysql_query("UPDATE displays SET mOrder=mOrder+1 WHERE mID <> '$mID' AND mOrder >= '$mOrder'");
if(!$query2) {
fail("Error 2 updating table information");
}

It does work but for some strange reason anything after the changed field is incremented by 1. I know this is sort of to be the case.

Example:
http://www.domain.com/eventOrder.php?mID=2&mOrder=5

will turn
field 1 - 2 - 3
--------------------
1 a 1
2 b 2
3 c 3
4 d 4
5 e 5
6 f 6
7 g 7
8 h 8
9 i 9
10 j 10

into
field 1 - 2 - 3
--------------------
1 a 1
2 b 3
3 c 4
4 d 5
5 e 2
6 f 7
7 g 8
8 h 9
9 i 10
10 j 11

(^damn spaces wont format^)

Note 6 is missing. is this the right result?

Thanks

[freddycodes]

Okay so there are few things to take into account that I didn't think of.

1) If the new order is lkess than the original, increment all rows greater then the new and less than the old, and decremement all rows greater that the old.


2) If the new order is greater than the original, decrement all rows greater then the original and less than the new, and increment all rows greater that the new.

[Paul Ferrie]

keh.....:confused:

[freddycodes]

That is the logic that has to happen. Making it happen will take some more work. If I have some more time later, I will try and help you with the syntax.

[Paul Ferrie]

Any help would be very much appreciated

[Paul Ferrie]

*Bump* :confused: