mysql_query到PDO和准备好的语句 [英] mysql_query to PDO and prepared statements

问题描述

我在CMS中具有此功能，该功能将字段和值的变量列表插入两个不同的表中.一个是静态索引表，一个是动态索引表.这是功能:

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);

}

因此，对于准备好的语句，我不能仅将值绑定到字段，对吗?因此，如果我做类似的事情，我仍然不得不逃避这些领域的挑战:

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);

如果我仍然必须逃离田野，那又有什么意义呢?此功能最终将包含多个文章及其字段的数组.这些字段本身也每次都不同..我发现当我第一次查看准备好的语句时，我可以将其交给一个字段数组和一个值数组，但是我想并不是这样.

我的问题是，你们所有人将如何实现?我想开始与数据库无关，而PDO看起来是实现此目的的好方法.

解决方案

PHP提供了许多便捷功能，这些功能可以手工完成很多工作.

• PDO在您的SQL语句中支持命名参数，因此您可以在其中的键与命名参数占位符匹配的地方传递键/值数组.
• join() 函数对于构建以逗号分隔的列表非常有用.
• 存在许多函数来操纵数组.
• 某些功能允许您将回调(可以是PHP 5.3中的闭包)提供给动态处理数组.

示例(未经测试):

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);
}

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.

解决方案

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);
}

这篇关于mysql_query到PDO和准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！