MySQL Database with PHP and JQuery Sortable Not Updating.

Forum Moderators: coopster

Message Too Old, No Replies

MySQL Database with PHP and JQuery Sortable Not Updating.

magic8designs

1:56 pm on Jun 8, 2011 (gmt 0)

I am having trouble updating a MySQL DB using JQuery Sortable and PHP. I believe everything else is working fine, my UPDATE query is just not working, and I'm not getting an error message when I use the 'or die' statement. Here is my update syntax:

<?php

//Database Connection Info (working properly)

...

foreach ($_GET['listItem'] as $position => $id) :
$sql[] = "UPDATE `people` SET `position` = $position WHERE `id` = $id";
$sqlUpdate = $sql[$position];
mysql_query($sqlUpdate) or die("Query failed: Update");
endforeach;

$sqlSort = "SELECT * FROM `people` ORDER BY `id` ASC";
mysql_query($sqlSort) or die ("Query failed: Sort2");

?>

Demaestro

2:41 pm on Jun 8, 2011 (gmt 0)

My best guess would be that the id being passed into the query isn't matching any id in the table.

Which would have the effect of not updating the table and not erroring.

Try adding these line to get more info on screen

$sql[] = "UPDATE `people` SET `position` = $position WHERE `id` = $id";
$sqlUpdate = $sql[$position];

echo '<br />********************<br />;
echo $sqlUpdate;
echo '<br />********************<br />;

mysql_query($sqlUpdate) or die("Query failed: Update");
endforeach;

$sqlSort = "SELECT * FROM `people` ORDER BY `id` ASC";
mysql_query($sqlSort) or die ("Query failed: Sort2");

Take a look at the query and see if it is formed correctly, also copy and paste one of the update statements into phpmyadmin or something and try executing it directly and see what it says.

rocknbil

4:21 pm on Jun 8, 2011 (gmt 0)

You also don't need an array since you're doing the update within the loop.

foreach ($_GET['listItem'] as $position => $id) {
$sql = "UPDATE `people` SET `position` = $position WHERE `id` = $id";
// echo $sql; // use to check query
mysql_query($sql) or die("Query failed: $sql");
}

Also make sure it's in GET and not post . . .

magic8designs

3:15 pm on Jun 9, 2011 (gmt 0)

Ok. So the update seems to work for moving one item (listItem) at a time, and when another item (listItem) is moved, the previous update disappears. I would like to make it so that I can update multiple items at the same time. Here is some more of the code that I have:

Upload2.php:

<head>
//Add JQuery libraries
//Make MySQL Database Connection
</head>

<body>

<div id="info">Waiting for Update</div>

<ul id="test-list" class="ui-sortable">

<?php

$query = "SELECT * FROM people ORDER BY position ASC";

$result = mysql_query($query) or die("Query died: Sort1");

$counter = 0;

$itemNames = array();

while($row = mysql_fetch_array($result)){
echo '<li id="listItem_',$counter,'"><img height="16" width="16" class="handle" alt="move" src="arrow.png"><img src="peopleSlides/',$row['listItem'],'" border="0" title="',$row['listItem'],'" style=" width: 100px; height: 90px" /></li>';
$itemNames[$counter] = $row['listItem'];
$counter++;
}

?>
</ul>

<form action="process-sortable.php" method="get" name="sortables">
<input type="hidden" name="test-log" id="test-log" />
</form>

</body>

Process-sortable.php:

<?php

//Make MySQL Database connection

foreach ($_GET['listItem'] as $position => $id) {
$sql = "UPDATE `people` SET `position` = $position WHERE `id` = $id";
echo $sql; // use to check query
mysql_query($sql) or die("Query failed: $sql");

?>