Change order of database contents

mikawhat

I'm creating a menu from a db, where for example 'category1' will link to 'category1.php'. No problems at all with that. my problem is the order of those db contents. I want to be able moving the different categories up and down. e.g

category1 [U]move down[/U]
category2 [U]move up[/U]/[U]move down[/U]
category3 [U]move up[/U]/[U]move down[/U]
category4 [U]move up[/U]/[U]move down[/U]
category5 [U]move up[/U]

so when you click on category2-move up the order should be:

category2
category1
category3
category4
category5

My thought on this is when clicking on a link to move up or down, to swap the ids of those two categories. I don't know if the thought is right but the main problem is how to translate this into php & mysql. Is there anybody who can help me with that?

By the way, my table has two columns, id and name...

autocolismo

create another column "order"
then order your querys by it
this way you wont have to change the id field

mikawhat

OK. lets say i've created the column and i'm using mysql_fetch_array to display the categories... in php, how can i specify the previous or next category? e.g.

category1 move down
category2 move up/move down
category3 move up/move down
category4 move up/move down
category5 move up

when move down is clicked for category1, how can i do:
1. category1 get the 'order' value of category2
2. category2 get the 'order' value of category1

autocolismo

if is moving down
$new_order = $actual_order - 1;

if is moving up
$new_order = $actual_order + 1;

UPDATE table SET order = '$new_order' WHERE id = 'id;
UPDATE table SET order = '$actual_order' WHERE order = '$new_order'

something like this ...

mikawhat

ok thank you. i'll give it a try

mikawhat

if i'm using while ($row = mysql_fetch_array($query)) to get the contents of the database, how can i refer to the first and last rows?

while ($row = mysql_fetch_array($query)) {
    $category = $row['category'];
    echo "<p>$category <a href=\"move_down.php\">move_down</a>|<a href=\"move_up.php\">move_up</a></p>";
}

with this codes i'll get:

category1 move up | move down
category2 move up | move down
category3 move up | move down
category4 move up | move down
category5 move up | move down

i want something to check:
- if is the first row, don't use move up link
- if is the last row, don't use move down link

Can anybody help me with this? thanx!

autocolismo

maybe

while ($row = mysql_fetch_array($query)) { 
    $category = $row['category']; 
    echo "<p>$category ";
if($row['order'] >1){ echo "<a href=\"move_down.php\">";}
echo "move_down";
if($row['order'] >1){ echo "</a>";}
echo "|";
if($row['order'] >= $totalRows_row){ echo "<a href=\"move_up.php\">";}
echo "move_up";
if($row['order'] >= $totalRows_row){ echo "</a>";}
echo "</p>"; 
}

mikawhat

this looks great! $totalRows_row is exactly what i was looking for! thanx!