[SOLVED] Ordering rows in table

[Brandon Jaeger]

Hi,

 

I'm trying to order items from a web page via MySQL with "Move up" and "Move down" links. I have a column name called 'orderid' which will hold the order ID (0, ... , # of rows). How would I go by changing every 'orderid' accordingly? I'm trying to do what they do in some forum scripts where they move the forums up/down to order them.

 

Thank you.

[Fadion]

The move up/down option can just switch the order of one row with the next one. So if one row has an order of 5, moving it up will give it an order of 6, thus swapping it with that row that has actually the order of 6. Basically:

 

row order #5 is moved up

order is incremented to #6

the existing row with order #6 is found

the order of that row is changed to #5

the order of the moved up row is changed to #6

 

Thats the same idea for moving down. Hope it helps.

[Brandon Jaeger]

So something like this?

 

The URL would look like: example.php?objectid=1&move=up

 

$object_id = $_GET["objectid"];
$move = $_GET["move"];

// Get current position (order)
$position = mysql_fetch_assoc( mysql_query("SELECT orderid FROM table WHERE id = " . $objectid) );

switch($move)
{
case "up": $position--; // subtract one from position. up = down in this case
case "down": $position++; // vice versa
}

// Get the number of rows
$numrows = mysql_num_rows( mysql_query("SELECT * FROM table") );

// Check to see if it doesn't go below 0 and not above the number of rows
if($position >= 0 && $position <= $numrows)
{
// Update the order of the row
mysql_query("UPDATE table SET orderid = " . $position . " WHERE id = " . $objectid);
}

 

I just pulled that off of the top of my head, so some parts may be wrong.

 

How then would I shift the order of all of the other rows?

 

Thanks!!

[Fadion]

There's no need to shift the order of all the other rows, just the next or previous row. In the url print the order that row actually has, so if a row has an order of 5 and want to move it up: index.php?order=5&move=up

 

<?php
if(isset($_GET['order'])){
     $order = $_GET['move'];
     $order = (int) $_GET['order'];
     if($order = 'up){
          $nextorder = $order + 1;
          //swap the order values from the two consecutive rows
          $results = mysql_query("UPDATE table SET order=$nextorder WHERE order=$order");
          $resultsNext = mysql_query("UPDATE table SET order=$order WHERE order=$nextorder");
     } else{
          $prevorder = $order - 1;
          //swap the order values from the two consecutive rows
          $results = mysql_query("UPDATE table SET order=$prevorder WHERE order=$order");
          $resultsNext = mysql_query("UPDATE table SET order=$order WHERE order=$nextorder");          
     }
}
?>

[Brandon Jaeger]

Thanks a lot! If I have any problems I'll come back here. If not, I'll mark it as 'solved' once I implement it

[Brandon Jaeger]

Okay, so I've edited it a bit and it's not working right. It doesn't swap them correctly.

 

	$query = mysql_query("SELECT * FROM content WHERE position = 0");

while($row = mysql_fetch_array($query))
{
	echo "id: " . $row[id] . "<br \>\n";
	echo "title: " . $row[title] . "<br \>\n";
	echo "content: " . $row[content] . "<br \>\n";
	echo "order num: " . $row[ordernum] . "<br \>\n";
	echo "position: " . $row[position] . "<br \>\n";
	echo "show: " . $row[show] . "<br \>\n";
	echo "<a href='?id=" . $row[ordernum] . "&move=up'>Move up</a> | <a href='?id=" . $row[ordernum] . "&move=down'>Move down</a>\n";
	echo "<br \><br \>\n";
}

if(isset($_GET['move']))
{
	$order = $_GET['id'];
	$move = $_GET['move'];

	if($move == "down")
	{
		$nextorder = $order + 1;
		//swap the order values from the two consecutive rows
		$results = mysql_query("UPDATE content SET ordernum=" . $nextorder . " WHERE ordernum=" . $order);
		$resultsNext = mysql_query("UPDATE content SET ordernum=" . $order . " WHERE ordernum=" . $nextorder);
	}
	else 
	{
		$prevorder = $order - 1;
		$nextorder = $order + 1;
		//swap the order values from the two consecutive rows
		$results = mysql_query("UPDATE content SET ordernum=" . $prevorder . " WHERE ordernum=" . $order);
		$resultsNext = mysql_query("UPDATE content SET ordernum=" . $order . " WHERE ordernum=" . $nextorder);          
	}
}

[Brandon Jaeger]

Nevermind, guys. I don't want to do it like this anymore.