Question 41710 in PHP Question posted in
Official manual can be found here.

Possible to do a MYSQL UPDATE query but not actually update a column? – PHP

Is it possible to do an UPDATE query but not actually update a value and leave it alone?

I would like to have queries like this:

UPDATE homes SET age = 25, SET name = (don’t update) WHERE id = 1.

Of course I could just leave out "SET name =", but I am using this as part of prepared statements in PHP.
The query is first prepared and then queries that match it are sent. But I frequently want to not set a value, but not begin yet another prepared query. Here is an example of my use case

$figures = [ ['age' => 'foo1', 'name' => 'DO NOT UPDATE'],   ['age' => 'foo1', 'name' => 'bar1'],   ['age' => 'foo2', 'name' => 'bar2']];

$stmt = $pdo->prepare("UPDATE homes SET age = ?, name = ?");

foreach ($figures as $row) {
$stmt->execute([$row['age'], $row['name']]);
}

The query and the data values can be changed. I’d like to use a single UPDATE prepared statement instead of doing 100s of different prepared statements with a different prepare() for each.

Tags:

2

Answers


  1. 0

    I believe you do not want to separately process the $figures array so you want to do it in a single loop.

    So just add a second sql prepared statement (you do not mind, right?) and then use a if-then-else to determine which prepared statement to execute

    <?php

$figures = [ ['age' => 'foo1', 'name' => 'DO NOT UPDATE', 'id' => '1'],   ['age' => 'foo1', 'name' => 'bar1', 'id' => '2'],   ['age' => 'foo2', 'name' => 'bar2', 'id' => '3']];

$stmt = $pdo->prepare("UPDATE homes SET age = ?, name = ? where id=?");
$stmt2 = $pdo->prepare("UPDATE homes SET age = ? where id=?");

foreach ($figures as $row) {

 if ($row['name']!='DO NOT UPDATE'){
    $stmt->execute([$row['age'], $row['name'], $row['id']]);
    } else {
     $stmt2->execute([$row['age'], $row['id']]);
  }
}
?>

    Please amend the prepared statements to suit your real needs

    Login or Signup to reply.
  2. 0

    If you have specific values for each column that will never appear in the data, you can check for those. For instance, if name can never be DO NOT UPDATE, have your prepared statement be:

    UPDATE homes SET ..., name = if(?='DO NOT UPDATE',name,?) WHERE id=?

    and pass the value for name (which may be DO NOT UPDATE) to execute twice:

    $stmt2->execute([..., $row['name'], $row['name'], $row['id']]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search