[php] jQuery UI Sortable, then write order into a database

I want to use the jQuery UI sortable function to allow users to set an order and then on change, write it to the database and update it. Can someone write an example on how this would be done?

This question is related to php jquery mysql jquery-ui jquery-ui-sortable

The answer is


The jQuery UI sortable feature includes a serialize method to do this. It's quite simple, really. Here's a quick example that sends the data to the specified URL as soon as an element has changes position.

$('#element').sortable({
    axis: 'y',
    update: function (event, ui) {
        var data = $(this).sortable('serialize');

        // POST to server using $.post or $.ajax
        $.ajax({
            data: data,
            type: 'POST',
            url: '/your/url/here'
        });
    }
});

What this does is that it creates an array of the elements using the elements id. So, I usually do something like this:

<ul id="sortable">
   <li id="item-1"></li>
   <li id="item-2"></li>
   ...
</ul>

When you use the serialize option, it will create a POST query string like this: item[]=1&item[]=2 etc. So if you make use - for example - your database IDs in the id attribute, you can then simply iterate through the POSTed array and update the elements' positions accordingly.

For example, in PHP:

$i = 0;

foreach ($_POST['item'] as $value) {
    // Execute statement:
    // UPDATE [Table] SET [Position] = $i WHERE [EntityId] = $value
    $i++;
}

Example on jsFiddle.


Try with this solution: http://phppot.com/php/sorting-mysql-row-order-using-jquery/ where new order is saved in some HMTL element. Then you submit the form with this data to some PHP script, and iterate trough it with for loop.

Note: I had to add another db field of type INT(11) which is updated(timestamp'ed) on each iteration - it serves for script to know which row is recenty updated, or else you end up with scrambled results.


This is my example.

https://github.com/luisnicg/jQuery-Sortable-and-PHP

You need to catch the order in the update event

    $( "#sortable" ).sortable({
    placeholder: "ui-state-highlight",
    update: function( event, ui ) {
        var sorted = $( "#sortable" ).sortable( "serialize", { key: "sort" } );
        $.post( "form/order.php",{ 'choices[]': sorted});
    }
});

You're in luck, I use the exact thing in my CMS

When you want to store the order, just call the JavaScript method saveOrder(). It will make an AJAX POST request to saveorder.php, but of course you could always post it as a regular form.

<script type="text/javascript">
function saveOrder() {
    var articleorder="";
    $("#sortable li").each(function(i) {
        if (articleorder=='')
            articleorder = $(this).attr('data-article-id');
        else
            articleorder += "," + $(this).attr('data-article-id');
    });
            //articleorder now contains a comma separated list of the ID's of the articles in the correct order.
    $.post('/saveorder.php', { order: articleorder })
        .success(function(data) {
            alert('saved');
        })
        .error(function(data) { 
            alert('Error: ' + data); 
        }); 
}
</script>
<ul id="sortable">
<?php
//my way to get all the articles, but you should of course use your own method.
$articles = Page::Articles();
foreach($articles as $article) {
    ?>
    <li data-article-id='<?=$article->Id()?>'><?=$article->Title()?></li>
    <?
}               
?>   
</ul>
   <input type='button' value='Save order' onclick='saveOrder();'/>

In saveorder.php; Keep in mind I removed all verification and checking.

<?php
$orderlist = explode(',', $_POST['order']);
foreach ($orderlist as $k=>$order) {
  echo 'Id for position ' . $k . ' = ' . $order . '<br>';
}     
?>

I can change the rows by following the accepted answer and associated example on jsFiddle. But due to some unknown reasons, I couldn't get the ids after "stop or change" actions. But the example posted in the JQuery UI page works fine for me. You can check that link here.


Thought this might help as well. A) it was designed to keep payload to its minimum while sending back to server, after each sort. (instead of sending all elements each time or iterating through many elements that server might chuck out) B) I needed to send back custom id without compromising the id / name of the element. This code will get the list from asp.net server and then upon sorting only 2 values will be sent back: The db id of sorted element and db id of the element next to which it was dropped. Based on those 2 values, server can easily identify the new postion.

<div id="planlist" style="width:1000px">
    <ul style="width:1000px">
       <li plid="listId1"><a href="#pl-1">List 1</a></li>
       <li plid="listId2"><a href="#pl-2">List 1</a></li>
       <li plid="listId3"><a href="#pl-3">List 1</a></li>
       <li plid="listId4"><a href="#pl-4">List 1</a></li>
    </ul>
    <div id="pl-1"></div>
    <div id="pl-2"></div>
    <div id="pl-3"></div>
    <div id="pl-4"></div>
</div>
<script type="text/javascript" language="javascript">
    $(function () {
        var tabs = $("#planlist").tabs();
        tabs.find(".ui-tabs-nav").sortable({
            axis: "x",
            stop: function () {
                tabs.tabs("refresh");
            },
            update: function (event, ui) {
                //db id of the item sorted
                alert(ui.item.attr('plid'));
                //db id of the item next to which the dragged item was dropped
                alert(ui.item.prev().attr('plid'));

                //make ajax call
            }
        });
    });
</script>

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to jquery

How to make a variable accessible outside a function? Jquery assiging class to th in a table Please help me convert this script to a simple image slider Highlight Anchor Links when user manually scrolls? Getting all files in directory with ajax Bootstrap 4 multiselect dropdown Cross-Origin Read Blocking (CORB) bootstrap 4 file input doesn't show the file name Jquery AJAX: No 'Access-Control-Allow-Origin' header is present on the requested resource how to remove json object key and value.?

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to jquery-ui

How to auto adjust the div size for all mobile / tablet display formats? jQuery not working with IE 11 JavaScript Uncaught ReferenceError: jQuery is not defined; Uncaught ReferenceError: $ is not defined Best Practice to Organize Javascript Library & CSS Folder Structure Change table header color using bootstrap How to get HTML 5 input type="date" working in Firefox and/or IE 10 Form Submit jQuery does not work Disable future dates after today in Jquery Ui Datepicker How to Set Active Tab in jQuery Ui How to use source: function()... and AJAX in JQuery UI autocomplete

Examples related to jquery-ui-sortable

jQuery UI Sortable, then write order into a database Using jQuery UI sortable with HTML tables Sort divs in jQuery based on attribute 'data-sort'? Jquery sortable 'change' event element position jQuery UI Sortable Position