• 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

    • To be fair, I haven't seen a BSOD since years ago, seriously. Windows has been pretty stable for me always.
    • Nobody is "shoving" anything down anybody's throats. The feature is turned off by default, so the user needs to explicitly enable it. How is that "shoving down" anything? Again: this is OPT-IN. If you don't like the feature for whatever reason, just ignore it or turn it off if you already enabled it.
    • Kioxia is Toshiba rebranded? Did not know that. 
    • I use to buy only Samsung and Crucial MX but things have change a lot in the last 5 years.   Western Digital Black ssd are very good. Corsair MP too (specially when it comes to gen 5) The Patriot 2TB Viper VP4300 Lite is a good cheap option with decent perf Crucial Txxx is pretty good too. I had bad exp with TeamGroup when they initially came out but i bought two TEAMGROUP T-Force Vulcan Z 2TB SLC Cache for my new computer because they were the cheapest 2 tb with any kind of cache and i've been pleasantly surprised.   You got to do your own research. Does it have dram cache? If yes which type of dram cache. Does it have a large SLC cache? If so how large is it? 20% of the drive? 30% of the drive? For example the TG 2TB i posted above has a 650GB SLC cache which is about 30% of the drive capacity. Is it TLC or QLC. I'd avoid QLC drive without any type of cache unless it's for storage they tend to perform badly with small writes. I'm not an expert so do your own research  https://www.techpowerup.com/ , https://www.tweaktown.com/ and https://www.tomshardware.com/ are usually good sources to know the real spec of a ssd and nvme specially the cache since a lot of manufacturer are not clear about this.
  • Recent Achievements

    • Week One Done
      SmileWorks Dental earned a badge
      Week One Done
    • Community Regular
      vZeroG went up a rank
      Community Regular
    • Collaborator
      Snake Doc earned a badge
      Collaborator
    • Week One Done
      Snake Doc earned a badge
      Week One Done
    • One Month Later
      Johnny Mrkvička earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      582
    2. 2
      Michael Scrip
      199
    3. 3
      ATLien_0
      198
    4. 4
      +FloatingFatMan
      129
    5. 5
      Xenon
      125
  • Tell a friend

    Love Neowin? Tell a friend!