|
|
Posted by Richard Lynch on 03/08/05 21:06
Dave Carrera wrote:
> Hi List,
>
> I have a table like the diag below:
>
> ID Name Pos
> 1 jig 1
> 2 pig 2
> 3 dig 3
> 4 fig 4
>
> What i am trying to do is upon click to change pos 1 to pos 2 and pos 2
> to pos 1so that i can manage the position i show my list.
>
> Can someone throw me some nuggets of logic wisdom as my searchs are
> coming up blank.
<?php
$connection = @mysql_connect('localhost', 'user', 'pass') or
trigger_error("Could not connect to database.", E_USER_ERROR);
//Handle moves:
if (isset($_GET['move_up'])){
$move_up = $_GET['move_up'];
list($id, $pos) = each($move_up);
//Maybe begin a transaction here:
$query = "update table set pos = pos - 1 where ID = $id";
@mysql_query($query, $connection) or
trigger_error(@mysql_error($connection) . " $query", E_USER_ERROR);
$query = "update table set pos = pos + 1 where pos = $pos - 1 and ID
!= $id";
@mysql_query($query, $connection) or
trigger_error(@mysql_error($connection) . " $query", E_USER_ERROR);
//maybe end that transaction here
}
if (isset($_GET['move_down'])){
$move_down = $_GET['move_down'];
list($id, $pos) = each($move_down);
//Maybe begin a transaction here:
$query = "update table set pos = pos + 1 where ID = $id";
@mysql_query($query, $connection) or
trigger_error(@mysql_error($connection) . " $query", E_USER_ERROR);
$query = "update table set pos = pos - 1 where pos = $pos + 1 and ID
!= $id";
@mysql_query($query, $connection) or
trigger_error(@mysql_error($connection) . " $query", E_USER_ERROR);
//maybe end that transaction here
}
//Display data:
$query = "select ID, name, Pos from table order by Pos";
$words = @mysql_query($query, $connection) or
trigger_error(@mysql_error($connection) . " $query", E_USER_ERROR);
while (list($id, $name, $pos) = @mysql_fetch_row($words)){
echo "$name ";
if ($pos) echo "<a href=\"$_SERVER[PHP_SELF]?move_up[$ID]=$pos\">^</a>";
else echo " ":
if ($pos < @mysql_num_rows($words)) echo "<a
href=\"$_SERVER[PHP_SELF]?move_down[PHP_SELF]?move_down[$ID]=$pos\">v</a>";
else echo " ";
echo "<br />";
}
?>
The basic trick is to provide yourself with both the ID and the pos you
want changed, so you can move both the current record (by ID) and the one
you are swapping with -- which you find that by the pos and the fact that
it's NOT the ID of the one you just moved.
NOTE:
This assumes that only *ONE* user (administrator) is moving stuff around
at a time.
If user X tries to move jig up while user Y tries to move pig up at the
"same" time in a web application, all hell will break loose because the ID
and pos will change from what each user is "expecting" in your script.
--
Like Music?
http://l-i-e.com/artists.htm
[Back to original message]
|