Save a re-ordered list with Jquery Sortable to DB [SOLVED]

[eluser]Ludovic-r[/eluser]
Hi!

I'm working on a lightweight CMS and I need to store some datas to my DB. The datas are generated by Ajax with Jquery Sortable.

Actually, it's a list where you can drag and drop items to re-arrange the order.

Let me explain :

In my DB :

I have fields called : ID (the id :p) / order (which contains only numbers) / title (the title of the picture)

In my .js file :

Code:

$(function() {
    $( "#list" ).sortable({
        opacity: 0.6,
        cursor: 'move',
        tolerance: 'pointer',
        revert: true,
        items:'li',
        placeholder: 'state',
        forcePlaceholderSize: true,
        update: function(event, ui){
    
//send datas to controller ----------------
            $.ajax({
                url: "/codeigniter/index.php/admin/save_order",
                type: 'POST',
                data: {
                    'order': $( "#list" ).sortable('toArray'),
                },
                success: function (data) {
                    $("#test").html(data);
                }

            });
//-------------------------------                                
            }
                
        });

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

The Ajax request give me something like : Pic_0 [0], Pic_1 [1], Pic_2 [2], Pic_3 [3],
That's the order of the list. If I drag and drop a "Pic" the Array result change (example : Pic_2 [2], Pic_1 [1], Pic_0 [0], Pic_3 [3],)

Ok then in my controller :

Code:

function save_order()
    {    
        $order = $this->input->post('order');    
    }

And in the view :

Code:

<li id="&lt;?php echo $row->title."_".$i++; ?&gt;">My_item</li>

Alright the question is : How can I update the order in my DB? I'm a bit lost with all that informations so I just need a way to Update the field "order" in the DB with the order saved before.

Any help will be very very very appreciated! Thanks!

[eluser]eoinmcg[/eluser]
i've no idea how your database schema looks like but you will need to add a 'rank' field as a means to store the order of the pictures.

now when ajax sends you the array you simply need to cycle through the array and and update the rank field for each picture.

to do so it will be easier to post the item's id rather than title. e.g.

Code:

<li id="&lt;?php echo "item-".$row->id; ?&gt;">&lt;?php echo $row->title; ?&gt;</li>

also, my preferred method is to serialise the data and send it like so:

Code:

data: $(this).sortable("serialize"),

here's some code that will update the rank of each pic

Code:

$items = $this->input->post('item');
    $total_items = count($this->input->post('item'));


    for($item = 0; $item < $total_items; $item++ )
    {

            $data = array(
                'id' => $items[$item],
                'rank' => $rank = $item
            );

             $this->db->where('id', $data['id']);

            $this->db->update('your_table', $data);

        }

[eluser]Ludovic-r[/eluser]
Many many thanks for your help! It's really appreciated!

So I've tried what you said and the "rank" field in the DB does not update (it stay on 0), when I use Firebug on Firefox it says that in the POST answer (submitted with Ajax) : order item[]=0&item;[]=1_&item;[]=2

Maybe the problem is with "serialize"?

Any idea ?

[eluser]eoinmcg[/eluser]
did you change your li s as i did in the code sample? that will will post an array called 'item' to your app.

you can check this by logging it to the firebug console:

Code:

$( "#list" ).sortable({
        opacity: 0.6,  
        data: $(this).sortable("serialize"),

if i then do a var_dump($_POST) in my controller i get

Code:

array
  'item' =>
    array
      0 => string '1' (length=1)
      1 => string '7' (length=1)
      2 => string '3' (length=1)
      3 => string '4' (length=1)
      4 => string '2' (length=1)
      5 => string '5' (length=1)
      6 => string '6' (length=1)

and the new order saves to the database. my guess is that you either have not changed your list to:

Code:

<li id="item-&lt;?php echo $row->id?&gt;">

or in your controller you're not looking for:

Code:

$this->input->post('item')

[eluser]Ludovic-r[/eluser]
I've copy/paste your code with the name of my table, I've re-checked the code and everything seems to be ok :

Controller :

Code:

function order()
    {    
        $items = $this->input->post('item');
            $total_items = count($this->input->post('item'));


            for($item = 0; $item < $total_items; $item++ )
            {

                    $data = array(
                        'id' => $items[$item],
                        'order' => $order = $item
                    );

                     $this->db->where('id', $data['id']);

                    $this->db->update('ft_upload_data', $data);

                }
    }

Js :

Code:

$(function() {
    $( "#list" ).sortable({opacity: 0.6,
        data:$(this).sortable("serialize"),
        cursor: 'move',
        tolerance: 'pointer',
        revert: true,
        placeholder: 'state',
        forcePlaceholderSize: true,
        update: function(event, ui){
    
//send POST data ----------------
            $.ajax({
                url: "/codeigniter/index.php/admin/order",
                type: 'POST',
                data: {
                    'order': $( "#list" ).sortable("serialize"),
                },
                success: function (data) {
                    $("#test").html(data);
                }

            });
//-------------------------------                                
            }
                
        });

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

View :

Code:

<li id="&lt;?php echo "item-".$row->id; ?&gt;">My item</li>

Any idea?

Thanks for your help and time

[eluser]eoinmcg[/eluser]
ok, time for some debugging. i see that your ajax request will populate <div id="test"></div> with whatever the server returns...

so, in your controller, let's output the sql statements to see what's being done and then exit. this will then be put into the #test div

Code:

function order()
    {    
        $items = $this->input->post('item');
        $total_items = count($this->input->post('item'));

        echo '<h3>Debugging</h3>';
        echo "<p>Total items sent: $total_items</p>";
            for($item = 0; $item < $total_items; $item++ )
            {

                    $data = array(
                        'id' => $items[$item],
                        'order' => $order = $item
                    );

                     $this->db->where('id', $data['id']);

                    $this->db->update('ft_upload_data', $data);
                    echo '<br />'.$this->db->last_query();
                }
                exit;

    }

btw, i'm assuming that id is your primary key in ft_upload_data

[eluser]Ludovic-r[/eluser]
Ok,

That's what I end up with :


Debugging

Total items sent: 1

UPDATE `ft_upload_data` SET `id` = NULL, `order` = 0 WHERE `id` IS NULL


Yes ID is my primary key in ft_upload_data

[eluser]eoinmcg[/eluser]
ok, looks like an empty array is getting posted. so the problem is somewhere with the jquery.

i've pasted an amended version of my javascript below that works fine for me:

Code:

$('#reorder').sortable({
        opacity: '0.5',
        update: function(e, ui){
            newOrder = $(this).sortable("serialize");
            console.log(newOrder);
            $.ajax({
                    url: "/admin/dashboard/save_order",
                    type: "POST",
                    data: newOrder,
                    // complete: function(){},
                    success: function(feedback){
                         $("#test").html(feedback);
                         //$.jGrowl(feedback, { theme: 'success' });
                    }
                    });
                }
    });

//and this is what my list looks like (ordered by rank)
<ol id="reorder">
<li id="item-4">Item #4</li>
<li id="item-1">Item #1</li>
<li id="item-2">Item #2</li>
<li id="item-3">Item #3</li>
</ol>

[eluser]Ludovic-r[/eluser]
When I use your amended version the list isn't sortable anymore (I mean that you can't drag and drop items).
It seems that there's a problem with the JS function.

Sorry for stealing your time!

[eluser]eoinmcg[/eluser]
did you rename your list to reorder and change the destination url?

it's kinda hard to help with debugging across a forum but my advice is to break the problem into small pieces. it looks like the problem lies with the javascript part. remember firebug & console.log() are your friends!

let me know how you get on.