0

For a prepared statement having say 5 named parameters, what if you pass only 3?

$sql = "UPDATE students SET firstname = :firstname, lastname = :lastname, id = :id, major = :major, minor = :minor";

$stmt= $dbh->prepare($sql);

I wanna update only the major and minor fields, what if I do $stmt->execute([":major" => "biology", "minor" => "physics"]);? Will the remaining (firstname, lastname, id) be untouched (original values) and only the provided params be touched?

I have tried it and it throws

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

It seems that I must pass the exact number of params defined in a prepared stmt. What if I have many fields, and I have a catch-all update SQL statement but most of the times I just wanna update some of the params? Say there are 50 potential update parameters, writing one sql each is too repeatable and will be a maintenance nightmare.

To reiterate, you can potential update a - z params but most of the times just a select few. How do you do it?

I want this, but not possible

$sql = "UPDATE alphabets SET a = :a, ..., z = :z";
$stmt = $dbh->prepare($sql);
$stmt->execute([":d" => "foo", ":r" => "boo"]);

Possible but don't want it

$sqlforA = "UPDATE alphabets SET a = :a"; //one for each updatable param
...
$sqlForZ = "UPDATE alphabets SET z = :z";

$updateArr; //associative array containing each updating key/value
foreach ($updateArr as $k => $v) {
  switch($k) {
  case "a":
    $stmt = $dbh->prepare($sqlForA);
    $stmt->execute([":a" => $v]);
    break;
  ...
  case "z":
    $stmt = $dbh->prepare($sqlForZ);
    $stmt->execute(":z" => $v);
    break;
  }
}
limestreetlab
  • 173
  • 1
  • 11
  • 3
    You could build the SQL dynamically, only including the columns in the `SET` part which are to be updated, or use `COALESCE` in mysql to only update values when something is set (sort of the reverse of https://stackoverflow.com/questions/1860216/update-field-when-not-null). – Nigel Ren Sep 06 '21 at 05:40
  • 1
    The database has the capability to only update specific fields of a table, if you craft the query accordingly. It’s not at the level of the query that it accepts “empty fields”. If you mention a field in a query, you need to supply a value for it. If you don’t want to supply a value for it, don’t mention it in the query to begin with. – deceze Sep 06 '21 at 06:00

0 Answers0