CODEWITHSUNDEEP
phpjavascriptjqueryjquery-ui
pjbollinger
pjbollinger

Reputation: 41

How do I save JQuery UI sortable table positions to MySQL database?

What my application does is, loads a list of items into a table, via MySQL.

I can load the table fine, but I need to make it so the items in the table can be rearranged and their positions stored back into the MySQL server.

Here is the main page (workshops.php):

<?php
    require_once("connection.php");    
?>
<html>
    <head>
        <style type="text/css">
            body { position: relative; }
            #content { width: 742px; height: auto; position: relative; margin-left: auto; margin-right: auto; }
        </style>
        <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
  <script src="js/jquery.js"></script>
  <script src="js/jquery-ui.js"></script>

  <script>
    $(document).ready(function() {
        var fixHelper = function(e,ui){
            ui.children().each(function() {
                $(this).width($(this).width());
            });
            return ui;
        };
        $("#sortable tbody.contenet").sortable({
            helper: fixHelper,
            stop: function(event, ui)
            {alert("something changed");
                 //create an array with the new order
                order = $.map($(this).find('input'), function(el){
                    for (j=0; j < $(el).length; j++){
                        return $(el).attr('id') + '=' + j;
                    }
                });
                var message = "";
                for(i=0; i < order.length; i++){
                    message = message + order[i];
                    message = message + " ";
                }
                alert(message);
                //sorder = order.serializeArray();
                //alert(sorder);

                    $.ajax({
                    url: "updateworkshops.php",
                    type: "post",
                    data: order,
                    error: function (){
                        alert("theres an error with AJAX");
                    },
                    success: function(){
                        //alert("Saved.");
                    }
                });


            }

        });
    });

  </script>
    </head>
    <body>
        <div id="content">
            <p>Click on "+ Create new workshop" to create a new workshop".</p>
            <p>Click on the name of a workshop to add dates and times for when it is available and/or to make changes to it.</p>
            <br />
            <table>
                <!--Create Workshop-->
                <tr>
                    <td width="25%" bgcolor="6BF26B"><a href="create.php">+ Create new workshop</a></td>
                </tr>
                <tr><td bgcolor="FFFF00"><a href="settings.php">~ Edit settings</a></td>
                </tr></table><br />
                <form id="pickles" action="updateworkshops.php" method="post">
                <table id="sortable"><tbody class="contenet">
                <!--List Workshops-->
                <?php
                    $query = "SELECT * FROM workshops ORDER BY position";
                    $result = mysql_query($query);
                    while ($row = mysql_fetch_assoc($result)) {
                        echo "<tr bgcolor='{$row['color']}'>"; echo "\r\n";
                        echo "<td><input type=\"hidden\" id=\"order_".$row['id']."\" name=\"order_".$row['id']."\" value=\"".$row['position']."\" /><label class=\"handle\">[X]</label></td>"; echo "\r\n";
                        echo "<td><a href='edit.php?workshop_id={$row['id']}'>{$row['name']}</a></td>"; echo "\r\n";
                        echo "<td>{$row['description']}</td>"; echo "\r\n";
                        echo "</tr>"; echo "\r\n";
                    }
                ?>
                </tbody>
            </table>
            <input type="submit" name="submit" value="Submit" />
            </form>
        </div>
    </body>
</html>
<?php mysql_close(); ?>

And this is the updateworkshops.php page that it POST the data to:

<?php
    require_once("connection.php");

if(isset($_POST['submit'])) {
    while(list($key,$value) = each($_POST)){
        if(substr($key,0,5) == "order_"){
            $id = trim($key,'order_');
            $sql = 'UPDATE workshops SET position = '.$value.' WHERE id = '.$id;
            if(!mysql_query($sql)) {
                echo "SOMETHING WENT WRONG";
            }
        }
    }
} 
mysql_close();
?>

When the table is done moving an item, I made it so it alerts the new array that it will POST to the updateworkshops.php page. When I do that, all the order_#'s are equal to 0 and those values are not stored in the database.

I feel like I'm missing something, but I've been trying to fiddle with this code for the past couple hours. Maybe it doesn't help that I'm unfamiliar with Javascript... but I thought I have the right idea down.

Upvotes: 1

Views: 8583

Answers (1)

PetersenDidIt
PetersenDidIt

Reputation: 25610

Try doing something like this:

var sortable = $("#sortable tbody.contenet").sortable({
    helper: fixHelper,
    stop: function(event, ui) {
        //create an array with the new order
        order = $(this).find('input').map(function(index, obj) {
            var input = $(obj);
            input.val(index + 1);
            return input.attr('id') + '=' + (index + 1);
        });
        $.ajax({
            url: "updateworkshops.php",
            type: "post",
            data: order,
            error: function() {
                console.log("theres an error with AJAX");
            },
            success: function() {
                console.log("Saved.");
            }
        });
    }
});​

All you need to do is use map to loop over the inputs use the index of the loop as the order. This code also updated the input value to have the new position value.

Also its time to learn about firebug or webkit developer tools and console.log rather than using alert. Alert is not a debug tool.

Upvotes: 2

Related Questions