Updating a table row with a variable number of fields

[hokietoner]

Suppose I have a table t with fields id, a, b, and c, and I have a function update_t($id, $a=null, $b=null, $c=null). I have to write several lines of code to build my query based on which parameters were passed in. For example, I would do something like this:

 

$sql = "UPDATE t SET ";

 

if($a !== null)

  $sql .= "a = $a, ";

if($b !== null)

  $sql .= "b = $b, ";

if($c !== null)

  $sql .= "c = $c, ";

 

$sql = trim($str, ", ") . " WHERE id=?";

 

SQL injection vulnerabilities in this code aside, this is really tedious. Is there a way, particularly in PDO (which I just started using) to specify an associative array keyed by field name so I can do something like this:

$dbh->updateTable($tableName, $arr)

 

Of course, I could write my own function but I'd like to use PDO to do this if I can.

[Daniel0]

Try to take a look at the code I posted here. The code isn't tested as it was purely as an example that you didn't have to manually filter every single thing. Also, the code is for an INSERT, but it's sort of the same logic you need to apply and you need to add an additional $where argument.

[hokietoner]

Thanks, I forgot about the join function. I guess I'll just write my own function similar to what you've written.

[dbo]

I'm not sure how you are allowing the update to take place, but if it's a form with all fields for the particular record, is there anything wrong with creating an update query that updates every field? If a particular field was unchanged, the update would just overwrite with the same data, thus not changing it.

 

It may not work for you, particularly if you're trying to maintain some sort of an audit log, but depending on the situation could make life a lot easier.

[hokietoner]

That's true, but I'm writing a generic update function so I don't want to force the caller to know all the existing values for fields that don't need to be updated.

 

I just noticed that I don't think you can put variable bindings in certain places in your query with PDO. For example, I tried something like this and it failed:

 

$handle->prepare("INSERT INTO :table (:acol, :bcol, :ccol) VALUES (:aval, :bval, :cval)")

 

I guess that means if I want to write a generic update/insert function, I have to make sure either I trust that the caller is not sending table and column names from user content, or I have to escape them myself.