PDOStatement->prepare and PDOStatement->bindParam() combination not working

Question

I have some code that should loop through values and change entries in a table. The 5 values of the variables $change_val, $column, and $id all echo out correctly, so I assume there is something wrong with my usage of bindParam (but I am not sure what it is).

$connection = new PDO("mysql:host=localhost;dbname=logbook", $username, $password);
$perform_edit = $connection->prepare("UPDATE contacts SET :column = :value WHERE name_id = :name_id");

[Definition of Arrays]

for ($i = 1; $i <= 5; $i++) {

    if (!empty($_POST[ $change_array[$i]])) {
        $change_val = $_POST[$change_array[$i]];
        $column = $column_array[$i];
        $id = $_POST["name_id_ref"];
        $perform_edit->bindParam(":column", $column, PDO::PARAM_STR);
        $perform_edit->bindParam(":value", $_POST[$change_array[$i]], PDO::PARAM_STR);
        $perform_edit->bindParam(":name_id", $_POST["name_id_ref"], PDO::PARAM_INT);
        $perform_edit->execute();
        }
}

The $_POST statement is there because the value I want is actually passed from another file. When I place appropriate echo statements within the loop, though, they all print out their correct value.

I've also tried bindValue, but that did not work either. I see no errors and things at least compile smoothly—just not as they should. Nothing in the table is changed.

What's wrong here?

`column` for one thing, is a reserved word http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html - ***Yet***, you can't do `SET :column` so you'll need to either set a variable for it instead, or choose the actual column name. — Funk Forty Niner, Jul 20 '14 at 17:32
Add `$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after the connection is opened. You stand at getting errors pop up. — Funk Forty Niner, Jul 20 '14 at 17:34
@Fred-ii- Ah! Excellent. I will start putting that after all of my connects. The problem is that there are five columns that will be edited, and the columns change through out the loop. I could put the prepare statement *within* the loop, but wouldn't that be inefficient? — AmagicalFishy, Jul 20 '14 at 17:40
TBH, I suck big time with loops when it comes to having to do stuff like you want to do. You could try it and see what the performance is; it's worth a shot. — Funk Forty Niner, Jul 20 '14 at 17:42
See these Q&A's http://stackoverflow.com/q/20531613/ and http://stackoverflow.com/q/12043967/ and http://stackoverflow.com/q/16429736/ - Found those Googling "update multiple columns using pdo", if you wish to further your research. Plus another http://www.coderexception.com/C1Nm1BHHPWxXUSUX/updating-multiple-columns-and-rows-using-pdo - Hope it helps. — Funk Forty Niner, Jul 20 '14 at 17:47

codemonkee · Accepted Answer · 2014-07-21T03:34:14.200

You cannot use place holders for table or column names it would defeat the purpose of preparing a statement ahead of time if the structure of that statement changed.

You would need to pre-build your prepare statement with the correct column names, whether you name them by hand, string replacement, or implode a list of column names.

I don't have an environment to test on right now but something like:

//Some random values and DB column names
$arrLocation = array ('Victoria','Washington','Toronto','Halifax','Vancouver');
$arrName     = array ('Sue', 'Bob', 'Marley', 'Tim', 'Fae');
$arrColumn   = array (1 => 'name', 2 => 'age', 3 => 'location');


/* Build column & named placeholders
 * $strSet = '`name` = :name, `age` = :age, `location` = :location';
 */

$strSet = '';
foreach ($arrColumn as $column) {
    $strSet .= "`$column` = :$column, ";
}
$strSet = rtrim($strSet, ', ');

$connection = new PDO($dsn, $user, $pass);

/*
 * Prepared statement then evaluates to:
 * UPDATE `table` SET `name` = :name, `age` = :age, `location` = :location
 *   WHERE `id` = :id;
 */
$stmt = $connection->prepare("UPDATE `table` SET $strSet WHERE `id` = :id;");

$arrChange = array (
  1 => $arrName[(rand(0, count($arrName)-1))],
  2 => rand(0, 30),
  3 => $arrLocation[(rand(0, count($arrLocation)-1))]
);

$idToUpdate = 1;
$stmt->bindParam(':id', $idToUpdate, PDO::PARAM_INT);
foreach($arrChange as $key=>$value) {
    $stmt->bindValue(":$arrColumn[$key]", $value);
}
$stmt->execute();

Maybe I am being dense, but I'm not sure exactly what you've done here. You made the column name a variable instead of a place holder, but you scroll through all the values of $strSet before you prepare the statement? (I did get the code working by placing the prepare statement in a loop, which unfortunately is not as efficient, but that's ok; I'm just curious as to exactly what you've done) — AmagicalFishy, Jul 21 '14 at 00:46
Added a couple code comments -- As a place holder cannot be used for tables or columns in a prepared statement, built the SET string to be evaluated in the prepared string which then have 4 named placeholders in the used example. The values to set is then looped over to bind the values for execution. — codemonkee, Jul 21 '14 at 03:43
bindParam is a reference and evaluates the values of the variable at time of execution; bindValue binds the value at the time of binding. — codemonkee, Jul 21 '14 at 03:46

PDOStatement->prepare and PDOStatement->bindParam() combination not working

1 Answers1