Geek Answers Handbook

What is the best approach to sort MySQL table rows by user choice?

Suppose we have a MySQL table (id, name) and a list view of the table with drag and drop. The user drags the 90th row and puts it on the 10th row. What is the best approach to save this new sort?

  • I do not mean to keep sorting for every use separately
  • The HTML/JavaScript is not the problem

I have seen some programmers add a weight column to a table by setting a lower number at the top of the table. In my example, it will be from 1 to 100. The problem is in the case of the above example (from 90 to 10), which requires updating 81 lines. 90 changes by 10 and every 10 to 89 increments. Is it effective in MySQL? Is there a better solution?
Another way, perhaps, is to save the new order as a row in another table , but in this case we lose the sorting of MySQL at the search stage!

I remember when we studied the structure of three trees at the university as an indexing tool, we said wow! Even when we took advantage of each one bit of a byte, 1.5 GB of pure text data stored less than 500KB !!! So now I'm still looking to find the best answer!

+6
source share
3 answers

Even programming languages, when updating indexes for an array that had an object added to a previously occupied index, add one to each index.

You will need to update the index for each individual row.

The cleanest way to do this, however, would be something like this (where people are a non-trivial table, of course):

 UPDATE people SET index = index + 1 WHERE index BETWEEN $newIndex AND $oldIndex;

depending on your database, between may include or exclude high and low numbers. Just make sure you know how he treats them!

+2
source

Instead of adding the weight column to the MYSQL table, add the next_object column to the table.

It will function as a linked list (if you are familiar with this). Each entered object points to the next object.

Go through the script to move the object of position 90 to position 10.

First you need to update the 89th object so that it now points to the 91st object (new 90th object)

Then you need to update the 9th object and point it to the 90th object (new 10th object)

Finally, you need to update the 90th object (new 10th object) and point it to the 10th object (new 11th object)

And, of course, I want to say that I update the next_object field to the object I'm talking about.

What exactly could you put as the value in the next_object field? Just an object identifier or something like that.

I just came up with this system as an alternative when I started writing this answer, so I'm not sure if this is the most efficient way. But hey, updating 3 objects is better than the potential 100,000 (if you had 100,000 objects).

0
source

Finally, I did a sort using this method: I created a weight column. Each row after insertion receives its identifier as a weight value. This ensures that a new line is completed. weight column - type FLOAT. JQuery helps get a new row position, as well as the prev () and next () table. Correctly new, we have three situations that are commented in in a tear-off fragment (after extracting identifiers from strings and sending Ajax to PHP):

  #table of DB $table = $_POST['table']; #the id of the row on top $t = $_POST['t']; #the id of the row on middle $m = $_POST['m']; #the id of the row on bottom $b = $_POST['b']; switch ('') { #top is empty, so the row is droped on top case $t: #we set middle weigh 0.5 lower than its bottom $query = " UPDATE `$table` m, `$table` b SET m.`weight` = b.`weight`-0.5 WHERE m.`id` = $m AND b.`id` = $b "; break; #bottom is empty, so the row is droped on the end case $b: #we set middle weigh 0.5 upper than its top $query = " UPDATE `$table` m, `$table` t SET m.`weight` = t.`weight`+0.5 WHERE m.`id` = $m AND t.`id` = $t "; break; #values are not empty, so the row is droped in the middle part default: #we set middle weigh exactly between top and bottom $query = " UPDATE `$table` m, `$table` t, `$table` b SET m.`weight` = (t.`weight`+b.`weight`)/2 WHERE m.`id` = $m AND t.`id` = $t AND b.`id` = $b "; break; } Yii::app()->db->createCommand($query)->query();

Even after 45-fold dividing 1 by 2, the value of the floating-point value will not drop to zero, so I am now fixing this solution!

0
source

More articles:


All Articles