PDO in PHP

PHP What is PDO?

PDO (PHP Data Objects) is an interface for connecting to databases in PHP. PDO and mysqli were once recommended to replace the mysql related functions originally used by PHP, based on the security of database use, because the latter lacked protection against SQL injection.

PHP Data Objects (PDO) extension defines a lightweight consistent interface for PHP to access databases. Each database driver that implements the PDO interface can expose database-specific features as standard extensions. Note that using PDO extensions does not implement any database functionality by itself; you must use a database-specific PDO driver to access database services.

Related mysql video tutorial recommendations: "mysql tutorial"

PDO provides a data access abstraction layer, which means that no matter which database is used, it can be used Same functions (methods) to query and get data. PDO does not provide a database abstraction layer; it does not rewrite SQL and does not emulate missing features. If necessary, a mature abstraction layer should be used.

Database support:

• firebird

• ##informix

• mysql

• mssql

• odbc

• pgsql

• sqlite

PHP PDO Errors and Error Handling

PDO provides three different error handling modes to meet different styles of applications Development:

PDO::ERRMODE_SILENT

This is the default mode. PDO will simply set the error code, which can be checked using the PDO::errorCode() and PDO::errorInfo() methods on statements and database objects. If the error occurs due to a call to a statement object, you can call that object's PDOStatement::errorCode() or PDOStatement::errorInfo() method. If the error is caused by calling a database object, then the above two methods can be called on the database object.

PDO::ERRMODE_WARNING

In addition to setting the error code, PDO will also send a traditional E_WARNING message. This setting is useful during debugging/testing if you just want to see what's going wrong without interrupting the flow of your application.

PDO::ERRMODE_EXCEPTION

In addition to setting the error code, PDO will also throw a PDOException exception class and set its properties to reflect the error code and error information. This setting is also very useful during debugging, as it effectively zooms in on the point in the script where the error occurred, allowing you to very quickly pinpoint potential problematic areas in the code (remember: if an exception causes the script to terminate, the transaction is automatically rolled back roll).

Another very useful thing about exception mode is that you can build your own error handling more clearly than traditional PHP-style warnings, and compared to silent mode and explicitly checking the return value of each database call , exception patterns require less code/nesting.

PHP PDO uses

to connect to MySQL

<?php

$type = &#39;mysql&#39;;
$hostname = &#39;localhost&#39;;
$dbname = &#39;test&#39;;
$username = &#39;root&#39;;
$password = &#39;root&#39;;

try {

    $dsn = sprintf(&#39;%s:dbname=%s;host=%s&#39;, $type, $dbname, $dbname);

    //初始化一个PDO对象
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION //开启异常模式
    ]);

} catch (PDOException $e) {
    die ("Database error: " . $e->getMessage());
}

?>

Query data

$type = 'mysql';
$hostname = '127.0.0.1';
$dbname = 'test';
$username = 'root';
$password = 'root';

try {

    $dsn = sprintf('%s:dbname=%s;host=%s;charset=utf8', $type, $dbname, $hostname);

    //初始化一个PDO对象
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION //开启异常模式
    ]);

} catch (PDOException $e) {
    die ("Database error: " . $e->getMessage());
}

$smt = $pdo->query('SELECT * FROM t_user');

$data = $smt->fetchAll(PDO::FETCH_ASSOC);

var_dump($data);

Print results

array(5) {
  [0]=>
  array(4) {
    ["password"]=>
    string(8) "jidasdas"
    ["phone"]=>
    string(9) "888888888"
    ["user_id"]=>
    string(32) "402881e564c0da7b0164c11adc8f0006"
    ["user_name"]=>
    string(5) "marry"
  }
  [1]=>
  array(4) {
    ["password"]=>
    string(4) "tiyv"
    ["phone"]=>
    string(6) "000000"
    ["user_id"]=>
    string(32) "402881e564c0da7b0164c1227c5d000b"
    ["user_name"]=>
    string(6) "Bliabx"
  }
  [2]=>
  array(4) {
    ["password"]=>
    string(5) "dsada"
    ["phone"]=>
    string(7) "3123123"
    ["user_id"]=>
    string(32) "402881e764bbd6340164bbd6af4e0001"
    ["user_name"]=>
    string(4) "Nusg"
  }
  [3]=>
  array(4) {
    ["password"]=>
    string(4) "kjhk"
    ["phone"]=>
    string(6) "321312"
    ["user_id"]=>
    string(32) "402881e764bbd7b60164bbd9c3cb0002"
    ["user_name"]=>
    string(6) "XIoaji"
  }
  [4]=>
  array(4) {
    ["password"]=>
    string(3) "dsa"
    ["phone"]=>
    string(3) "110"
    ["user_id"]=>
    string(32) "402881e764bbed9f0164bbee12c70000"
    ["user_name"]=>
    string(6) "Villig"
  }
}

