PHP: update multiple table rows with one query

Have a PHP script to update all rows in a table. Doesn't seem to me like too much data is being passed. I haven't had any problems with the script up until about 2 weeks ago when I started receiving "connection reset" errors on submission. I toyed with the script a little but I can't find a way to speed it up.

for ($i = 1; $i <= 360; $i++) {
  $info  = mysql_real_escape_string($_POST[$i]);
  $check = mysql_real_escape_string($_POST[''.$i.'Check']);

  mysql_query("UPDATE $table SET info = '$info', status = '$check' WHERE ID = '$i'")
    or die (mysql_error());
}

I really can't tell what's causing the error. It's worked without a problem for so long. I know it's a lot updates but I'm sure larger scripts exist. Also the script walks perfectly some of the time. When it works, the script executes in about 2-3 seconds, which is why I'm not sure 开发者_如何学运维this script is actually causing the errors.

Thanks!

EDIT -- I played around and determined the problem is certainly the number of queries I have running. If I cut the loop to ($i - 1; $i <= 5; $i++) the script executes properly every time with no reset. I upped the the loop back towards 360 and the higher that number hte more queries and it seems the greater the chance of the script timing out. I still can't find a way to fix this, help, please!

If same values have to entered in a series of records,
then BETWEEN startingRecordID AND endingRecordID
can be used for updating multiple records using a single query
(thereby reducing the total number of queries).

I see a few things in your code,

first in the query, you don't need quotes around the $i if its datatype is an integer which I would assume it is if its the primary key.

And in your second post variable you have

$_POST[''.$i.'Check']

Not sure why you have the first set of single quotes in front of $i

And in your query you have

$table

is that a variable that is changing? Are you performing this query on different tables?

I would echo the query to see what it looks like.

$query = "UPDATE $table SET info = '$info', status = '$check' WHERE ID = '$i'";
echo $query;

I always use the mysqli- MySQL Improved Extension and prepared statements when doing the same query that many times. That would look like this.

$conn = new mysqli('localhost', 'my_user', 'my_password', 'my_db'); //creates resource link
//create query
$query = "UPDATE table SET info = '?', status = '?' WHERE ID = ?"
// now create the statement
$stmt = $conn->prepare($query);
// check for an error when creating statement
echo $conn-error;
//now bind variables to the ?
// i for integers, s for strings, d for decimals
$stmt->bind_param('ssi', $info, $check, $i);
// checking for errors every step when testing
echo $stmt->error;

// now run the loop
for ($i = 1; $i <= 360; $i++) {
  $info  = mysql_real_escape_string($_POST[$i]);
  $check = mysql_real_escape_string($_POST[''.$i.'Check']);

  $stmt->execute();
  echo $stmt->error;
}  

This should be faster because the query doesn't have to be processed on every loop.

is 'ID' indexed? If not create an index on ID and try it.

You could run EXPLAIN on a similar SELECT query to see what's happening:

EXPLAIN $table WHERE ID = '1';
EXPLAIN $table WHERE ID = '360';
...

...but these are such simple queries that I don't know what you'd see. I'd look to @jmm's advice and check to see if ID is indexed, and what data type it is. Also, how many rows are in this table? Has that number changed recently?

Finally, if the same queries worked before but now cause trouble, you should also look to external changes. Has the server changed recently? More or less RAM? Changes to MySQL or PHP config? Perhaps something like this could be caused by a MySQL memory setting or PHP max execution setting.