pdo update multiple rows in one query

Asked Mar 25 '16 at 10:36

Active Apr 05 '16 at 11:09

Viewed 816 times

So i have a insert query with multiple rows (1 000 row per query) and it work fine, so tried to make the same for update query but i didn't find any solution,

this is my code :

$sth = $db->prepare("UPDATE table SET salutation = ? , fnamn = ? , enamn = ? , mob = ? WHERE id = ?");

for ($i=0; $i < count($recipents); $i++) {
 if(isset($recipents[$i]['id'])) 
   $sth->execute(array($recipents[$i]['salutation'], $recipents[$i]['fnamn'], $recipents[$i]['enamn'], $recipents[$i]['mob'],$recipents[$i]['id']));
}

so please if someone has any idea how to update multiple rows in one query i will be very appreciative.

asked Mar 25 '16 at 10:36

Mohammadov

In this exemple they use ON DUPLICATE KEY or i do not update with the key, i can't do that – Mohammadov Mar 25 '16 at 11:01
You have to use [prepared statements](https://phpdelusions.net/pdo#multiexec) for this. To wrap all the updates in a transaction also would be a good idea. – Your Common Sense Mar 25 '16 at 11:02
"or i do not update with the key" - `id` is not a unique key? Strange.... – VolkerK Mar 25 '16 at 11:03
The code you have shoud work. If you wrap it in a transaction and commit every 1000 rows it will probably be quick enough. – Ryan Vincent Mar 25 '16 at 11:10
ok thnx but how can i do that ? – Mohammadov Mar 25 '16 at 11:17
I have a $transaction count that starts a transact when ... hold on I will create some code... – Ryan Vincent Mar 25 '16 at 11:23
1

Sample, untested code here: http://pastebin.com/4E09U7cZ. IF you benchmark it you will see it is quite quick compared with using autocommit. – Ryan Vincent Mar 25 '16 at 11:31
Thnx bro i will try your code – Mohammadov Mar 25 '16 at 11:46

pdo update multiple rows in one query

0 Answers0