PHP + MySQL: Changing order of database entries

[Andos]

Hey there.

I have a MySQL table with several rows, each one beloning to a certain user-id.

The user is able to add and remove rows (items) as they wish, but I want to add a little feature to change their order. (like move up/down on the list)

What is the best way of doing this?

I thought of having a sort-value that is given so you can order the elements by it, but when a user deletes an entry i guess all those values has to be rebuilt in some way. But how to do it efficiently and easy?

[JoeC]

New column; position. And just select in the SQL query like this:

SELECT * FROM users ORDER BY position ASC

so assuming you have 1, 4, 283, 2 and 8 in your position fields, it would select as 1, 2, 4, 8, 283.

[Alex Suraci]

He's talking about ordering the actual MySQL tables/rows I think. Sorry, I can't help though.

Edited April 25, 2006 by mynimal

[Andos]

Hmm I've tried this and seems to work out:

When a new entry is inserted, it gets the highest sort_id in the list +1. When you want to move an item up/down, you simply swap the two item's sort_id.

When an item is deleted you say sort_id-- to all entries which has a sort_id greater than the one that was just deleted.

If you have a better solution, please post ;)

[JoeC]

@myin: So am I :p

@Andos: Mine above, and for re-ordering, give them a list of all items, with a text box containing the current number in it. Then, any new ones just get the highest+1 as default. User can re-number all the like, and they'll fix the numbering as they go.

You could write a loop to go through and renumber all IDs higher than the one deleted. I wrote a script for that a while ago, I'll post in a sec when I find it, though you'll have to edit it yourself.

Edit: $seedid is from a posted textbox where the user types the number they want to delete. Can be from any sort of input. Changed the password etc :p

<?
$usr = "joezif_fdgf";
$pwd = "hdfghg";
$db = "joezif_fghfd";
$host = "localhost";

$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (!$cid) { print "ERROR: " . mysql_error() . "\n"; }

$seedid = $_POST["seedid"];
$password = $_POST["password"];

$query = " SELECT * FROM seeds ";
$result = mysql_query($query,$cid);

$numrows = mysql_num_rows($result);

$seedname = mysql_query("SELECT seedname FROM seeds WHERE seedid = '".$seedid."'",$cid);
$seed = mysql_fetch_assoc($seedname);
$myseed = $seed['seedname'];

if ($_SERVER['REQUEST_METHOD'] == "POST" && $password == $pwd) {

$sql = " DELETE FROM seeds WHERE seedid = '$seedid' ";
$result = mysql_query($sql, $cid);
if (mysql_error()) { print "Database ERROR: $sql " . mysql_error(); }

if (!$result || $numrows < 1) {
	die("There are no more fields in the table.");
}

for ($x = $seedid; $x <= $numrows; $x++)
	{
	$y = $x + 1;
	$query = "UPDATE seeds SET seedid=' " . $x . " ' WHERE seedid = " . $y;
	$result = mysql_query($query,$cid);
	$worked = "All fields succesfully renumbered.";

	if(mysql_affected_rows($cid) == -1) {
		die("Update failed, check SQL. Confirm DB is ok, look for duplicates before running again!");
	}

}

	$sql = " ALTER TABLE `seeds` PACK_KEYS =0 CHECKSUM =0 DELAY_KEY_WRITE =0 AUTO_INCREMENT = $numrows ";
	$query = mysql_query($sql);
	$autoinc = "Auto-increment adjusted correctly.";

print "All done, yada yada";
}

Make of that what you will, it was one of my first scripts, and I was learning.

Edited April 25, 2006 by JoeC

[PKHelloNasty]

While sorting the data within MySQL seems like a good idea, it really is not, unless you send all the commands to the database within a procedure. If this precaution is not taken, you will surley screw up your database when two users try to sort two items at the same time.

Go read up on procedures here:

http://dev.mysql.com/doc/refman/5.0/en/sto...procedures.html

This way you can ensure all the SQL Commands to be sent to MySQL will be executed in the proper order, without interuption.