Reordering Database Fields With JQuery UI Sortable

Question

I have a database table that contains a list of items. The relevant fields are "item name" and "list_order".

I want to be able to re-order the "list_order" column in my table by triggering an ajax call every time an item is sorted.

For example, if I have the following data

Milk     1
Cookies  2
Cereal   3

I would like to use JQuery UI to drag Cereal to the top of the list and have this reflected in the database as

Milk     2
Cookies  3
Cereal   1

Can anyone point me to examples or links, or provide an explanation on how to do this? TIA.

David Houde · Accepted Answer · 2012-01-12 19:59:29Z

2

You should be able to make an ajax call to the php script with an array of list_position=>item_id and then in PHP loop through said array with

update table set list_position='$key' where id='$val';

and then of course you would sort by list_position when selecting data on future queries.

Edit: Just found this example online @ http://wil-linssen.com/entry/extending-the-jquery-sortable-with-ajax-mysql/

JS:

<script type="text/javascript"> 
// When the document is ready set up our sortable with it's inherant function(s) 
$(document).ready(function() { 
  $("#test-list").sortable({ 
    handle : '.handle', 
    update : function () { 
      var order = $('#test-list').sortable('serialize'); 
      $("#info").load("process-sortable.php?"+order); 
    } 
  }); 
}); 
</script>

HTML:

<pre> 
    <div id="info">Waiting for update</div> 
</pre> 
<ul id="test-list"> 
  <li id="listItem_1"> 
    <img src="arrow.png" alt="move" width="16" height="16" class="handle" /> 
    <strong>Item 1 </strong>with a link to <a href="http://www.google.co.uk/" rel="nofollow">Google</a> 
  </li> 
  <li id="listItem_2"> 
    <img src="arrow.png" alt="move" width="16" height="16" class="handle" /> 
    <strong>Item 2</strong> 
  </li> 
  <li id="listItem_3"> 
    <img src="arrow.png" alt="move" width="16" height="16" class="handle" /> 
    <strong>Item 3</strong> 
  </li> 
  <li id="listItem_4"> 
    <img src="arrow.png" alt="move" width="16" height="16" class="handle" /> 
    <strong>Item 4</strong> 
  </li> 
</ul> 
<form action="process-sortable.php" method="post" name="sortables"> 
  <input type="hidden" name="test-log" id="test-log" /> 
</form>

PHP:

<?php 
/* This is where you would inject your sql into the database 
but we're just going to format it and send it back 
*/ 
foreach ($_GET['listItem'] as $position => $item) : 
  $sql[] = "UPDATE `table` SET `position` = $position WHERE `id` = $item"; 
endforeach; 
print_r ($sql); 
?>

answered Jan 12, 2012 at 19:59

David Houde

4,7881 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mu is too short Over a year ago

You could do the update in a single UPDATE with something like update ... set position = case id when ... end where id in (...), basically use a CASE as the SQL version of an associative array.

Nick Pickering Over a year ago

I'm having an issue where the number of cases being updated is into the hundreds, and the resulting query is slow using CASE WHEN THEN method.

Jon Over a year ago

Is it possible to send the SQL UPDATE for only the affected rows? So if listItem_2 and listItem_3 are swapped around, it should only update those two and leave 1 and 4 alone? (to minimise server resources for example when updating hundreds of rows at once when only 5 need updating)

Collectives™ on Stack Overflow

Reordering Database Fields With JQuery UI Sortable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related