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

#1 / Dec 09, 2010 1:31am

Ludovic-r

66 posts

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 :

$(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 :

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

And in the view :

<li id="<?php echo $row->title."_".$i++; ?>">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!

#2 / Dec 09, 2010 4:10am
eoinmcg
311 posts
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.
```
<li id="<?php echo "item-".$row->id; ?>"><?php echo $row->title; ?></li>
```
also, my preferred method is to serialise the data and send it like so:
```
data: $(this).sortable("serialize"),
```
here’s some code that will update the rank of each pic
```
$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);

        }
```
#3 / Dec 09, 2010 5:17am

Ludovic-r
66 posts

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 ?
#4 / Dec 09, 2010 5:33am
eoinmcg
311 posts
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:
```
$( "#list" ).sortable({
        opacity: 0.6,  
        data: $(this).sortable("serialize"),
```
if i then do a var_dump($_POST) in my controller i get
```
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:
```
<li id="item-<?php echo $row->id?>">
```
or in your controller you’re not looking for:
```
$this->input->post('item')
```

#5 / Dec 09, 2010 6:00am

Ludovic-r

66 posts

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 :

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 :

$(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 :

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

Any idea?

Thanks for your help and time 😉

#6 / Dec 09, 2010 6:39am

eoinmcg

311 posts

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

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

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

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

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

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

<p>    }

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

#7 / Dec 09, 2010 6:50am

Ludovic-r
66 posts

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

#8 / Dec 09, 2010 7:17am

eoinmcg

311 posts

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:

$('#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>

#9 / Dec 09, 2010 7:43am

Ludovic-r
66 posts

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!
#10 / Dec 09, 2010 8:49am

eoinmcg
311 posts

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.
#11 / Dec 09, 2010 4:40pm

Ludovic-r
66 posts

Yes, I’ve done exactly what you said, the list isn’t be draggable anymore.

If I replace with the last js code it works but with no result in the DB. With firebug I have :

order item[]=174&item;[]=171&item;[]=175 (in the POST)

and still :

Debugging

Total items sent: 1

UPDATE `ft_upload_data` SET `id` = NULL, `order` = 0 WHERE `id` IS NULL
#12 / Dec 10, 2010 3:21am

eoinmcg
311 posts

i’ve created a small proof of concept script here: https://gist.github.com/735918

you’ll still need to hook it into your codeigniter app, but hopefully it’ll give you an idea of where you’re going wrong.
#13 / Dec 10, 2010 6:22am

Ludovic-r
66 posts

Okay! I found the problem :

It works perfectly when you put the JS Script in the Header (with DB, controller and so on…) BUT in my case I put the js in a file called main.js so I don’t use the $(document).ready(function () {...}); and because of that it doesn’t work!

Have you a trick to use the JS script in a separated file (main.js for me) or use the $(document).ready(function () {...}); in this one? I can’t put the “sortable” function in my header!

Otherwise I would like to thank you sooooo much for your time that’s really really helpful!

You’re the man!
#14 / Dec 10, 2010 6:48am

eoinmcg
311 posts

i always use external files for my js; keeps thing clean and tidy.

there’s nothing stopping you wrapping your jquery snippets in main.js with the $(document).ready(function () {...});
#15 / Dec 10, 2010 7:45am

Ludovic-r
66 posts

Yeah, I tried that but it don’t work

Thread

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

eoinmcg

Ludovic-r

Username

Password

Thread