Order By Mysql

Reorder table using single query with php, jquery UI, MySql



$(function() {

$( "#sortable" ).sortable({
update: function( event, ui ) {
var sortedIDs = $( "#sortable" ).sortable( "toArray" );

type: "POST",
url: "general_ajax.php",
data: { mode: "SortMustKnows", sortedIDs: sortedIDs },
success : function(data){
alert( data );

$( "#sortable" ).disableSelection();



<ul id="sortable">

<li id="1"><span></span>Item 1</li>
<li id="2"><span></span>Item 2</li>
<li id="3"><span></span>Item 3</li>
<li id="4"><span></span>Item 4</li>
<li id="5"><span></span>Item 5</li>


Mysql Query

$sortedIDs = '5,4,3,2,1';
@mysql_query("SELECT @i:=0;");
@mysql_query("UPDATE `table_name` SET  must_order = @i:=@i+1 where `primary_key` IN ($sortedIDs) ORDER BY FIELD(`primary_key`,$sortedIDs)");

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s