CODEWITHSUNDEEP
phpmysqlsql
Sven van den Boogaart
Sven van den Boogaart

Reputation: 12317

Update multiple rows in once. based on id

I have a variable, $ids It is a , separated string so it can be $ids = "1" or $ids = "1, 3, 7, 8"

What i want to do is update the database based on these values so i have :

   $query = "UPDATE Fields SET Value = '1'   WHERE Id IN '$ids'";

And also:

   $query = "UPDATE Fields SET Value = '1'   WHERE Id '$ids'";

What is the best way to update the database, should i split the string in to an array, and then do a for each loop? or is there a better way?

Upvotes: 2

Views: 990

Answers (3)

Pieter
Pieter

Reputation: 1833

Use this query:

$query = "UPDATE Fields SET Value = '1' WHERE Id IN ($ids)";

Where $ids should be formatted als 1,2,3,4. Don't forget to check them before execution (SQL Injection).

Upvotes: 0

David
David

Reputation: 218828

There's nothing inherently wrong with using an IN clause here. Any WHERE clause works for an UPDATE statement. You'll just want to treat the numbers as a list of values instead of a string. Something like this:

$query = "UPDATE Fields SET Value = '1' WHERE Id IN ($ids)";

The really important part is how you get the $ids value into the query in the first place. You don't show that in the question, but you'll want to make sure you're not opening yourself to a SQL injection vulnerability. Make sure you're properly sanitizing inputs and using prepared statements. How prepared statements handle lists of values vs. individual values is up to the data access technology being used.

Upvotes: 2

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Save the fact that it's wide open to SQL Injection, this line works for one or many id's:

$query = "UPDATE Fields SET Value = '1'   WHERE Id IN ($ids)";

Now, to keep yourself from SQL Injection attacks, which is obviously up to you, you'd want to explode that array and send multiple update statements like this:

$query = "UPDATE Fields SET Value = '1'   WHERE Id = :Id";

Upvotes: 3

Related Questions