Mysql_query UPDATE

Question

I could swear that I had this working last week, but now I get errors.

In PHP I have a large CSV file that I run through a foreach loop and in this loop I have a created a variable that adds an UPDATE line to itself, like this:

foreach ($csv->data as $value){
   $updater .= "UPDATE tblProduktData SET xtra = 2 WHERE id = '$value[1]';";
}
mysql_query("$updater") or die(mysql_error());

The CSV file contains over 3000 lines so having the mysql_query() inside the loop obviously makes the process slow and is not recommendable.

Can anyone tell me if I'm missing something or just doing it wrong?

you're trying to run multiple queries in a single query() call. this is not permitted for security reasons. — Marc B, Jun 24 '15 at 19:01
Switch to a API that is not **deprecated** which has prepared statements, and then use prepared statements for this. — Jite, Jun 24 '15 at 19:02
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 24 '15 at 19:02

score 1 · Answer 1 · answered Jun 24 '15 at 19:20

We will temporarily ignore the fact that you are using a PHP extension mysql_ that has been deprecated ( Scheduled for removal from the language) for a number of years now.

For some reason you are adding to the sql query each time through the loop by using the .= syntax. I assume you thought you could run more than one query at a time using the mysql_ extension, but you cannot.

So try this :-

foreach ($csv->data as $value){
   $updater = "UPDATE tblProduktData SET xtra = 2 WHERE id = '$value[1]'";
   mysql_query($updater) or die(mysql_error());
}

This is in fact a perfect candidate for using mysqli_ or PDO prepared statements.

The mysqli_ extension manual

The PDO manual

Thanks @RiggsFolly - I think the easiest for me to understand would be mysqli, so I will try to "convert" to this. What you are saying is that no matter the PHP extension I will not be able to perform only one query that updates the 3000+ rows? I will have to, just like the above example from you, to perform a mysqli_query for every row in the CSV file? — MazeyMazey, Jun 24 '15 at 19:33

score 0 · Answer 2 · answered Jun 24 '15 at 19:38

Try this:

$id = "0"; // initialze the ids to update with a non-existing value
// fetch all the ids into a variable
foreach ($csv->data as $value){
  $id .= "," . $value[1] 
}

$updater .= "UPDATE tblProduktData SET xtra = 2 WHERE id in (".$id.") ;";
mysql_query("$updater") or die(mysql_error());

Mysql_query UPDATE

2 Answers2