PHP: update multiple table rows with one query

Question

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!

TheCodeArtist · Accepted Answer · 2012-11-11 05:29:22Z

1

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).

edited Nov 11, 2012 at 5:29

TheCodeArtist

22.7k4 gold badges74 silver badges135 bronze badges

answered Sep 24, 2011 at 10:40

Faryad Ali

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

jmm · Accepted Answer · 2011-01-26 04:35:35Z

0

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.

answered Jan 26, 2011 at 4:35

jmm

9901 gold badge7 silver badges12 bronze badges

2 Comments

Thomas Over a year ago

i really appreciate the help, I have never used mysli before (although I have seen it used before and never quite understood the syntax) however even with this method I am still experiencing erratic processing speeds. It seems to be working exactly as effectively as my previous script. I'm going to assume the issue has something more to do with the hosting service than the script itself (even as the script is obviously not perfect)

jmm Over a year ago

Well what does your script look like before and after. I just get the feeling the problem lies somewhere else. What does your code look like that you use to connect to the database? Is there any other queries before or after? Put some echo statements in the loop to see how many times it runs with out trouble.

jmm · Accepted Answer · 2011-01-25 22:18:05Z

0

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

answered Jan 25, 2011 at 22:18

jmm

9901 gold badge7 silver badges12 bronze badges

Comments

Steven · Accepted Answer · 2011-01-25 22:29:09Z

0

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.

answered Jan 25, 2011 at 22:29

Steven

9282 gold badges9 silver badges21 bronze badges

Collectives™ on Stack Overflow

PHP: update multiple table rows with one query

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related