Sortable PHP MYSQL

Question

Im trying to use this https://gist.github.com/linssen/2773872 on my php work but that code only shows a array text and doesnt update in my database.

Main page:

<ul id="test-list">
<?php do { ?>
<li id="listItem_<?php echo $row_dados['id']; ?>"><img src="images/arrow.png" alt="move" width="16" height="16" class="handle" /><strong><?php echo $row_dados['posicao']; ?></strong></li>
<?php } while ($row_dados = mysql_fetch_assoc($dados)); ?>
</ul>

This is creating a loop to get all the records from database and show it.

Script:

<script type="text/javascript">
// When the document is ready set up our sortable with it's inherant function(s)
$(document).ready(function() {
$("#test-list").sortable({
handle : '.handle',
update : function () {
var order = $('#test-list').sortable('serialize');
$("#info").load("process-sortable.php?"+order);
}
});
});
</script>

process-sortable.php:

<?php
/* This is where you would inject your sql into the database 
   but we're just going to format it and send it back
*/

foreach ($_GET['listItem'] as $position => $item) :
    $sql[] = "UPDATE `dados` SET `posicao` = $position WHERE `id` = $item";
endforeach;

print_r ($sql);
?>

But this only prints the result i would like to change this code to actually update my database.

The result:

Array
(
    [0] => UPDATE `dados` SET `posicao` = 0 WHERE `id` = 4
    [1] => UPDATE `dados` SET `posicao` = 1 WHERE `id` = 3
    [2] => UPDATE `dados` SET `posicao` = 2 WHERE `id` = 1
    [3] => UPDATE `dados` SET `posicao` = 3 WHERE `id` = 2
    [4] => UPDATE `dados` SET `posicao` = 4 WHERE `id` = 5
    [5] => UPDATE `dados` SET `posicao` = 5 WHERE `id` = 6
    [6] => UPDATE `dados` SET `posicao` = 6 WHERE `id` = 7
    [7] => UPDATE `dados` SET `posicao` = 7 WHERE `id` = 8
    [8] => UPDATE `dados` SET `posicao` = 8 WHERE `id` = 9
    [9] => UPDATE `dados` SET `posicao` = 9 WHERE `id` = 10
    [10] => UPDATE `dados` SET `posicao` = 10 WHERE `id` = 277
    [11] => UPDATE `dados` SET `posicao` = 11 WHERE `id` = 278
)

So its working fine, but doesnt change the database based on this lines.

So the probem is probably on process-sortable.php, so any help would be awesome.

Cumps.

Answer 1

Are that gist code are from yourself or you references? Btw, have you check database connection and where $item coming from?

You can set $item from $_POST / $_GET, sql select or define just before sql update are execute. Check my snippet below, I put comment so you can read it easier :

<?php
// Create connection to database
$con = mysqli_connect("localhost","your_db_username","your_db_password","your_db_name");

// Validate database connection 
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// You need to get $item from somewhere else (e.g : You set as variable $item=1)
$item = 1;

// You update statement here
foreach ($_GET['listItem'] as $position){
    $sql = "UPDATE dados SET posicao = {$position} WHERE id = {$item}";
    if (mysqli_query($conn, $sql)) {
      echo "Record updated successfully";
    } else {
      echo "Error updating record: " . mysqli_error($conn);
    }
}

mysqli_close($conn);
?>

Answer 2

Create connection to your database, then prepare query and execute.

<?php
/* Connect to an ODBC database using driver invocation */
$dsn      = 'mysql:dbname=testdb;host=127.0.0.1';
$user     = 'dbuser';
$password = 'dbpass';

try {
    $dbh  = new PDO($dsn, $user, $password);
    $stmt = $dbh->prepare('UPDATE `table` SET `position` = :position WHERE `id` = :id') ;

    foreach ($_GET['listItem'] as $position => $item) {
        $stmt->execute(array(':position' => $position, ':id' => $item));
    }
} catch (PDOException $e) {
    echo 'Something went wrong: ' . $e->getMessage();
}

Answer 3

You can use your process-sortable.php file like

        <?php
    /* This is where you would inject your sql into the database 
       but we're just going to format it and send it back
    */

    foreach ($_GET['listItem'] as $position => $item) :
        $sql[] = "UPDATE `dados` SET `posicao` = $position WHERE `id` = $item";
    endforeach;

    $sql_str = implode(',',$sql);
    //execute $sql_str in mysql query string you will get output

    print_r ($sql_str);
    ?>