CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
Ben
Ben

Reputation: 745

mysql_query to PDO and prepared statements

I have this function in my CMS, which inserts a variable list of fields and values into two different tables; one is a static index table and one is dynamic. This is the function:

function insertFields($fields)
{
   $stdfields = array();
   $extfields = array();

   /* Separate the fields based on if the fields is standard or extra.  $this->fields is a csv list of the defined extra fields */
   foreach($fields as $field => $value)
   {
      $fields[mysql_real_escape_string($field)] = mysql_real_escape_string($value);

      if(strstr($this->fields, $field))
         $extfields[$field] = $value;
      else
         $stdfields[$field] = $value;   
   }

   //Build the 2 queries -- Maybe there is a better way to do this?
   $extfieldcount = count($extfields);
   $stdfieldcount = count($stdfields);
   $stditers = 0;
   $extiters = 0;

   foreach($extfields as $field => $value)
   {
      if($extiters != $extfieldcount)
      {
         $extfields.= $field.", ";
         $extvalues.= "'".$value."', ";
      }
      else
      {
         $extfields.= $field." ";
         $extvalues.= "'".$value."' ";
      }
      $extiters++;
   }

   foreach($stdfields as $field => $value)
   {
      if($stditers != $stdfieldcount)
      {
         $newfields.= $field.", ";
         $newvalues.= "'".$value."', ";
      }
      else
      {
         $newfields.= $field." ";
         $newvalues.= "'".$value."' ";
      }
   $stditers++;
   }

   //Inset the standard fields
   $stdquery = "INSERT INTO masteridx (".$newfields.") VALUES (".$newvalues.")";
   $this->dbQuery($stdquery);

   /* not perfect. I need a better way to find the id that was inserted, so I can combine three queries into at least two */

   $findlastquery = "SELECT `id` FROM `masteridx` WHERE `slug`='".$fields['slug']."' LIMIT 1";
   $result = $this->dbQuery($findlastquery);
   $result = mysql_fetch_assoc($result);
   $tempfield = "id, ";
   $tempvalue = "'".$result['id']."', ";

   //Insert the extra fields
   $extquery = "INSERT INTO ".$this->type." (".$tempfield.$extfields.") VALUES (".$tempvalue.$extvalues.")";
   $this->dbQuery($extquery);

}

So for a prepared statement, I can't Bind the fields, just the values, right? So I would still have to escape the fields if I did something like:

for ($i = 0; $i <= $stdfieldcount; $i++)
{
    if($i < $stdfieldcount)
    $qs.= '?, ';
    else
    $qs.= '? '; 
}

$sth = $dbh->prepare("INSERT INTO masteridx ({$stdfields}) VALUES ({$qs})");
$sth->execute($array_of_stdfield_values);

What's the point here if I still have to escape the fields? This function will eventually take an array of multiple articles and their fields. The fields themselves would be different each time, as well.. I figured that when I first looked at prepared statements, I could just hand it an array of fields, and an array of values, but I guess that's not the case.

My question is really, how would you all accomplish this? I would like to start being database agnostic, and PDO looked like a great way to do this.

Upvotes: 3

Views: 935

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562931

PHP provides quite a few convenience functions that do a lot of the stuff you're doing by hand.

  • PDO supports named parameters in your SQL statements, so you can then pass a key/value array where the keys match your named parameter placeholders.
  • The join() function is very useful for building comma-separated lists.
  • Many functions exist to manipulate arrays.
  • Some functions allow you to give a callback (which can be a closure in PHP 5.3), to process arrays dynamically.

Example (not tested):

function insertFields($fields) {
    $columns = join(",", array_map(
        function($col) { return "`".preg_replace("/`/gu","``",$col)."`"}, 
        array_keys($fields)));

    $params = join(",", array_map(
        function($col) { return ":".preg_replace("/[`\s]/gu","",$col)},
        array_keys($fields)));

    $stdquery = "INSERT INTO masteridx ({$columns}) VALUES ({$params})";
    $stmt = $pdo->prepare($stdQuery);
    $stmt->execute($fields);
}

Upvotes: 4

Related Questions