• 0

PHP + MySQL: Changing order of database entries


Question

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?

5 answers to this question

Recommended Posts

  • 0

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 ;)

  • 0

@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 by JoeC
  • 0

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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.