SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)

    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:
    PHP Code:
    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
    PHP Code:
    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
    ictus==""

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Dan,

    I tried building the query like so
    PHP Code:
    //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?
    ictus==""

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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

  5. #5
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    PHP Code:
    $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
    PHP Code:
    $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:
    PHP Code:
        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
    ictus==""

  6. #6
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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?
    ictus==""

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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..

  8. #8
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    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:
    PHP Code:
        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:
    PHP Code:
            $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:
    PHP Code:
    $insertUpdate->update($insertUpdate); 
    Hope this helps someone else
    Steve
    ictus==""


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •