*solved* Re-ordering SQL rows

[jcbarr]

Okay, I have a script that allows users to queue up items. They click on an item and it places them in their queue which is displayed at the top of the page. That works all fine and well.

I want them to then be able to look at their queue at the top of the page and re-order their items. For example, click a link next to it and it moves the item up one spot.

The script places the data in a table that looks like this;

ID--PID--TEAM

The script then pulls all the rows for a certain team and displays data and sorts by the ID field ASC.

So, how would I go about re-ordering the rows in the table? I have been trying to figure this out for some time and I just can't seem to come up with a good way to do this. In a similar issue before I simply had to use a "swap" method, where the user selected two IDs, typed them in and then clicked the swap button...that is clunky...I want them to be able to click an arrow and move the item up or down.

Any help would be great...

Here is what the queue code looks like currently;

[code=php:0]<?php
$sql="SELECT * FROM queue WHERE TEAM='$team' ORDER BY ID ASC";
$result=mysql_query($sql);
while ($players=mysql_fetch_assoc($result)){
  $rookie=$players['PID'];
  $sql2="SELECT * FROM rosters WHERE PID='$rookie'";
  $info=mysql_query($sql2);
  while ($list=mysql_fetch_assoc($info)){
    echo "<tr bgcolor=#FFFFCC>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['POS'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['Firstname'];
    echo " ";
    echo $list['Lastname'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['Years'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['SP'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['AC'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['AG'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['ST'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['HA'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['EN'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['IN'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1>";
    echo $list['DI'];
    echo "</td>";
    echo "<td align=center><font face=tahoma size=1><a href=queue.php?team=$team&PID=$rookie&DEL=Y";
    echo ">REMOVE</a></td></tr>";
  }
}
?>[/code]

[Daniel0]

Add a new field called order.

Have the up link something like this: index.php?move_up=*id*
Have the down link something like this: index.php?move_down=*id*

[code]<?php
if(!empty($_GET['move_up']))
{
$query1 = mysql_query("SELECT * FROM queue WHERE id='{$_GET['move_up']} LIMIT 1");
$info1 = mysql_fetch_assoc($query);

$query2 = mysql_query("SELECT * FROM queue WHERE order='".$info1['order']-(1)." LIMIT 1");
$info12 = mysql_fetch_assoc($query);

mysql_query("UPDATE queue SET order=order-1 WHERE id='{$info1['id']}");
mysql_query("UPDATE queue SET order=order+1 WHERE id='{$info2['id']}");
}
else if(!empty($_GET['move_down']))
{
$query1 = mysql_query("SELECT * FROM queue WHERE id='{$_GET['move_down']} LIMIT 1");
$info1 = mysql_fetch_assoc($query);

$query2 = mysql_query("SELECT * FROM queue WHERE order='".$info1['order']+(1)." LIMIT 1");
$info12 = mysql_fetch_assoc($query);

mysql_query("UPDATE queue SET order=order+1 WHERE id='{$info1['id']}");
mysql_query("UPDATE queue SET order=order-1 WHERE id='{$info2['id']}");
}

// rest of code here
?>[/code]

Now order it by order.

PS: You should consider using this instead of all those echoes: [code]echo <<<EOF
all
your
stuff
here
EOF;[/code] (heredoc)

[jcbarr]

can't order by order, SQL has some issues with that. Not a big deal, just called the column list instead.

Awful lot of syntax error in that code apparently but it appears to be pretty close, just gotta get it cleaned up.

Thanks a ton for pointing me in that direction.

[btherl]

You probably need backquotes around the order column each time you mention it.. `order`.  I'm a postgres guy, so that might not be the right escaping..

[jcbarr]

I believe you are right...

Thanks, I got that portion of things working. Thanks a lot for all the help.