PDO: Making an execute statement dynamic...but not working

Question

this should be easy...but I can't figure it out. If the following execute statement works at the end of an insert query.....

     $query->execute(array($this->visible_password, 
                           $this->hashed_password, 
                           $this->temp_hashed_password, 
                           $this->first_name, 
                           $this->last_name, 
                           $this->position, 
                           $this->location, 
                           $this->city, 
                           $this->email, 
                           $this->country, 
                           $this->institution, 
                           $this->interests, 
                           $this->profile_comment));

and

       $result =  join(", ", array_values($place_holder));
       echo $result;

gives

$this->visible_password, $this->hashed_password, $this->temp_hashed_password, $this->email, $this->first_name, $this->last_name, $this->position, $this->location, $this->city, $this->country, $this->institution, $this->interests, $this->profile_comment

then why doesn't this work.....

     $query->execute(array($result))

var_dump on placeholder gives...

   array(13) { 

 [0]=> string(26) "$this->visible_password" 
 [1]=> string(25) "$this->hashed_password" 
 [2]=> string(30) "$this->temp_hashed_password" 
 [3]=> string(15) "$this->email" 
 [4]=> string(20) "$this->first_name" 
 [5]=> string(19) "$this->last_name" 
 [6]=> string(18) "$this->position" 
 [7]=> string(18) "$this->location" 
 [8]=> string(14) "$this->city" 
 [9]=> string(17) "$this->country" 
 [10]=> string(21) "$this->institution" 
 [11]=> string(19) "$this->interests" 
 [12]=> string(25) "$this->profile_comment" }

$placeholder is an array...that takes the attributes ($attributes) of a class

            $place_holder = array();
            foreach ($attributes as $key => $value) {
            $place_holder[] = "&#36this->" . $key;
        }

I have been trying to make create user method in my user class abstract so I could use it in all my classes. I've been converting my site to PDO from mysqli (nightmare). Here is the method....

public function  pdo_create_test() {

  $attributes = $this->attributes();
  $att = array_keys($attributes);     
   $question_marks = array();
    foreach ($attributes as $key => $value) {
            $question_marks[] = "?";
    }
   $place_holder = array();
    foreach ($attributes as $key => $value) {
           $place_holder[] = "&#36this->" . $key;
        }
  $result = join(", ", array_values($place_holder));
  $sql = "INSERT INTO ".self::$table_name." (";
  $sql .= join(", ", array_keys($attributes));
  $sql .= ") VALUES (";
  $sql .= join(", ", array_values($question_marks));
  $sql .= ")";
  $query = $handler->prepare($sql);
  $query->execute(array($this->visible_password, 
                      $this->hashed_password, 
                      $this->temp_hashed_password, 
                      $this->first_name, 
                      $this->last_name, 
                      $this->position, 
                      $this->location, 
                      $this->city, 
                      $this->email, 
                      $this->country, 
                      $this->institution, 
                      $this->interests, 
                      $this->profile_comment));

                   }

Here is an update....(I have altered the placeholder to get the value as Bill said below) I am also echoing the sql and the result of the placeholder to see what is says.

public function  pdo_create_test() {
  global $handler;
  $attributes = $this->attributes();
  $att = array_keys($attributes);
  $question_marks = array();
    foreach ($attributes as $key => $value) {
    $question_marks[] = "?";
    }
  $place_holder = array();
    foreach ($attributes as $key => $value) {
    $place_holder[] = $this->$key;
        }
  $result = join(", ", array_values($place_holder));
  $sql = "INSERT INTO ".self::$table_name." (";
  $sql .= join(", ", array_keys($attributes));
  $sql .= ") VALUES (";
  $sql .= join(", ", array_values($question_marks));
  $sql .= ")";
    echo $sql;
    echo "<br/>";
    echo "<br/>";
    echo $result;
    $query = $handler->prepare($sql);
    $query->execute(array($result));

}

for

             $user = new User;
             $user->visible_password = "Sam";
             $user->hashed_password = "Walsh";
             $user->pdo_create_test();

the output is

INSERT INTO users (visible_password, hashed_password, temp_hashed_password, email, first_name, last_name, position, location, city, country, institution, interests, profile_comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Sam, Walsh, , , , , , , , , , ,

but no entry into the database....don't see why...the other DB fields are set to NULL so this isn't an issue....

Answer 1

PHP does have the concept of variable variables, so you can do this:

$varname = 'foo';

$foo = 123;

echo $$varname;

But this feature is limited. You can't apply it to all the elements of an array, nor can you use it with $this->foo notation for object variables.

You can use the variable variable syntax to get the value of your object variables corresponding to the attributes this way:

$place_holder = array();
foreach ($attributes as $key => $value) {
    $place_holder[] = $this->$key;
}

Notice the $key after ->. Normally object variable syntax does not have a $ in that place. By using a $ there, I'm telling it to get the value of the object variable whose name is the value of $key.

By the way, here's a more succinct (and probably faster) way to do the same thing:

$place_holder = array_intersect_key($attributes, get_object_vars($this));

---

Re your comment:

Well, first of all, you're doing this wrong:

$result = join(", ", array_values($place_holder));

This produces a single string with values separated by commas. It doesn't matter if you wrap it in array($result), that just creates an array of a single string, even though the single string contains commas.

In other words, these two arrays are quite different:

array('A,B,C') // array with one element

array('A', 'B', 'C') // array with three elements

Instead, you need to pass to execute() is an array with the same number of elements as the number of ? placeholders.

So just pass this:

$query->execute($place_holder);

Second, you need to check for errors after every prepare() or execute():

if (($query = $handler->prepare(...)) === false) {
  print_r($handler->errorInfo());
}
if ($query->execute($result) === false) {
  print_r($query->errorInfo());
}

Or if that seems like too much coding, you could instead enable PDO exceptions, and an error will cause your app to terminate with an error.

$handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Answer 2

I'm not sure what your place_holder variable contains, but I think this might be the reason. Join is just another name for the implode function which returns a string. Execute needs the actual physical input item.

From the PHP site on execute for the array of input values:

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

UPDATE

As stated in comments, here is a function that will execute a SQL string using the given data object. It would be best to separate this out into a couple of functions (create parameter array, bind parameters, ect), but I have put them all into one function for readability.

An example use of the function could be executeQuery('SELECT * FROM users WHERE id = :id', array('id' => 1));

function executeQuery($sql, $data) {
    //Assumed that $pdo is created and is a valid PDO object
    $params = array();
    preg_match_all('/:\w+/', $sql, $matches);
    //Loop through all the matches
    //Create an array that looks like: array(
    //  :id => 1, 
    //  :name => 'Me'
    //)
    foreach($matches[0] as $param) {
        $paramName = substr($param, 1);
        //If the item is not supplied in the data object, null will be used
        $params[$param] = isset($data[$paramName]) && !empty($data[$paramName]) ? $data[$paramName] : null;
    }
    //Pepare the SQL statement
    if(($stmt = $pdo->prepare($sql)) !== false) {
        //Bind all parameters
        foreach($params as $param => $value) {
            $stmt->bindValue($param, $value);
        }
        //Execute the statement
        $stmt->execute();
    }
}