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

Trs2988

I'm a little confused about what you mean, but this should at least get you on the right track.

//assuming $mID is the ID of the record you're changing to mOrder 1, and $mOrder is it's current mOrder
mysql_query("UPDATE table SET mOrder=mOrder+1 WHERE mOrder >= 1 AND mOrder <= $mOrder");
mysql_query("UPDATE table SET mOrder=1 where mID=$mID");

Let me know how that works out.

Paul_Ferrie

i think your going along the right tracks. going to test it now.

Paul_Ferrie

Getting there

$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

Trs2988

Well, it's doing exactly what you told it to. "UPDATE displays SET mOrder=mOrder+1 WHERE mID <> '$mID' AND mOrder >= '$mOrder'" increases everything that isn't $mID, and is greater than what you're turning it into. You need to get the original mOrder of the record you're changing, and then add on "AND mOrder <= '$origMOrder'"

Hope that makes sense.

Paul_Ferrie

it dosn't :S can you explain more how i would use orgOrder

thanks

Trs2988

Let me try to explain. Here's your original set:

mID: 1 2 3 4 5 6 7 8 9 10
mOrder: 1 2 3 4 5 6 7 8 9 10

If you want to change the mOrder of #5 to 2, here's what you want to end up with:

mID: 1 2 3 4 5 6 7 8 9 10
mOrder: 1 3 4 5 2 6 7 8 9 10

So how do you get from the first set to the second set? First of all, #1 is unchanged. So anything with an mOrder less than the new mOrder (2) shouldn't be changed. Secondly, anything that is equal to or greater than the new mOrder (2), but LESS THAN the old mOrder (5) was increased by 1. That's the important part.

So, let's start expressing this in terms of variables. Let's call the original mOrder of #5 origMOrder (which is set equal to 5). Similarly, we'll call the new mOrder of #5 newMOrder (which is set equal to 2). We want to change the mOrder of #5 from origMOrder (5) to newMOrder (2). With me so far?

Now, back to our rules. "Anything that is equal to or greater than the new mOrder , but LESS THAN the old mOrder was increased by 1." So what should be increased? When the mOrder >= newMOrder AND mOrder < origMOrder. And you'll notice that that's almost in terms of a SQL query.

UPDATE displays SET mOrder=mOrder+1 WHERE mID <> '$mID' AND mOrder >= '$mOrder' AND mOrder < '$origMOrder'

I hope that it akes sense this time. I did my best to explain it.

Paul_Ferrie

and the trian comming from texas has 52 passneger and needs to stop and drop off 1 third of passengers at each station blah blah blah. so how many will reach arrive that started ther journey from texas.

God it's like Math class all over agaion lol

Trs2988

Paul Ferrie wrote:
God it's like Math class all over agaion lol

I'd have to agree. Math and programming go hand in hand.

Paul_Ferrie

I've done alright so far🙂
i will studie suedo tomorrow and try and get to grips with it.

Thanks again
Paul