I have viewed some examples on scripts that I use such as phpBB that allow you to move row items up or down. I got it to work but the problem is that you may have to click a link a few times in order for it to move. That is because it ads +10 to the plan_order value and uses ORDER BY plan_order DESC and the problem is that if one item has 20, and the next item has a value of 10, if you use the "move up" link it changes from 10 to 20 as well so you have two listings as 20 plan_order so there is no change in movement. Then you have to click move up again and then it changes to 30 and goes. Is there a way to make it move up or down based on one click? Here is the code I have used:

if ( $action == 'moveup' )
{
$sql = "UPDATE " . PLANS_TABLE . " SET plan_order=plan_order+10 WHERE plan_id = '$editid'";
query_db($sql);
}
if ( $action == 'movedn' )
{
$sql = "UPDATE " . PLANS_TABLE . " SET plan_order=plan_order-10 WHERE plan_id = '$editid'";
query_db($sql);

}

And the part of the links code is:

$up = "?action=moveup&editid=" . $row['plan_id'];
$dn = "?action=movedn&editid=" . $row['plan_id'];
$img_path = $site_url.$images_dir;


$up_down = '<a href="'.$up.'"><img src="'.$img_path.'up.gif" border="0" title="move up '.$row['plan_name'].'"></a> &nbsp;
<a href="'.$dn.'"><img src="'.$img_path.'down.gif" border="0" title="move down '.$row['plan_name'].'"></a>';

That is similar to how other scripts use it and they do not seem to have the movement problem. Plus when a script is at the top or bottom of the page you sometimes have to click a few times in order for it to move but I plan to add some more code to fix that.

EDIT

Well I added the header() tag to refresh the page since sometimes it wouldn't update so that solved that problem. Perhaps if I made it detect if the value is the highest or lowest on the list and not to update that, that may fix the whole problem. I will try that now.

Yay I got it to work :D Added the header refresh after the update and adding some code that grabs the highest value and makes sure that it doesn't go beyond that and vice versa for the lowest value.

Well darn I spoke too soon it still has the problem if adding too many or taking away too many numbers or they all equal the same amount which causes the user to have to click the up or down link multiple times for it to work.

I may be reading this wrong... but if you're attempting to rearrange items in a list what you'll need to do is either submit all elements and simply update their plan_order to be their individual ordering in the list, or re-order all elements above the current element (if moving up), or re-order all elelements below the current element (if moving down).

The best solution is to submit all elements in the list, then you can easily loop through them:


<?php

for ($i=0; $i<$_POST['plans']; $i++) {
$sql = "UPDATE " . PLANS_TABLE . " SET plan_order='" . $i . "' WHERE plan_id = '" . (int)$_POST['plans'][$i] . "'";
query_db($sql);
}

?>
Hope that makes sense.

Thanks I will try that. I would have to modify your code since I am using $_GET instead of POST but if that doesn't work I suppose I can create a hidden post field. (edit) Actually I am not sure how to do it with your example since I am not sure if you are pulling every Id from a form or what. Would that cause problems when dealing with hundreds of listings? Because I had a site that did this using javascript and one user on my account had so many listings that it no longer worked that is why I want to go the php route this time.

EDIT

Actually I just noticed a function from one of the scripts that I am using and perhaps if I copy that it will work.

Ok I got it to work. I have to use the header() function to reload the page though since sometimes the page doesn't update right away but it least it works. I wonder though, if someone has hundreds of listings would it take a long time for them to move songs up and down since it has to update all of the entries every time.

<a href="script.php?move=up">up</a>
<a href="script.php?move=down">down</a>
<a href="script.php?move=first">first</a>
<a href="script.php?move=last">last</a>
<a href="script.php?move=up10">10 up</a>
<a href="script.php?move=down10">10 down</a>
<a href="script.php?move=42">to 42nd position</a>


$id = intval($id) ;
if ($id && $move) {
$query = "SELECT rank FROM items WHERE id='$id' LIMIT 1 ";
$res = mysql_query ($query);
$old_rank =mysql_result ($res,0,'rank');
$query = "SELECT MAX(rank) AS max_rank FROM items" ;
$res = mysql_query ($query);
$max_rank = mysql_result ($res,0,'max_rank');

switch ($move) {
case "first":
$query = "UPDATE items SET rank = 1 WHERE id='$id' LIMIT 1 ";
mysql_query ($query);
$query = "UPDATE items SET rank = rank + 1 WHERE rank < $old_rank and rank < $max_rank and id != '$id' ";
mysql_query ($query) ;
break;
case "last";
$query = "UPDATE items SET rank = '$max_rank' WHERE id='$id' LIMIT 1 ";
mysql_query ($query);
$query = "UPDATE items SET rank = rank - 1 WHERE rank > $old_rank and rank > 1 and id != '$id' ";
mysql_query ($query) ;
break;
case "up":
$query = "UPDATE items SET rank = rank - 1 WHERE id='$id' and rank > 1 LIMIT 1 ";
mysql_query ($query);
$query = "UPDATE items SET rank = rank + 1 WHERE rank = $old_rank -1 and id != '$id' ";
mysql_query ($query) ;
break;
case "down":
$query = "UPDATE items SET rank = rank + 1 WHERE id='$id' LIMIT 1 ";
mysql_query ($query);
$query = "UPDATE items SET rank = rank - 1 WHERE rank = $old_rank + 1 and rank > 1 and id != '$id' ";
mysql_query ($query) ;
break;
case "up10":
// left for OP as excersize
break;
case "down10":
// left for OP as excersize
break;
default:
if ($move == intval ($move) ) {
$query = "UPDATE items SET rank = $move WHERE id='$id' LIMIT 1 ";
mysql_query ($query);
if ($move > $old_rank) {
$query = "UPDATE items SET rank = rank - 1 WHERE rank > $old_rank AND rank <= $move and id != '$id' ";
mysql_query ($query) ;
}elseif ($move < $old_rank) {
// left for OP as excersize
}
}
}
}


This has been typed in with morning coffee so it will need some tidying up before it will work, but you got the idea. First thing, other then fixing syntax errors, should be making sure that we are not trying to move item out of the range (to rank 0 or rank greater then number of items in the table)

Thanks but I have found a solution. I wonder how to make this feature display the results using AJAX so that the whole page doesn't reload every time a change is made.