Preprocessing

/* 通过数组值向预处理语句传递值 */
$sql = &#39;SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour&#39;;

$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);

$sth->execute([&#39;:calories&#39; => 150, &#39;:colour&#39; => &#39;red&#39;]);

$red = $sth->fetchAll();

$sth->execute([&#39;:calories&#39; => 175, &#39;:colour&#39; => &#39;yellow&#39;]);

$yellow = $sth->fetchAll();

Basic PDO package

<?php
/**
* DAOPDO
* @authors by houzhyan <houzhyan@126.com>
* @blog http://www.descartes.top/
* @version >5.1 utf8
*/
class DAOPDO
{
    protected static $_instance = null;
    protected $dbName = &#39;&#39;;
    protected $dsn;
    protected $dbh;
    
    /**
     * 构造
     * 
     * @return DAOPDO
     */
    private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        try {
            $this->dsn = &#39;mysql:host=&#39;.$dbHost.&#39;;dbname=&#39;.$dbName;
            $this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);
            $this->dbh->exec(&#39;SET character_set_connection=&#39;.$dbCharset.&#39;, character_set_results=&#39;.$dbCharset.&#39;, character_set_client=binary&#39;);
        } catch (PDOException $e) {
            $this->outputError($e->getMessage());
        }
    }
    
    /**
     * 防止克隆
     * 
     */
    private function __clone() {}
    
    /**
     * Singleton instance
     * 
     * @return Object
     */
    public static function getInstance($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
    {
        if (self::$_instance === null) {
            self::$_instance = new self($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset);
        }
        return self::$_instance;
    }
    
    /**
     * Query 查询
     *
     * @param String $strSql SQL语句
     * @param String $queryMode 查询方式(All or Row)
     * @param Boolean $debug
     * @return Array
     */
    public function query($strSql, $queryMode = &#39;All&#39;, $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $recordset = $this->dbh->query($strSql);
        $this->getPDOError();
        if ($recordset) {
            $recordset->setFetchMode(PDO::FETCH_ASSOC);
            if ($queryMode == &#39;All&#39;) {
                $result = $recordset->fetchAll();
            } elseif ($queryMode == &#39;Row&#39;) {
                $result = $recordset->fetch();
            }
        } else {
            $result = null;
        }
        return $result;
    }
    
    /**
     * Update 更新
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function update($table, $arrayDataValue, $where = &#39;&#39;, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        if ($where) {
            $strSql = &#39;&#39;;
            foreach ($arrayDataValue as $key => $value) {
                $strSql .= ", `$key`=&#39;$value&#39;";
            }
            $strSql = substr($strSql, 1);
            $strSql = "UPDATE `$table` SET $strSql WHERE $where";
        } else {
            $strSql = "REPLACE INTO `$table` (`".implode(&#39;`,`&#39;, array_keys($arrayDataValue))."`) VALUES (&#39;".implode("&#39;,&#39;", $arrayDataValue)."&#39;)";
        }
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    
    /**
     * Insert 插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function insert($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "INSERT INTO `$table` (`".implode(&#39;`,`&#39;, array_keys($arrayDataValue))."`) VALUES (&#39;".implode("&#39;,&#39;", $arrayDataValue)."&#39;)";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    
    /**
     * Replace 覆盖方式插入
     *
     * @param String $table 表名
     * @param Array $arrayDataValue 字段与值
     * @param Boolean $debug
     * @return Int
     */
    public function replace($table, $arrayDataValue, $debug = false)
    {
        $this->checkFields($table, $arrayDataValue);
        $strSql = "REPLACE INTO `$table`(`".implode(&#39;`,`&#39;, array_keys($arrayDataValue))."`) VALUES (&#39;".implode("&#39;,&#39;", $arrayDataValue)."&#39;)";
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    
    /**
     * Delete 删除
     *
     * @param String $table 表名
     * @param String $where 条件
     * @param Boolean $debug
     * @return Int
     */
    public function delete($table, $where = &#39;&#39;, $debug = false)
    {
        if ($where == &#39;&#39;) {
            $this->outputError("&#39;WHERE&#39; is Null");
        } else {
            $strSql = "DELETE FROM `$table` WHERE $where";
            if ($debug === true) $this->debug($strSql);
            $result = $this->dbh->exec($strSql);
            $this->getPDOError();
            return $result;
        }
    }
    
    /**
     * execSql 执行SQL语句,debug=>true可打印sql调试
     *
     * @param String $strSql
     * @param Boolean $debug
     * @return Int
     */
    public function execSql($strSql, $debug = false)
    {
        if ($debug === true) $this->debug($strSql);
        $result = $this->dbh->exec($strSql);
        $this->getPDOError();
        return $result;
    }
    
    /**
     * 获取字段最大值
     * 
     * @param string $table 表名
     * @param string $field_name 字段名
     * @param string $where 条件
     */
    public function getMaxValue($table, $field_name, $where = &#39;&#39;, $debug = false)
    {
        $strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";
        if ($where != &#39;&#39;) $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, &#39;Row&#39;);
        $maxValue = $arrTemp["MAX_VALUE"];
        if ($maxValue == "" || $maxValue == null) {
            $maxValue = 0;
        }
        return $maxValue;
    }
    
    /**
     * 获取指定列的数量
     * 
     * @param string $table
     * @param string $field_name
     * @param string $where
     * @param bool $debug
     * @return int
     */
    public function getCount($table, $field_name, $where = &#39;&#39;, $debug = false)
    {
        $strSql = "SELECT COUNT($field_name) AS NUM FROM $table";
        if ($where != &#39;&#39;) $strSql .= " WHERE $where";
        if ($debug === true) $this->debug($strSql);
        $arrTemp = $this->query($strSql, &#39;Row&#39;);
        return $arrTemp[&#39;NUM&#39;];
    }
    
    /**
     * 获取表引擎
     * 
     * @param String $dbName 库名
     * @param String $tableName 表名
     * @param Boolean $debug
     * @return String
     */
    public function getTableEngine($dbName, $tableName)
    {
        $strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name=&#39;".$tableName."&#39;";
        $arrayTableInfo = $this->query($strSql);
        $this->getPDOError();
        return $arrayTableInfo[0][&#39;Engine&#39;];
    }
    //预处理执行
    public function prepareSql($sql=&#39;&#39;){
        return $this->dbh->prepare($sql);
    }
    //执行预处理
    public function execute($presql){
        return $this->dbh->execute($presql);
    }
 
/**
 * pdo属性设置
 */
    public function setAttribute($p,$d){
        $this->dbh->setAttribute($p,$d);
    }
 
    /**
     * beginTransaction 事务开始
     */
    public function beginTransaction()
    {
        $this->dbh->beginTransaction();
    }
    
    /**
     * commit 事务提交
     */
    public function commit()
    {
        $this->dbh->commit();
    }
    
    /**
     * rollback 事务回滚
     */
    public function rollback()
    {
        $this->dbh->rollback();
    }
    
    /**
     * transaction 通过事务处理多条SQL语句
     * 调用前需通过getTableEngine判断表引擎是否支持事务
     *
     * @param array $arraySql
     * @return Boolean
     */
    public function execTransaction($arraySql)
    {
        $retval = 1;
        $this->beginTransaction();
        foreach ($arraySql as $strSql) {
            if ($this->execSql($strSql) == 0) $retval = 0;
        }
        if ($retval == 0) {
            $this->rollback();
            return false;
        } else {
            $this->commit();
            return true;
        }
    }
 
    /**
     * checkFields 检查指定字段是否在指定数据表中存在
     *
     * @param String $table
     * @param array $arrayField
     */
    private function checkFields($table, $arrayFields)
    {
        $fields = $this->getFields($table);
        foreach ($arrayFields as $key => $value) {
            if (!in_array($key, $fields)) {
                $this->outputError("Unknown column `$key` in field list.");
            }
        }
    }
    
    /**
     * getFields 获取指定数据表中的全部字段名
     *
     * @param String $table 表名
     * @return array
     */
    private function getFields($table)
    {
        $fields = array();
        $recordset = $this->dbh->query("SHOW COLUMNS FROM $table");
        $this->getPDOError();
        $recordset->setFetchMode(PDO::FETCH_ASSOC);
        $result = $recordset->fetchAll();
        foreach ($result as $rows) {
            $fields[] = $rows[&#39;Field&#39;];
        }
        return $fields;
    }
    
    /**
     * getPDOError 捕获PDO错误信息
     */
    private function getPDOError()
    {
        if ($this->dbh->errorCode() != &#39;00000&#39;) {
            $arrayError = $this->dbh->errorInfo();
            $this->outputError($arrayError[2]);
        }
    }
    
    /**
     * debug
     * 
     * @param mixed $debuginfo
     */
    private function debug($debuginfo)
    {
        var_dump($debuginfo);
        exit();
    }
    
    /**
     * 输出错误信息
     * 
     * @param String $strErrMsg
     */
    private function outputError($strErrMsg)
    {
        throw new Exception(&#39;MySQL Error: &#39;.$strErrMsg);
    }
    
    /**
     * destruct 关闭数据库连接
     */
    public function destruct()
    {
        $this->dbh = null;
    }
   /**
    *PDO执行sql语句,返回改变的条数
    *如需调试可选用execSql($sql,true)
    */
    public function exec($sql=&#39;&#39;){
        return $this->dbh->exec($sql);
    }
}
?>

PHP PDO detailed description

PDO class

PDO::beginTransaction — Start a transaction

PDO::commit — Commit a transaction

PDO::__construct — Create a PDO instance that represents a database connection

PDO:: errorCode — Get the SQLSTATE related to the last operation of the database handle

PDO::errorInfo — Return the error information of the last database operation

PDO::exec — Execute a SQL statement and return the received Number of rows affected

PDO::getAttribute — Retrieve attributes of a database connection

PDO::getAvailableDrivers — Return an array of available drivers

PDO::inTransaction — Check whether it is within a transaction

PDO::lastInsertId — Returns the ID or sequence value of the last inserted row

PDO::prepare — Prepares the SQL statement to be executed and returns a PDOStatement object

PDO::query — Execute a SQL statement and return a PDOStatement object, which can be understood as a result set

PDO::quote — Add quotation marks to the string in the SQL statement.

PDO::rollBack — roll back a transaction

PDO::setAttribute — set attributes

PDOStatement class

PDOStatement: :bindColumn — Bind a column to a PHP variable

PDOStatement::bindParam — Bind a parameter to the specified variable name

PDOStatement::bindValue — Bind a value to a parameter

PDOStatement::closeCursor — Close the cursor so that the statement can be executed again.

PDOStatement::columnCount — Returns the number of columns in the result set

PDOStatement::debugDumpParams — Prints a SQL preprocessing command

PDOStatement::errorCode — Gets the last statement SQLSTATE related to handle operations

PDOStatement::errorInfo — Get extended error information related to the last statement handle operation

PDOStatement::execute —Execute a prepared statement

PDOStatement::fetch — Gets the next row from the result set

PDOStatement::fetchAll — Returns an array containing all the rows in the result set

PDOStatement::fetchColumn — Returns the next row from the result set individually of a column.

PDOStatement::fetchObject — Gets the next row and returns it as an object.

PDOStatement::getAttribute — Retrieve a statement attribute

PDOStatement::getColumnMeta — Return metadata for a column in a result set

PDOStatement::nextRowset — In a multi-rowset statement Advance to the next rowset in the handle

PDOStatement::rowCount — Returns the number of rows affected by the previous SQL statement

PDOStatement::setAttribute — Sets a statement attribute

PDOStatement::setFetchMode — Set the default fetch mode for statements.

The above is the detailed content of PDO in PHP. For more information, please follow other related articles on the PHP Chinese website!