rearranging table rows

[sukanya.paul]

hi,
i have a table with rows of name. also there are arrows up and down corresponding to each row. when up arrow is clicked the position of the row should go 1 place higher and when down arrow is clicked it should come down 1 place.
the name come from database and have random id which are unique.
can some1 pls help me with this.
i'm trying for quite a while but am clueless.
Thanks in advance..Suk

[allahverdi]

You mean when you press up arrow, that row go to up and other one go down?

[sukanya.paul]

yes.
example:

aaa up down
bbb up down [ say up and down are buttons]
ccc up down

if i click down for aaa then the list shpuld look like

bbb up down
aaa up down
ccc up down

[allahverdi]

If you mean that. Your table is like this:

   id   |   name
---------------
   1    |   hello
   2    |   hi
   3    |   goodbye

To echo all rows:

<?php
$result = mysql_query("SELECT * FROM mytable ORDER BY id DESC");
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$id = $row['id'];
$name = $row['name'];
echo $name."&nbsp;&nbsp;<a href=\"?up=".$id."\">Go up<a/>&nbsp;&nbsp;<a href=\"?down=".$id."\">Go down</a><br/><br/>";
}
?>

When press up or down:

<?php
if($_GET['up']){
$id = $_GET['up'];
$newid = $id + 1;
$current = mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'");
$other = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'");
}
if($_GET['down']){
$id = $_GET['down'];
$newid = $id - 1;
$current mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'");
$other = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'");
}
?>

I don't know if it is correct way or not. But hope it will help...

[sukanya.paul]

this may work but the problem is that in my table the ids are not in order..

my table:
---------
id | name
---------------
1 | hello
10 | hi
14 | goodbye
---------
this is because entries get deleted also.. so any clue how to proceed?

[allahverdi]

I don't know how but you can make: on click hi's up, replace hi's id with goodbye's one and goodbye's one with hi's one.

Um got idea. But i think it is incorrect:

Change this:

<?php
if($_GET['up']){
$id = $_GET['up'];
$newid = $id + 1;
$current = mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'");
$other = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'");
}
if($_GET['down']){
$id = $_GET['down'];
$newid = $id - 1;
$current mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'");
$other = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'");
}
?>

To this:

<?php
if($_GET['up']){
$id = $_GET['up'];
$selectup = mysql_query("SELECT * FROM mytable WHERE id > '$id' LIMIT 1") or die(mysql_error());
while($row = mysql_fetch_array($selectup, MYSQL_ASSOC)){
$newid = $row['id'];
}
$updatecurrent = mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'") or die(mysql_error());
$updateup = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'") or die(mysql_error());
}
if($_GET['down']){
$id = $_GET['down'];
$selectdown = mysql_query("SELECT * FROM mytable WHERE id < '$id' LIMIT 1") or die(mysql_error());
while($row = mysql_fetch_array($selectdown, MYSQL_ASSOC)){
$newid = $row['id'];
}
$updatecurrent = mysql_query("UPDATE mytable SET id = '$newid' WHERE id = '$id'") or die(mysql_error());
$updatedown = mysql_query("UPDATE mytable SET id = '$id' WHERE id = '$newid'") or die(mysql_error());
}
?>

Hope it will be correct without any errors.