Update multiple rows with single query using MySQL and PHP

Question

<?php
$userData = array();
while (anything to create a loop) {
    $value1 = $result1_from_loop;
    $value2 = $result2_from_loop;
    $value3 = $result3_from_loop;
    $userData[] = '("'.$value1.'", "'.$value2.'", "'.$value3.'")';
} // THIS ENDS THE WHLE OR FOR LOOP

$query = 'INSERT INTO users (data1,data2,data3) VALUES' . implode(',', $userData);
mysql_query($query);
?>

The above code works perfectly for inserting multiple records into table users as seen above and it's very fast as well.

However, I am trying to use the same method to update after going through a loop as before. I have no idea how to achieve this.

I want something like this:

<?php
$userData = array();
while (Loop statement) {
    $value1 = $result1_from_loop;
    $value2 = $result2_from_loop;
    $value3 = $result3_from_loop;
    $userData[] = '("'.$value1.'", "'.$value2.'", "'.$value3.'")';
} // This ends the WHLE or FOR loop

$query = 'UPDATE users SET(data1,data2,data3) VALUES' . implode(',',$userData) WHERE data2=$value2
mysql_query($query);

I know the above code is not close to correct, syntax is even wrong. I just pasted it to show the idea of what I want achieved. In the WHERE statement how will data2 get to know the value of each $value2?

Yes i know, it wasn't meant to be correct. I just put it there to have an idea of what i want done. I can't even correct the syntax cos i only know how to write INSERT with this method. — Tom R, Nov 10 '17 at 16:00
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Nov 10 '17 at 16:01
@Fred if you can ignore the syntax i give me a clue how i can successfully create a WHERE statement using one of the values in $userData[] i would be grateful. — Tom R, Nov 10 '17 at 16:02

score 0 · Answer 1 · answered Nov 10 '17 at 15:59

0

UPDATE uses a different format to INSERT. For UPDATE, your code should look something like this:

$query = 'UPDATE users SET data1 = $userData[0], data2 = $userData[1], data3 = $userData[2] WHERE data2=$value2';

Although just as a note, using mysql_query is not advised as it is deprecated (and will be removed altogether in later PHP versions) and your code is vulnerable to SQL injection. At a minimum I'd recommend using mysqli_query instead and looking into using prepared statements.

answered Nov 10 '17 at 15:59

Roy

705
2
11
32

Thanks, however this doesn't fix my use case. All i am trying to do is UPDATE multiple rows using a single connection to MYSQL i tried $update.="update statement" and outside the loop i mysql_query($update) it didn't work. I am not good with mysqli and just wondering if there is a way to do this with MYSQL without inserting/updating each record per loop. – Tom R Nov 10 '17 at 16:08

Update multiple rows with single query using MySQL and PHP

1 Answers1