• 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.
  • Posts

    • Oh no... here we go again. Tha same sh*t that happaned to Mail & Calendar...
    • I too have worked with pro gear for decades and so I found the interface to be unintuitive from both the pro and casual side (a rare "accomplishment" by what are obviously otherwise skilled coders, hehe). I eventually got it to work, thanks for offering, but I found other compatibility issues with my own use case, so I just dropped it entirely. Right now, I just use an analog line out/line in approach which works as expected across all usage scenarios. With both machines on the same power block/outlet, I'm not getting any analog hum or hiss. My next step will be to try the updated Multiplicity 4 when a bug, addressing this very issue unfortunately, is resolved. Knowing Stardock, that could be tomorrow or five years from now, so I check back every few months to see it it's fixed and I want to upgrade. Again, thanks for offering to help.
    • We recognize that performance can use some improvements, and we continue to work on improving it. However, it's worth noting that massive performance improvements don't happen overnight, they take a lot of work and effort, and in most cases, the improvements are more noticeable when you compare across several updates. That said, Files is open-source and everyone is invited to help with these efforts 🙂
    • A lot of effort has gone into improving stability and Files Preview now has a 99% crash free rate. These improvements will make their way to Files Stable when v4 is released later this year. If you're still experiencing issues, please report them on GitHub or Discord so we can track them properly.
  • Recent Achievements

    • Rookie
      Snake Doc went up a rank
      Rookie
    • First Post
      nobody9 earned a badge
      First Post
    • One Month Later
      Ricky Chan earned a badge
      One Month Later
    • First Post
      leoniDAM earned a badge
      First Post
    • Reacting Well
      Ian_ earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      495
    2. 2
      Michael Scrip
      203
    3. 3
      ATLien_0
      197
    4. 4
      Xenon
      137
    5. 5
      +FloatingFatMan
      115
  • Tell a friend

    Love Neowin? Tell a friend!