Php Query In loop not updating database

[madjack87]

I am using jquery and ajax to have a drag and drop sortable table.

 

My problem is that it only works about 10 percent of the time.

 

Here is my js code.

<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("tow_sort2.php?"+order);
		}
	});
});
</script>

Here is the tow_sort2.php page:

<?php 
include("../_includes/db_connection.php");
 
/**
 * This is where you would inject your sql into the database 
 * but we're just going to format it and send it back 
 */ 
$array =array();
foreach ($_GET['listItem'] as $position => $item)
{
  $sql = "UPDATE tow SET tow_order = '$position' WHERE tow_id = '$item'"; 
  $conn->query($sql);
  $array[] = $sql;
}
print_r($array);
?> 

Here is the array that is being printed:

Array ( [0] => UPDATE tow SET tow_order = '0' WHERE tow_id = '5924' [1] => UPDATE tow SET tow_order = '1' WHERE tow_id = '5925' [2] => UPDATE tow SET tow_order = '2' WHERE tow_id = '5922' [3] => UPDATE tow SET tow_order = '3' WHERE tow_id = '5923' )

[mac_gyver]

you need to tell use about the times it doesn't work and tell us exactly what part of it doesn't work and how you know it doesn't work.

 

is the submitted data correct or not when it doesn't work? are there query errors when it doesn't work? do you have error checking for the query?

 

you should also be using a POST request to send the data to the server (so that just making a get request for the page cannot mess up your data) and you should be using a prepared query to supply the external data to your sql query statement.

[madjack87]

you need to tell use about the times it doesn't work and tell us exactly what part of it doesn't work and how you know it doesn't work.

 

is the submitted data correct or not when it doesn't work? are there query errors when it doesn't work? do you have error checking for the query?

 

you should also be using a POST request to send the data to the server (so that just making a get request for the page cannot mess up your data) and you should be using a prepared query to supply the external data to your sql query statement.

 

Its very random when it does and doesnt work. Even when doing the exact same steps. The array that is being printed is always correct. I see no problem in the SQL queries that are stored in the array.  When I tried to add error checking before I received no errors..

 

What do you mean by I should be using a prepared query?

[mac_gyver]

either the query is failing or the data being submitted doesn't match any row(s) or is being updated to the same value it started with and the UPDATE query doesn't affect any row(s).

 

When I tried to add error checking before I received no errors

 

 

it would take seeing how you were checking/displaying/logging errors, since the method being used may not have been effective, since you are using ajax to submit the data.

 

you should ALWAYS have error handling in your code and for development, display any errors, and when on a live server, log any errors.

 

your code should also be testing if the UPDATE query actually changed each row. the php database extension you are using will have an 'affected rows/row count' method you can use to do this.

 

you haven't stated what exactly isn't working. are none of the row(s) being updated? are only some of the rows being updated and if so, which ones? are the rows being updated with the wrong values?

 

you also haven't stated how you know it isn't working. what method/tool are you using to look at/view the result, since, there may be something wrong with the method (showing cached values as an example.)

 

another possibility is that the data contains white-space characters, which print_r() won't directly show. you should use var_dump() instead, since it will give the length of the data.

 

i suspect, that the problem will be ultimately due to using a get request and that the browser/client-side code is making two requests to your page.

 

What do you mean by I should be using a prepared query?

 

 

there is a ton of information to be found, on any subject, just by searching the web for it.

Edited March 26, 2016 by mac_gyver