PDO Update Bound Parameter not working

Hi I have a simple method for updates and am using the PDO driver. If I structure the SQL without bound parameter then the update works however when bound parameters don’t work.

Here is the method:

function update() {
        //Update
        $sql = 'UPDATE ' . $this->table . ' SET ?  Where ' . $this->column_name .' = ?';
        //echo $sql . '<br />';
         $stmt = $this->pdo->prepare($sql);
        try {
                if ($stmt->execute(array($this->update_expression, $this->value))){
                    return true;
                }
            } catch (Exception $e){
                return $e->getMessage(); //return exception
            }
    }

Here is the output of $stmt after the prepare statement

object(PDOStatement)#16 (1) {   ["queryString"]=>   string(40) "UPDATE users SET ?  Where uid_number = ?" } 

Can you give me some suggestions on how to troubleshoot this? I have already verified that it is a valid PDO object, and that the properties are set with the correct values. No errors are reported.

Your help is appreciated.
Steve

You bind values, not arbitrary pieces of SQL

UPDATE tablename SET columname = ? WHERE colname = ?

I don’t think you can do what you’re trying to do, you need to construct the query first

Hi Dan,

I tried building the query like so

//Update
        $sql = 'UPDATE ' . $this->table . ' SET :update_expression  Where ' . $this->column_name .' = :value';

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindParam(':update_expression', $this->update_expression);
        $stmt->bindParam(':value', $this->value);
        try {
                if ($stmt->execute()){
                    return true;
                }
            } catch (Exception $e){
                return $e->getMessage(); //return exception
            }
    }

I get the same result. Do you mean build the query in a different way than I have shown?

$sql = 'UPDATE ' . $this->table . ' SET ' . $this->update_expression . ' WHERE ' . $this->column_name .' = ?';

The string you pass to prepare() should be a complete SQL query missing only values, not entire expressions

Hi Dan,

As per your suggestion, I did this and it did not work

$sql = 'UPDATE ' . $this->table . ' SET ' . $this->update_expression . ' WHERE ' . $this->column_name .' = ?';
$string_sql = (string)$sql;
execute($string_sql)...

On one of my older pieces of code I do this and it works:

    if ($this->end_id == NULL){
            $this->stmt = $this->dbc->prepare("SELECT * FROM " . $this->table_name . ' WHERE id >= :start_id');
            $this->stmt->bindParam(':start_id', $this->start_id);
        } else { // get range of ids
            $this->stmt = $this->dbc->prepare("SELECT * FROM " . $this->table_name . ' WHERE id >= :start_id AND id <= :end_id');
            $this->stmt->bindParam(':start_id', $this->start_id);
            $this->stmt->bindParam(':end_id', $this->end_id);

Here the values are the only things being bound and the column is explicitly written out. I guess given the fact that the string_sql typecast as a string is not enough?

Thanks
Steve

Shouldn’t I be able to make other parts of your query variable (e.g. column name, table name, SQL keywords, or whole SQL expressions), using a built dynamic SQL query as a string, and interpolate PHP variables or expressions into the string?

Yes, you can do that. The problem is in your code. You are trying to directly execute a query with a question mark in it without using prepared statements or binding anything to that value…

Ok I solved this. Now my CRUD update method works with bound paramaters. Here the method - notice the trim($sql) that was the difference maker:

    function update() {
    //Update
        $sql = 'UPDATE ' . $this->table . ' SET ' . $this->field_value . ' = :update_value  WHERE ' . $this->column_name .' = :value';
         $stmt = $this->pdo->prepare(trim($sql));
         $stmt->bindParam(':update_value', $this->update_value);
        $stmt->bindParam(':value', $this->value);

        $this->pdo->beginTransaction();
        try {
                if ($stmt->execute()) {
                    $this->pdo->commit();
                    return true;
                } else {
                    $this->pdo->rollback();
                }
            } catch (Exception $e){
                return $e->getMessage(); //return exception
            }
    }

Here’s how the properties are set:


        $insertUpdate->setTable("users");
        $insertUpdate->setFieldValue("display_name");
        $insertUpdate->setUpdateValue("Be Test");
        $insertUpdate->setColName('uid_number');
        $insertUpdate->setValue("10004");

I am using a container so my update call may be different than the way you might like to set it up, but for the record here how I call update once the properties are set:

$insertUpdate->update($insertUpdate);

Hope this helps someone else :slight_smile:
Steve