Store li order in Mysql DB with Jquery sortable

Question

I have a sortable list in my site which saves the order of the list items with an ajax request to a php page. The problem is when the id of the LI (which is generated by the database) gets above 999 or so it begins to error as the array I'm sending to the PHP page becomes too long.

Is there a better way of doing this?

Currently my Jquery/ ajax is:

$(function() {
$('#sortable').sortable({
    update: function(event, ui) {
        var order = []; 

        $('#sortable li').each( function(e) {
            order[ $(this).attr('id') ] =  $(this).index() + 1;
        });

        var request = $.ajax({
            url: "ajax/save_order.php",
            type: "POST",
            data: {positions : order},
            dataType: "json"
        });
    }
});
});

My PHP page is:

$positions = $_POST['positions'];

foreach ($positions as $id_section => $order)
{
    $sql = sprintf("
        UPDATE      section_order
        SET     id_order = %d
        WHERE       id_section = %d
    ",
        $order,
        $id_section
    );
    $result = mysql_query($sql);
}

I've been trying to work out how to get the data from the sortable into a multidimensional array in jquery, but currently am drawing a blank.

I am confused by this.. "gets above 999 or so it begins to error" What's the error? — superphonic, Nov 29 '13 at 17:21
you could send your data as a list of CSV, I assume your `ids` are all numbers, so they should be kept in the array in the order they are placed. This may reduce the amount of data sent to the server, but you would have to accomodate your php for this — hanzo2001, Nov 29 '13 at 19:42
@superphonic there is no error but the console seems to suggest it's taking so long to count all the rows in the array that it times out. — evilscary, Dec 02 '13 at 10:22

score 1 · Accepted Answer · edited May 23 '17 at 12:25

OK I see. Seems a bit unwieldy for a user to manually drag and drop items in a list of over 1000 items. But I don't know how/why you are doing this so will assume it works for your application.

The below solution is actually from this SO question jQuery UI Sortable, then write order into a database

jQuery Sortable has a serialize function that takes care getting the order for you. Use it like below:

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

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

And in the save_order.php file, just check what the posted data looks like and loop though it, should be able to do something like:

<?php
    foreach ($_POST['each_item_whatever...'] as $value) {

        // Database stuff

    }
?>

Store li order in Mysql DB with Jquery sortable

1 Answers1