I have the following table:
id | name | position
1 Bob 4
2 Jim 5
3 Harry 73
4 Paul 89
I want the user to be able to re-order the columns as they see fit, like move one row up and down, using the position column. I have thrown in the big numbers in there (73 and 89) just to cater for all events.
Is there a way to dynamically re-order the table via SQL? Or will I have to manually re-order all tables when someone selects 'move up' on 'Harry'? I can only imagine I will have to:
There must be a quicker easier way to do this using MySQL/PHP?
For those that find this page in the future, I found some code from an old shop that did the trick:
if((isset($_POST['moveup_x'])) || (isset($_POST['movedown_x']))) {
$current = select("SELECT `position` FROM `events` WHERE `id`='".rEsc($eventid)."';", true,true);
if(isset($_POST['moveup_x'])) $target = ($current - 1);
if(isset($_POST['movedown_x'])) $target = ($current + 1);
if($current > 1) {
$counter = 1;
foreach(select("SELECT `id` FROM `events` ORDER BY `position`;") as $val)
{
$newposition = $counter;
if($current == $newposition) {
$newposition = $target;
} elseif(($current > $newposition) && ($target <= $newposition)) {
$newposition++;
} elseif(($current < $newposition) && ($target >= $newposition)) {
$newposition--;
}
runSQL("UPDATE `events` SET `position`='{$newposition}' WHERE `id`='{$val['id']}';");
$counter++;
}
array_push($msgs,"Successfully moved event.");
} else {
array_push($msgs,"!Cannot move the event any higher up the list.");
}
}
You need to know your current, which you can get from $_POST (in this case I got from rowID in the database) and your target (I had img submit buttons so I just minused or plussed from whichever button the user clicks). Once you have that, loop through each row in the table however you see fit, (set a counter beforehand) and run the IF/ELSE statement. Modify for your need.
It works very well though. There problably is a quicker/easier way, i.e. all in one SQL statement, instead of updating every single row in the database, but I couldnt find it.
you just need to use a simple case in your update statement, see example below for moving a number up. For moving a numberdown, same case with minor adjustment, and you can use a nested case to decide which to use by comparing number and destination
set @nr=4; set @dest=2;
select *,
case
when number between @dest and @nr-1 then number+1
when number=@nr then @dest
else number
end as number_alt
from
(
select 1 as number union select 2 union select 3 union select 4 union select 5
)a
If can you provide position order after pressing each up or down you can use following query. That is if current position is 4,5,73,89, suppose user selected 89 and pressed up. In that case if you can provide 4,5,89,73 to query then following query will help you
select * from table22 order by field(position,4,5,89,73);