[SOLVED] Sorting query help

[Canman2005]

Hi all

 

Wondering if anyone can help, basically I have the following sql table called "test"

 

id      title      order

1        test 1    1000

2        test 2    1001

3        test 3    1002

 

I then have a php page which lists all the above data, next to each row on my page, I have two buttons, one button which says "up" and one button which says "down".

 

Basically, what I want to do is, when the "up" button is clicked, switch the value in the "order" field with the next highest one, and if the "down" button is clicked, then it does the opposite.

 

For example, if the button "down" was selected on ID 2 on the above example table, then its `order` value "1001" would be swictched with the order value for ID 1, so the table would then look like

 

id      title      order

2        test 2    1000

1        test 1    1001

3        test 3    1002

 

And if the "up" button was selected on ID2, then the table would look like

 

id      title      order

1        test 1    1001

3        test 3    1001

2        test 2    1002

 

Does this make much sense?

 

I have tried to post the id value and its current `order` value and then doing the QUERY

 

$orderup = $_GET['order']+1;
$sql1 ="UPDATE `test` SET `order`='".$_GET['order']."' WHERE `order`='".$orderup."'";
@mysql_query($sql1, $connection) or die(mysql_error());

$sql ="UPDATE `test` SET `order`='".$orderup."' WHERE `id`='".$_GET['id']."'";
@mysql_query($sql, $connection) or die(mysql_error());

 

But that doesnt seem to work right

 

Any help would be great

 

Thanks

 

Dave

[spode]

You don't need to update the database. Just get the information and THEN order it out. That way you dont change your database everytime someone wants to reorder it.

 

<?php

$getinfo = mysql_query("SELECT `order` FROM `test`") or die(mysql_error());
$neworder = sort($getinfo);
$reverse = rsort($neworder); //etc.
?>

[Canman2005]

Sorry Spode, you have totally lost me

[spode]

I just updated my post...try looking at that code...I'm kind of a noob but that should work, to my understanding.

 

What I'm saying is from the code you have provided, it looks as if you are trying to reorder it by updating the table, which is unneccesary. Just order the array that is returned.

[Canman2005]

Hi spode

 

That didnt seem to do anything, but I guess there is more to it.

 

What sort of update query would I need to perform?

 

Thanks

[Barand]

~spode

 

So if he comes back to the table tomorrow he'll then have to swap the positions all over again

 

~canman

 

consider

$a = 1;

$b = 2;

 

If you want to swap the values

 

$tmp = $a;

$a = $b;

$b = $tmp;

 

So you have to find the id of the next highest (or lowest) record and do a similar swap.

[Canman2005]

Thanks Barand

 

Could you tell me the QUERY to use in order to get the id of the next highest or lowest record

[Canman2005]

Thanks all