jQuery drag & drop table rows sort not working

Question

I wanted to make a script to order some rows from a database with drag & drop. The rows are displayed in a table, and I drag and drop rows to reorder them. Each tr have the row id from database. When I drag and drop a row, jquery serialize the table content and sends it to php to save rows position.

What could be the problem that rows position are not saved in db?

Table with rows:

$select_categories = mysqli_query($db_connect, "SELECT `id`, `title` FROM `categories` ORDER BY `category_order` ASC") or die(mysqli_error());

if(mysqli_num_rows($select_categories) != 0)
{
    echo '<table cellpadding="0" cellspacing="0" class="manage_content" id="sort_rows" align="center">';

    while($category = mysqli_fetch_assoc($select_categories))
    {
        echo '
        <tr id="row-'.$category['id'].'">
            <td width="700"><a href="'.$website_address.'/admin/categories_edit.php?id='.$category['id'].'">'.$category['title'].'</a></td>
            <td><a href="'.$website_address.$_SERVER['PHP_SELF'].'?action=delete_content&amp;id='.$category['id'].'" class="delete_content">Delete</a></td>
        </tr>
        ';
    }

    echo '</table>';
}

jQuery

$("#sort_rows tbody").sortable({

    cursor: 'move',
    delay: 180,

    update: function()
    {
        var rowsOrder = $(this).sortable("serialize");

        $.post("ajax_actions.php", { action:'change_rows_order', table:'categories', order:'category_order', rows_order:rowsOrder } );
    }

}).disableSelection();

AJAX

if(isset($_POST['action']) && $_POST['action'] == 'change_rows_order')
{
    $order_no = 1;

    foreach($_POST['rows_order'] as $row_id)
    {
        $update_order = mysqli_query($db_connect, "UPDATE `".clear_tags($_POST['table'])."` SET `".clear_tags($_POST['order'])."` = '".$order_no."' WHERE `id` = '".$row_id."'") or die(mysqli_error());

        $order_no++;
    }
}

HTML

<table cellpadding="0" cellspacing="0" class="manage_content" id="sort_rows" align="center">
<tr id="row-10">
    <td width="700"><a href="http://local.admin.com/admin/categories_edit.php?id=10">Editorial</a></td>
    <td><a href="http://local.admin.com/admin/index.php?action=delete_content&amp;id=10" class="delete_content">Delete</a></td>
</tr>

<tr id="row-11">
    <td width="700"><a href="http://local.admin.com/admin/categories_edit.php?id=11">Fashion</a></td>
    <td><a href="http://local.admin.com/admin/index.php?action=delete_content&amp;id=11" class="delete_content">Delete</a></td>
</tr>

<tr id="row-12">
    <td width="700"><a href="http://local.admin.com/admin/categories_edit.php?id=12">Street Style</a></td>
    <td><a href="http://local.admin.com/admin/index.php?action=delete_content&amp;id=12" class="delete_content">Delete</a></td>
</tr>

<tr id="row-13">
    <td width="700"><a href="http://local.admin.com/admin/categories_edit.php?id=13">Portraits</a></td>
    <td><a href="http://local.admin.com/admin/index.php?action=delete_content&amp;id=13" class="delete_content">Delete</a></td>
</tr>

<tr id="row-14">
    <td width="700"><a href="http://local.admin.com/admin/categories_edit.php?id=14">Clothing</a></td>
    <td><a href="http://local.admin.com/admin/index.php?action=delete_content&amp;id=14" class="delete_content">Delete</a></td>
</tr>
</table>

far too many unknowns here. What troubleshooting steps have you taken? — charlietfl, Dec 24 '13 at 18:31
@charlietfl I don't know how to pass the serialized data to AJAX, I think that's the problem. Basically when I drag and drop a row, I want to pass an array containing all rows to AJAX and then to save their position in db. — sorinu26, Dec 24 '13 at 20:37
should be already passing the data...inspect request in browser console network tab to see what is sent — charlietfl, Dec 24 '13 at 20:42
@charlietfl I have no errors in JS log. With PHP is a problem. The sanitized variable that I send with AJAX is just a string. I don't know how to convert it to array to be able to use it in PHP foreach. This is an example of the variable that is sent with AJAX when I drag and drop: `row[]=10&row[]=11&row[]=12&row[]=14&row[]=13`. How can I convert this to array? — sorinu26, Dec 27 '13 at 01:17
`$_POST['row']` or `$_POST['row[]']` should be array. Seems you are missing ID's or something on your elements. Show your html and js draggable code — charlietfl, Dec 27 '13 at 01:23
@charlietfl I edited the question with the HTML code. You can also see the JS code there. I know that `$_POST['row']` should be array but how I convert the string that I get with JS sanitize to array? — sorinu26, Dec 27 '13 at 13:31
Your script is vulnerable to SQL injections; read [how to prevent them](http://stackoverflow.com/q/60174/53114). — Gumbo, Dec 27 '13 at 13:33
@Gumbo all variables have `clear_tags` function when I insert or use them in a query. That function I created, contains some functions used to clean the variable and prevent SQL injection. — sorinu26, Dec 27 '13 at 14:14
@sorinu26 `$row_id` does not. Besides that, I don’t know what `clear_tags` actually does and it doesn’t sound like it is appropriate for escaping MySQL identifiers. — Gumbo, Dec 27 '13 at 14:16
@Gumbo You're right, I forgot to clean that variable. This code is in testing, when It works I check it twice and clean everything. In my `clean_tags` function I have `mysqli_real_escape_string` and other functions. — sorinu26, Dec 27 '13 at 14:26
@sorinu26 “In my `clean_tags` function I have `mysqli_real_escape_string` and other functions.” – So it actually is not appropriate. — Gumbo, Dec 27 '13 at 17:09
@sorinu26 To escape strings for being used in a [MySQL string literal](http://dev.mysql.com/doc/refman/5.7/en/string-literals.html), `mysqli_real_escape_string` *without any other function* is the right tool. However, if you want to escape strings for being used in a [MySQL identifier](http://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html), using `mysqli_real_escape_string` is wrong as it’s not a MySQL string literal. — Gumbo, Dec 27 '13 at 17:53
@Gumbo Can you please explain me a little, in like 2 or 3 sentences? Thank you! — sorinu26, Dec 28 '13 at 00:07
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44055/discussion-between-gumbo-and-sorinu26) — Gumbo, Dec 28 '13 at 12:55

score 1 · Accepted Answer · answered Jan 03 '14 at 22:06

1

I solved the problem. I followed the tutorial here: http://www.webresourcesdepot.com/dynamic-dragn-drop-with-jquery-and-php/

answered Jan 03 '14 at 22:06

sorinu26

1,122
2
13
20

jQuery drag & drop table rows sort not working

1 Answers1