使用 PDO 插入/更新辅助函数

Question

我有一个非常简单的辅助函数来为传统的普通 mysql 驱动程序使用生成 SET 语句：

function dbSet($fields) {
  $set='';
  foreach ($fields as $field) {
    if (isset($_POST[$field])) {
      $set.="`$field`='".mysql_real_escape_string($_POST[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

这样使用

$id = intval($_POST['id']);
$fields = explode(" ","name surname lastname address zip fax phone");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-".$_POST['d'];
$query  = "UPDATE $table SET ".dbSet($fields)." stamp=NOW() WHERE id=$id";

它使代码非常干燥、简单但同时又灵活。

我想问是否有人愿意分享类似的功能，利用 PDO 准备好的语句功能？

我仍然怀疑如何实现这一点。
有没有直接简单的方法使用 PDO 准备好的语句插入数据？ 应该是什么形式？查询生成器助手？或者插入查询助手？它应该采用什么参数？

我希望它可以很容易地用作此处的答案。因为在每个主题中我们都可以看到准备好的语句用法推荐，但没有一个好的例子。我的意思是，现实生活中的例子。我认为输入20次bind_param()并不是一个好的编程风格。 甚至还有20个问号。

Answer 1

您可以这样扩展 PDO：

class CustomPDO extends PDO {

    public function updateTable($sTable, array $aValues = array()){

        if (!empty($aValues) && !empty($sTable)){

            # validation of table / columns name
            $sTable = mysql_real_escape_string($sTable);

            $aColumns = array_map('mysql_real_escape_string',array_keys($aValues));

            $aElements = array();

            foreach ($aColumns as $sColumn){

                $aElements[] = "`$sColumn`= :$sColumn";

            } // foreach

            $sStatement = "UPDATE $sTable SET " . implode(',', $aElements);

            $oPDOStatement = $this->prepare($sStatement);

            if ($oPDOStatement){

                return $oPDOStatement->execute($aValues);

            } // if

        } // if

        return false;

    } // updateTable

}

# usage :
# $oDb->updateTable('tbl_name',$_POST);


# test

error_reporting (E_ALL);
ini_Set('display_errors',1);

$oDb = new CustomPDO('sqlite::memory:');

$oDb->exec('CREATE TABLE t1(c1 TEXT, c2 INTEGER)');

$oDb->exec("INSERT INTO t1(c1, c2) VALUES ('X1',1)");

var_dump($oDb->query('SELECT * FROM t1')->fetchAll(PDO::FETCH_ASSOC));

$oDb->updateTable('t1', array('c1'=>'f1','c2**2'=>2));

var_dump($oDb->query('SELECT * FROM t1')->fetchAll(PDO::FETCH_ASSOC));

Answer 2

与其他人一样，我扩展了标准 PDO 类以满足我的需要。类似的东西可能适合您：

Class ExtendedPDO extends PDO
{

    public function prepareArray($sql, array $data)
    {
        // Call the standard prepare method
        $statement = parent::prepare($sql);

        foreach ($data as $field=>$value) {
            $statement->bindValue(':' . $field, $value);
        }

        return $statement;
    }

}

然后您可以非常简单地使用它：

// Include connection variables
include '../includes/config/database.php';

// The data to use in the query
$data = array(
    'title' => 'New value',
    'id'    => 1,
);

// The query you want to run
$sql = '
    UPDATE
        test
    SET
        title = :title
    WHERE
        id = :id
';

try {
    // Connect to the database
    $dbh = new ExtendedPDO(PDO_DSN, PDO_USERNAME, PDO_PASSWORD);

    // Attach the data to your query
    $stmt = $dbh->prepareArray($sql, $data);

    // Run it
    $stmt->execute();
} catch (PDO Exception $e) {
    echo $e->getMessage();
}

Answer 3

插入查询通常需要许多占位符。问号样式难以阅读，命名参数重复并且容易出现输入错误。因此，我为整个插入查询创建了一个函数：

function insert($table, $col_val){
    global $db;
    $table = preg_replace('/[^\da-z_]/i', '', $table);
    $smt = $db->prepare("DESCRIBE `$table`");
    $smt->execute();
    $columns = $smt->fetchAll(PDO::FETCH_COLUMN);
    $sets = array();
    $exec = array();
    foreach($col_val as $col => $val){
        if(!in_array($col, $columns))
            return false;
        $sets[] .= "`$col`=?";
        $exec[] = $val;
    }
    $set = implode(',', $sets);
    $smt = $db->prepare("INSERT INTO `$table` SET $set");
    $smt->execute($exec);
    return $db->lastInsertId();
}

用法很简单：

insert('table_name', array(
    'msg'   =>  'New message',
    'added' =>  date('Y-m-d H:i:s'),
));

如果您需要 lastInsertId()：

$new_id = insert(...

Answer 4

参考：如何防止 PHP 中的 SQL 注入？

$preparedStatement = $db->prepare('SELECT * FROM employees WHERE name = :name');
$preparedStatement->execute(array(':name' => $name));
$rows = $preparedStatement->fetchAll();

Answer 5

我通常有一个扩展 PDO 的类，但我的类是非常自定义的。如果我将其清理并测试，我将在稍后发布。不过，这是针对您的系统的解决方案。

function dbSet($fields, &$values) {
    $set = '';
    $values = array();

    foreach ($fields as $field) {
        if (isset($_POST[$field])) {
            $set .= "`$field` = ?,";
            $values[] = $_POST[$field];
        }
    }

    return rtrim($set, ',');
}

$fields = explode(" ","name surname lastname address zip fax phone date");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-"$_POST['d'];

$query  = "UPDATE $table SET ".dbSet($fields, $values).", stamp=NOW() WHERE id=?";
$values[] = $id;

$dbh->prepare($query);
$dbh->execute($values);  

这可能并不完美，可以进行调整。它考虑到 $dbh 是通过 PDO 连接设置的。等待我提出的任何小语法问题，这应该可行。

编辑

说实话，我想我会选择 Doctrine ORM（或其他 ORM）。当您设置模型并在其中添加所有验证时，事情就很简单：

$table = new Table();
$table->fromArray($_POST);
$table->save();

这应该可以轻松填充内容。这当然是使用 ORM，比如 Doctrine。

已更新

对第一个代码进行了一些细微调整，例如将 isset 放回原处并在 substr 上使用 rtrim。要提供 PDO 扩展类的模型，只需布局实现方式并进行一些单元测试以确保其有效。

Answer 6

谢谢大家。
每个答案都很有帮助，我希望我能分享赏金。

最后，令我惊讶的是，我能够以与以前相同的方式进行制作，基于已接受的答案

$fields = array("login","password");
$_POST['password'] = MD5($_POST['login'].$_POST['password']);
$stmt = $dbh->prepare("UPDATE users SET ".pdoSet($fields,$values)." WHERE id = :id");
$values["id"] = $_POST['id'];
$stmt->execute($values);

它可以包装到辅助函数中，但我怀疑是否有必要。它将把代码缩短一行。

pdoSet代码：

function pdoSet($fields, &$values, $source = array()) {
  $set = '';
  $values = array();
  if (!$source) $source = &$_POST;
  foreach ($fields as $field) {
    if (isset($source[$field])) {
      $set.="`$field`=:$field, ";
      $values[$field] = $source[$field];
    }
  }
  return substr($set, 0, -2); 
}

Answer 7

我会扩展核心 PDO 类并添加一个像这样的方法：

class Database extends PDO
{
    public function QueryFromPost($Query,$items)
    {
        $params = array();
        $Query .= ' WHERE ';

        foreach($items as $key => $default)
        {
             $Query .= ' :' . $key. ' = ' . $key;
             if(isset($_POST[$key]))
             {
                  $params[':' . $key] = $_POST[$key];
             }else
             {
                 $params[':' . $key] = $default;
             }
        }
        $s = $this->prepare($Query);
        return $s->execute($params);
    }
}

然后像这样使用

$db = new Database(/*..Default PDO Params*/);
$statement = $db->QueryFromPost('SELECT * FROM employees',array('type' => 'plc'));
foreach($preparedStatement->fetchAll() as $row)
{
    //...
}

但是正如已经说过的那样，您应该对您尝试做的事情感到非常厌倦，您需要验证您的数据，它已被清理，但您未经验证。

Answer 8

对于我认为重复出现的参数绑定情况，我一直在修补一些琐碎的事情。
http://fossil.include-once.org/hybrid7/wiki/db

无论如何；它提供了一些替代准备好的语句占位符。您的示例可以缩短为：

db("UPDATE table SET :, WHERE id=:id", $columns[], $where[]);

这种情况仅适用于命名参数，因此 $set 将是 array("name"=>..) 和 $where=array("id"=>123)。 :, 在您传递的第一个数组上展开。它被替换为逗号分隔的名称=:名称对（这就是它的助记符是:,的原因）。

还有一些占位符 :, :& :: 和 :? 用于不同的用例。只有 ?? 确实有点标准。因此它需要一些时间来适应，但它显着简化了准备好的语句和数组绑定（PDO 本身不这样做）。

Answer 9

这是我的通用数据库抽象类。看一下 autoExecute() 函数。它为您想要完成的任何任务提供了大量的灵活性。我应该警告一下，这是为 PHP 5.3 编写的，并且针对 PostgreSQL 进行了轻微的定制。

<?php
/**
 * Database abstraction and query result classes
 * Requires PHP 5.3
 *
 * Events:
 *  - on_commit - Dispatched when the transaction is successfully committed to the DB
 *  - on_rollback - Dispatched when the transaction is rolled back in the DB
 *
 * @author Kenaniah Cerny <[email protected]>
 * @version 1.1.2
 * @license http://creativecommons.org/licenses/by/3.0/us/
 * @copyright Copyright (c) 2009, Kenaniah Cerny
 */
class Database extends PDO {

    private $stmt;
    private $good_trans = null;
    private $nested_transactions = 0; //Keeps track of virtual transaction nesting level
    private $callbacks = array();

    private static $connections = array(); //Keeps track of opened connections

    /**
     * Returns a database instance using lazy instantiation
     * @param string $name a database connection name
     * @param array $config database config details for a new connection
     */
    static function getInstance($name = 'main', $config=array()){

        //Attempt to return an existing connection
        if(array_key_exists($name, self::$connections)):
            return self::$connections[$name];
        endif;

        //Attempt to create a new connection
        $host = in_array($config['host'], array('localhost', '127.0.0.1')) ? "" : ";host=" . $config['host'];
        $db = new Database($config['driver'].":dbname=".$config['name'].$host, $config['user'], $config['pass']);

        //Save to connection pool
        self::$connections[$name] = $db;

        return $db;

    }

    /**
     * Registers a callback to be run when the given event is invoked
     * @param string $event Event name
     * @param callable $callable
     */
    public function register_listener($event, $callable){

        if(!array_key_exists($event, $this->callbacks)):
            $this->callbacks[$event] = array($callable);
        else:
            $this->callbacks[$event][] = $callable;
        endif;

    }

    /**
     * Invokes callbacks for the given event type
     * @param string $event Event name
     * @param boolean $stop_on_false Stops bubbling this event if one of the handlers returns false
     */
    protected function dispatch_event($event, $stop_on_false = true){

        if(!array_key_exists($event, $this->callbacks)) return;

        foreach($this->callbacks[$event] as $callable):

            $res = call_user_func($callable, $this, $event);
            if($stop_on_false && $res === false) return false;

        endforeach;

        return true;

    }

    /**
     * PDO Constructor
     * @param $dsn
     * @param $username
     * @param $password
     */
    function __construct($dsn, $username, $password) {
        parent::__construct($dsn, $username, $password);
    }

    /**
     * Prepares an SQL statement
     * @param string $sql
     */
    function prepare($sql) {
        $stmt = parent::prepare($sql, array(PDO::ATTR_STATEMENT_CLASS => array(__NAMESPACE__.'\DatabaseStatement')));
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt;
    }

    /**
     * Prepares an executes an SQL statement with the parameters provided
     * @param string $sql
     * @param array $params
     */
    function execute($sql, $params = array()) {

        if($this->debug):
            var_dump("Statement:\n".$sql."\nParams: ".$this->fmt($params));
        endif;

        try {
            $stmt = $this->prepare($sql);
            $val = $stmt->execute((array) $params);
            if($stmt->errorCode() != '00000') error_log($this->errormsg());
            if($this->debug && $stmt->errorCode() != '00000'){
                var_dump($stmt->errorInfo());
                Errors::add("Database error: ".$this->errormsg(), E_USER_ERROR);
            }
            if(!$val) return false;
        } catch (PDOException $e){
            if($this->debug) var_dump($stmt->errorInfo());
            error_log($this->errormsg());
            Errors::add("Database error: ".$this->errormsg(), E_USER_ERROR);
            if($this->nested_transactions) $this->failTrans();
            else throw $e;
        }

        $this->stmt = $stmt;

        return $stmt;

    }    

    /**
     * Returns the value of the first column of the first row
     * of the database result.
     * @param $sql
     * @param $params
     */
    function getOne($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getOne() : false;
    }

    /**
     * Fetches a single column (the first column) of a result set
     * @param $sql
     * @param $params
     */
    function getCol($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getCol() : false;
    }

    /**
     * Fetches rows in associative array format
     * @param $sql
     * @param $params
     */
    function getAssoc($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getAssoc() : false;
    }

    /**
     * Fetches rows in array format with columns
     * indexed by ordinal position
     * @param $sql
     * @param $params
     */
    function getArray($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getArray() : false;
    }

    /**
     * Fetches all rows in associative array format
     * @param $sql
     * @param $params
     */
    function getAll($sql, $params = array()){
        return $this->getAssoc($sql, $params);
    }

    /**
     * Fetches rows in array format where the first column
     * is the key name and all other columns are values
     * @param $sql
     * @param $params
     */
    function getKeyPair($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getKeyPair() : false;
    }

    /**
     * Fetches rows in multi-dimensional format where the first
     * column is the key name and all other colums are grouped
     * into associative arrays for each row
     * @param $sql
     * @param $params
     */
    function getGroup($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getGroup() : false;
    }

    /**
     * Fetches only the first row and returns it as an
     * associative array
     * @param $sql
     * @param $params
     */
    function getRow($sql, $params = array()){
        $stmt = $this->execute($sql, $params);
        return $stmt ? $stmt->getRow() : false;
    }

    /**
     * Internal function used for formatting parameters in debug output
     * @param unknown_type $params
     */
    private function fmt($params){
        $arr = array();
        foreach((array) $params as $k=>$v){
            if(is_null($v)) $v = "NULL";
            elseif(is_bool($v)) $v = $v ? "TRUE" : "FALSE";
            $arr[] = "[".$k."] => ".$v;
        }
        return "Array(".join(", ", $arr).")";
    }

    /**
     * Returns the number of affected rows from an executed statement
     */
    function affected_rows(){
        return $this->stmt ? $this->stmt->rowcount() : false;
    }

    /**
     * Automated statement processing
     *
     * Params array takes the following fields:
     *
     *  - table         The name of the table to run the query on
     *
     *  - data          A key-value paired array of table data
     *
     *  - mode          INSERT, UPDATE, REPLACE, or NEW
     *
     *  - where         Can be a string or key-value set. Not used on INSERTs
     *                  If key-value set and numerically indexed, uses values from data
     *                  If key-value and keys are named, uses its own values
     *
     *  - params        An array of param values for the where clause
     *
     *  - returning     Optional string defining what to return from query.
     *                  Uses PostgreSQL's RETURNING construct
     *
     *  This method will return either a boolean indicating success, an array
     *  containing the data requested by returning, or a boolean FALSE indicating
     *  a failed query.
     *
     */
    function autoExecute($table, $params, $data){

        $fields = array(); //Temp array for field names
        $values = array(); //Temp array for field values
        $set = array(); //Temp array for update sets
        $ins = array(); //Insert value arguments

        $params['table'] = $table;
        $params['data'] = $data;

        $params['params'] = (array) $params['params'];

        //Parse the data set and prepare it for different query types
        foreach((array) $params['data'] as $field => $val):

            $fields[] = $field;
            $values[] = $val;
            $ins[] = "?";
            $set[] = $field . " = ?";

        endforeach;

        //Check for and convert the array/object version of the where clause param
        if(is_object($params['where']) || is_array($params['where'])):

            $clause = array();
            $params['params'] = array(); //Reset the parameters list

            foreach($params['where'] as $key => $val):

                if(is_numeric($key)):
                    //Numerically indexed elements use their values as field names
                    //and values from the data array as param values
                    $field = $val;
                    $params['params'][] = $params['data'][$val];
                else:
                    //Named elements use their own names and values
                    $field = $key;
                    $params['params'][] = $val;
                endif;

                $clause[] = $field . " = ?";

            endforeach;

            $params['where'] = join(" AND ", $clause);

        endif;

        //Figure out what type of query we want to run
        $mode = strtoupper($params['mode']);
        switch($mode):
            case 'NEW':
            case 'INSERT':

                //Build the insert query
                if(count($fields)):
                    $sql =  "INSERT INTO " . $params['table']
                            . " (" . join(", ", $fields) . ")"
                            . " SELECT " . join(", ", $ins);
                else:
                    $sql =  "INSERT INTO " . $params['table']
                            . " DEFAULT VALUES";
                endif;

                //Do we need to add a conditional check?
                if($mode == "NEW" && count($fields)):
                    $sql .= " WHERE NOT EXISTS ("
                            . " SELECT 1 FROM " . $params['table']
                            . " WHERE " . $params['where']
                            . " )";
                    //Add in where clause params
                    $values = array_merge($values, $params['params']);
                endif;

                //Do we need to add a returning clause?
                if($params['returning']):
                    $sql .= " RETURNING " . $params['returning'];
                endif;

                //Execute our query
                $result = $this->getRow($sql, $values);

                //Return our result
                if($params['returning']):
                    return $result;
                else:
                    return $result !== false;
                endif;

                break;
            case 'UPDATE':

                if(!count($fields)) return false;

                //Build the update query
                $sql =  "UPDATE " . $params['table']
                        . " SET " . join(", ", $set)
                        . " WHERE " . $params['where'];

                //Do we need to add a returning clause?
                if($params['returning']):
                    $sql .= " RETURNING " . $params['returning'];
                endif;

                //Add in where clause params
                $values = array_merge($values, $params['params']);

                //Execute our query
                $result = $this->getRow($sql, $values);

                //Return our result
                if($params['returning']):
                    return $result;
                else:
                    return $result !== false;
                endif;

                break;
            case 'REPLACE': //UPDATE or INSERT

                //Attempt an UPDATE
                $params['mode'] = "UPDATE";
                $result = $this->autoExecute($params['table'], $params, $params['data']);

                //Attempt an INSERT if UPDATE didn't match anything
                if($this->affected_rows() === 0):
                    $params['mode'] = "INSERT";
                    $result = $this->autoExecute($params['table'], $params, $params['data']);
                endif;

                return $result;

                break;
            case 'DELETE':

                //Don't run if we don't have a where clause
                if(!$params['where']) return false;

                //Build the delete query
                $sql =  "DELETE FROM " . $params['table']
                        . " WHERE " . $params['where'];

                //Do we need to add a returning clause?
                if($params['returning']):
                    $sql .= " RETURNING " . $params['returning'];
                endif;

                //Execute our query
                $result = $this->getRow($sql, $params['params']);

                //Return our result
                if($params['returning']):
                    return $result;
                else:
                    return $result !== false;
                endif;

                break;
            default:
                user_error('AutoExecute called incorrectly', E_USER_ERROR);
                break;
        endswitch;

    }

    /**
     * @see $this->startTrans()
     */
    function beginTransaction(){
        $this->startTrans();
    }

    /**
     * Starts a smart transaction handler. Transaction nesting is emulated
     * by this class.
     */
    function startTrans(){

        $this->nested_transactions++;
        if($this->debug) var_dump("Starting transaction. Nesting level: " . $this->nested_transactions);

        //Do we need to begin an actual transaction?
        if($this->nested_transactions === 1):
            parent::beginTransaction();
            $this->good_trans = true;
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        endif;

    }

    /**
     * Returns TRUE if the transaction will attempt to commit, and
     * FALSE if the transaction will be rolled back upon completion.
     */
    function isGoodTrans(){
        return $this->good_trans;
    }

    /**
     * Marks a transaction as a failure. Transaction will be rolled back
     * upon completion.
     */
    function failTrans(){
        if($this->nested_transactions) $this->good_trans = false;
        if($this->debug):
            Errors::add("Database transaction failed: ".$this->errorMsg());
        endif;
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    }

    /**
     * @see $this->rollbackTrans()
     */
    function rollback(){
        $this->rollbackTrans();
    }

    /**
     * Rolls back the entire transaction and completes the current nested
     * transaction. If there are no more nested transactions, an actual
     * rollback is issued to the database.
     */
    function rollbackTrans(){
        if($this->nested_transactions):
            $this->nested_transactions--;
            if($this->debug) var_dump("Rollback requested. New nesting level: " . $this->nested_transactions);
            $this->good_trans = false;
            if($this->nested_transactions === 0):
                $this->good_trans = null;
                $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
                if($this->debug) var_dump("Transaction rolled back.");
                parent::rollback();
                $this->dispatch_event('on_rollback');
            endif;
        endif;
    }

    /**
     * Clears the nested transactions stack and issues a rollback to the database.
     */
    function fullRollback(){
        while($this->nested_transactions) $this->rollbackTrans();
    }

    /**
     * Returns the number of nested transactions:
     * 0 - There is no transaction in progress
     * 1 - There is one transaction pending
     * >1 - There are nested transactions in progress
     */
    function pending_trans(){
        return $this->nested_transactions;
    }

    /**
     * @see $this->completeTrans()
     */
    function commit($fail_on_user_errors = false){
        return $this->completeTrans($fail_on_user_errors);
    }

    /**
     * Completes the current transaction and issues a commit or rollback to the database
     * if there are no more nested transactions. If $fail_on_user_errors is set, the
     * transaction will automatically fail if any errors are queued in the Errors class.
     * @param boolean $fail_on_user_errors
     */
    function completeTrans($fail_on_user_errors = false){

        if(!$this->nested_transactions) return;

        //Fail the transaction if we have user errors in the queue
        if($fail_on_user_errors && Errors::exist()) $this->good_trans = false;

        //Do we actually need to attempt to commit the transaction?
        if($this->nested_transactions === 1):

            if(!$this->good_trans || !parent::commit()){
                if($this->debug) var_dump("Transaction failed: " . $this->errormsg());
                $this->rollbackTrans();
                return false;
            }

            //Transaction was good
            $this->nested_transactions--;
            $this->good_trans = null;
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
            if($this->debug) var_dump("Transaction committed.");
            $this->dispatch_event('on_commit', false);
            return true;
        else:
            //Don't take action just yet as we are still nested
            $this->nested_transactions--;
            if($this->debug) var_dump("Virtual commit. New nesting level: " . $this->nested_transactions);
        endif;

        return $this->good_trans;

    }

    /**
     * Returns the text of the most recently encountered error
     */
    function errormsg(){
        $msg = $this->errorInfo();
        return $msg[2];
    }

}

class DatabaseStatement extends \PDOStatement implements \Countable {

    /**
     * Binds passed parameters according to their PHP type and executes
     * the prepared statement
     */
    function execute($params = array()) {
        $i = 1;
        foreach($params as $k => $v):
            $mode = PDO::PARAM_STR;
            if(is_null($v)) $mode = PDO::PARAM_NULL;
            elseif(is_bool($v)) $mode = PDO::PARAM_BOOL;
            elseif(is_resource($v)) $mode = PDO::PARAM_LOB;
            $this->bindParam($i, $params[$k], $mode);
            $i++;
        endforeach;
        $ok = parent::execute();
        return $ok ? $this : false;
    }

    /**
     * Returns the value of the first column of the first row
     */
    function getOne() {
        return $this->fetchColumn(0);
    }

    /**
     * Returns an array of values of the column found at $index
     * position.
     * @param $index
     */
    function getCol($index=0) {
        return $this->fetchAll(PDO::FETCH_COLUMN, $index);
    }

    /**
     * Returns all rows in numeric array format
     */
    function getArray(){
        return $this->fetchAll(PDO::FETCH_NUM);
    }

    /*
     * Returns all rows in associative array format
     */
    function getAll(){
        return $this->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Returns all rows in associative array format
     */
    function getAssoc() {
        return $this->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Returns rows in multi-dimensional format where the first
     * column is the key name and all other colums are grouped
     * into associative arrays for each row
     */
    function getGroup() {
        return $this->fetchAll(PDO::FETCH_GROUP);
    }

    /**
     * Returns a single row in associative format
     */
    function getRow(){
        return $this->fetch(PDO::FETCH_ASSOC);
    }

    /**
     * Fetches rows in array format where the first column
     * is the key name and all other columns are values
     */
    function getKeyPair(){
        //Emulate it
        $tmp = $this->fetchAll(PDO::FETCH_ASSOC);
        $arr = array();
        for($i = 0; $i < count($tmp); $i++){
            $arr[array_shift($tmp[$i])] = count($tmp[$i]) > 1 ? $tmp[$i] : array_shift($tmp[$i]);
        }
        return $arr;
    }

    /**
     * Returns the number of rows returned by this statement
     */
    function recordCount(){

        return $this->rowCount();

    }

    /**
     * Returns the number of rows returned by this statement
     */
    function count(){

        return $this->rowCount();

    }
}

Answer 10

尽管我的数据库类不使用准备好的语句，但我仍然想在这里提及它。我认为没有理由用准备好的语句来实现所有事情。我确实知道准备好的语句更快，但仅限于多次使用。如果您仅执行一次查询（这是我通常需要使用的唯一查询类型），则它较慢。因此，到处使用准备好的语句会适得其反。

可以找到该类的正确描述stackoverflow 的其他地方。但这里有一些好东西：

• 不到 100 行数据库层
• DB::instance()->execute 和 DB:: DB::x q 用于 DB::instance()->query
• 自动引用，具有两种类型的占位符 ? 和 ?x （其中 < code>x 可以是 、、& 和 |）。 ?, 占位符可以在此处用作 UPDATE 助手。

但有关完整信息，请参阅上面链接的 stackoverflow 帖子；)

PS：存储库中的自述文件不适用于此类。它适用于普通的DB.php，而不适用于DB_intelligent.php。
PPS：该类是为 PHP 5.3 编写的。如果您想在 PHP 5.2 上使用它，只需将所有这些 PDO 方法从 DB_forPHP52.php 复制到 DB_intelligent.php 并删除 __callStatic 方法。

Answer 11

除了其他答案之外：正确引用列名的方法：

/**
 * Escape identifier (database/table/column name) - ie. if you're using MySQL:
 * db_name.tbl_name.col_name -> `db_name`.`tbl_name`.`col_name`
 **/
protected function quoteIdentifier($identifier) {
    static $escapeChars = array(
        'mysql'  => '``',
        'oracle' => '""',
        'mssql'  => '[]',
        //...
    );

    $escape = $escapeChars[$this->getAttribute(self::ATTR_DRIVER_NAME)];
    $identifier = (array) explode('.', $identifier);
    $identifier = array_map(function($segment) use($escape) {
        return $escape[0] . $segment . $escape[1];
    }, $identifier);

    return implode('.', $identifier);
}