Update database order of list using Jquery Sortable

Question

Hello I have a list which looks like this: https://i.sstatic.net/bzgTu.jpg In this list you can drag items in the different lists. Now, I want to update the database values of these items once you drag them. Ideally I want to update only once you press a "Save" button, but you can ignore this if it is to much work.

I use this to drag the items: http://johnny.github.io/jquery-sortable/, which is very similar to jQuery UI's version.

I have absolutely now clue how Ajax works, and I would love some help. This is the list structure:

<ol class="nav nav-tabs nav-stacked sortable" style=
"margin-top: 25px;">
    <li class="page_select menuid_1" style=
    "color: #1b1b1b; padding: 40px 20px 10px; margin-top: 10px; font-weight: bold; font-size: 14px;">
    Main Menu</li>

    <li class="page_select menuid_1 position_1 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId1" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=1">Home</a>

        <ol class="nav nav-tabs nav-stacked"></ol>
    </li>

    <li class="page_select menuid_1 position_4 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId2" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=2">About Me</a>

        <ol class="nav nav-tabs nav-stacked">
            <li class=
            "page_select menuid_1 position_1 online">
                <span class="pull-right" style=
                "margin: 8px 32px 0 0"><a class=
                "deleteToggle icon-trash pull-right"
                data-toggle="modal" href="#deleteModal"
                id="deleteId12" style=
                "font-style: italic"></a></span><a href="pages.php?page=12">Test</a>
            </li>

            <li class=
            "page_select menuid_1 position_2 offline">
                <span class="pull-right" style=
                "margin: 8px 32px 0 0"><a class=
                "deleteToggle icon-trash pull-right"
                data-toggle="modal" href="#deleteModal"
                id="deleteId11" style=
                "font-style: italic"></a></span><a href="pages.php?page=11">Test
                Page</a>
            </li>
        </ol>
    </li>

    <li class="page_select menuid_1 position_4 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId3" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=3">Contact</a>

        <ol class="nav nav-tabs nav-stacked">
            <li class=
            "page_select menuid_1 position_9 offline">
                <span class="pull-right" style=
                "margin: 8px 32px 0 0"><a class=
                "deleteToggle icon-trash pull-right"
                data-toggle="modal" href="#deleteModal"
                id="deleteId7" style=
                "font-style: italic"></a></span><a href="pages.php?page=7">Test
                Page</a>
            </li>
        </ol>
    </li>

    <li class="page_select menuid_1 position_3 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId4" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=4">Portfolio</a>

        <ol class="nav nav-tabs nav-stacked"></ol>
    </li>

    <li class="page_select menuid_1 position_3 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId5" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=5">Shop</a>

        <ol class="nav nav-tabs nav-stacked"></ol>
    </li>

    <li class="page_select menuid_2" style=
    "color: #1b1b1b; padding: 40px 20px 10px; margin-top: 10px; font-weight: bold; font-size: 14px;">
    Footer Menu</li>

    <li class="page_select menuid_2 position_5 online">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId6" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=6">Sitemap</a>

        <ol class="nav nav-tabs nav-stacked"></ol>
    </li>

    <li class=
    "page_select menuid_2 position_9 offline">
        <span class="pull-right" style=
        "margin: 8px 32px 0 0"><a class=
        "deleteToggle icon-trash pull-right"
        data-toggle="modal" href="#deleteModal" id=
        "deleteId8" style=
        "font-style: italic"></a></span><a href=
        "pages.php?page=8">Last Page</a>

        <ol class="nav nav-tabs nav-stacked"></ol>
    </li>

    <li class="page_select menuid_3" style=
    "color: #1b1b1b; padding: 40px 20px 10px; margin-top: 10px; font-weight: bold; font-size: 14px;">
    aa

        <p style=
        "margin-left: 40px; font-size: 12px; color: #ccc;">
        No pages yet.</p>
    </li>
</ol>

The formatting is a bit shit, since it is generated by PHP. Feel free to suggest new classes and stuff if this will not do.

In the database I have 3 fields "position", "parent_id" and "menu_id". Position being the position in the list, parent_id is the id of the item it is nestled in and menu_id the menu in which it is in (In the screenshot you will notice different menus). All of these values have to update when you move an element.

Now, I understand this might be a lot of work, so I do not expect anyone to just hand me the code. I would very much appreciated if someone could help me out, in any way.

stackoverflow.com/questions/18902974/… have a look at the answer fiddle and the alert after draf drop may it helps — caramba
– caramba, Commented Nov 1, 2013 at 23:21
@caramba This not not seem to do anything for me, it never shows an alert, as it does in the jsfiddle. This might be because I'm not using the jQueryUI Sortable. All other jQueryUI options work though. EDIT: Alright, I switched to jQueryUI, this seems to do a thing or two. — Mitchel Jager
– Mitchel Jager, Commented Nov 2, 2013 at 0:44

greener · Accepted Answer · 2013-11-02 04:15:42Z

2

I've done similar recently in ASP. This is the basic idea which you can customize to your needs:

The HTML list has attribute iden which is the record id in the database:

<li iden="<%=rs("ID")%>" class="ui-state-default"><%=rs("TITLE")%></li>

The Javascript runs off the update event:

$("#order-block1").sortable({
        placeholder: "ui-state-highlight",
        forcePlaceholderSize: true,
        update: function( event, ui ) {
            var t = ""
            $(this).children('li').each(function(index, element) {
                t+= "UPDATE PROJECTS SET RANK = '" + (index+1) + "' WHERE ID =" + $(this).attr('iden') + ";"
            });

            $.ajax({
                url: 'updateOrder.asp?str=' + t, type: 'POST',
                success: function(data) {
                },
                error: function() {
                    alert('error updating order');
                }
            });
        }
    });

The server side (updateOrder.asp) could look something like:

set conn=Server.CreateObject("ADODB.Connection")
conn.Open db

str = request.QueryString("str")

a = split(str, ";")

for each x in a

    on error resume next

    conn.Execute x

next

conn.close

It basically creates a string with SQL code which is then split on the ; and using a loop, executing each sql command.

edited Nov 2, 2013 at 4:15

answered Nov 1, 2013 at 23:30

greener

5,06913 gold badges56 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mitchel Jager Over a year ago

My Ajax knowledge is incredibly small, what would I do with the $.ajax({code}) part?

Mitchel Jager Over a year ago

Alright, I got it to work, well except for the actual updating. I am getting the correct data right now, but that is all. I used a file "ajax.php" for the url, how would I make this file look like? This is the piece of code: pastebin.com/NTrj2P5Z

greener Over a year ago

See my update above. Your code will look something like a php version of the above with a split and a loop. So basically the data gets sent to your ajax.php which then splits the string into each sql command, then runs the commands in succession to update the database.

Collectives™ on Stack Overflow

Update database order of list using Jquery Sortable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related