PHP can only loop through 280 mysql UPDATES - HELP

[byroncoolie]

Thanks in advance all.

 

I have an AJAX list sorting script that loops through an array and changes the 'position' field in a db table. This works great until the number of records in the array exceeds 280.

I'm trying to work out how to overcome this.

 

A time delay usleep() does not help.

Perhaps I need to count the records and split the operation of the function into blocks of 280.

Perhaps I need to open a database connection then close it and open it again after 280 records.

 

Here is the script:

foreach ($_GET['listItem'] as $position => $item){

$sql = "UPDATE $dbProd SET `position` = '$position' WHERE `id` = '$item'";

mysql_query($sql) or die(mysql_error());

$query .= $sql;

}

print_r ($query);

[trq]

You should be able to get this done in one query especially considering your using Ajax (because you can execute the query as soon as a single item is moved, not just after all the moves have happened and the form has been submitted).

 

You need to rethink your logic. Tell us more about what it is you are doing.

[byroncoolie]

You need to rethink your logic. Tell us more about what it is you are doing.

 

It's part of a CMS. The page that displays all products has the facility to re-order them by dragging and dropping, hence the basic ajax functionality. By dragging and dropping this creates a querystring of all the new order ids and product ids which is then processed by a script to do the database work.

 

The querystring looks like this: ?listItem[]=1&listItem[]=3&listItem[]=2 etc etc. When this exceeds 280 items it fails in the script that splits the array and does the database update. The database cannot handle more than 280 requests in the loop.

 

Here is the actual script (we have used this in our build):

http://www.wil-linssen.com/musings/entry/extending-the-jquery-sortable-with-ajax-mysql/

 

Hope this helps.

 

[PFMaBiSmAd]

Have you investigated where the 280 limit is occurring at?

 

It is probably in the maximum length of the URL that the browser will send and the web server will accept.

 

You should be using the POST method for a large amount of data.

[byroncoolie]

Thank you, I did consider the length of the URL but the (obscure) error message swayed me from this. I investigated it further by changing the GET to a POST and have resolved the issue.

 

Thanks. Here's the final code:

 

THE SORT PAGE:

<script type="text/javascript">

  $(document).ready(function() {

    $("#list").sortable({ // list is the id of the UL

      handle : '.handle',

      update : function () {

var order = $('#list').sortable('serialize'); // produces string of listItem[]=1&listItem[]=3&listItem[]=2 where listItem is the id of the LI's in the UL

$.post('product_sortable-process.php', order);

      }

    });

});

</script>

 

THE ACTION PAGE:

foreach ($_POST['listItem'] as $position => $item){

$sql = "UPDATE $dbProd SET `position` = '$position' WHERE `id` = '$item'";

mysql_query($sql) or die(mysql_error());

}

 

All thanks to this post by Bart:

http://stackoverflow.com/questions/654535/jquery-what-to-do-with-the-list-that-sortableserialize-returns