从给定的id数组列表及其值更新特定id的特定值

Question

I have a doubt in mysql_query. I want to update the particular value(id's value) for particular id from list of arrays of id and its value.

here is the normal query

update table_name set name='sham', age='18' where id=1
Update table_name set name='ram', age='19' where id=2
Update table_name set name='rani', age='29' where id=3

Instead of above query I want to update these fields in 1 query by passing these values through an array

like update set name=?, age=? where id=?

Can we do it?

What query should be written and how to pass in array to update query

Answer 1

Can we do it?

Yes, it can be done.

Should you do it?

No. It's a bad idea. It will be slow and inefficient.

If you want to use arrays of values to execute a query several times with different values, here is one way to do it with PDO and prepared statements with named placeholders:

(We assume you already have a working PDO connection in your script: $dbh)

$values = [
    [
        ':name' => 'sham',
        ':age' => '18',
        ':id' => 1
    ],
    [
        ':name' => 'ram',
        ':age' => '19',
        ':id' => 2
    ],
    [
        ':name' => 'rani',
        ':age' => '29',
        ':id' => 3
    ]
];
$query = "UPDATE some_table SET name = :name, age = :age where id = :id";
$sth = $dbh->prepare($query);
foreach ($values as $set) {
    $sth->execute($set);
}

This code will prepare the query once, and execute it for each set of values in $values.

Here is the same code with unnamed placeholders (question marks):

$values = [
    [
        'sham',
        '18',
        1
    ],
    [
        'ram',
        '19',
        2
    ],
    [
        'rani',
        '29',
        3
    ]
];
$query = "UPDATE some_table SET name = ?, age = ? where id = ?";
$sth = $dbh->prepare($query);
foreach ($values as $set) {
    $sth->execute($set);
}