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
PHP PDO Errors and Error Handling
PDO provides three different error handling modes to meet different styles of applications Development: PDO::ERRMODE_SILENTThis 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.PHP PDO uses
to connect to MySQL
<?php
$type = 'mysql';
$hostname = 'localhost';
$dbname = 'test';
$username = 'root';
$password = 'root';
try {
$dsn = sprintf('%s:dbname=%s;host=%s', $type, $dbname, $dbname);
//初始化一个PDO对象
$pdo = new PDO($dsn, $username, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION //开启异常模式
]);
} catch (PDOException $e) {
die ("Database error: " . $e->getMessage());
}
?>$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);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"
}
}/* 通过数组值向预处理语句传递值 */ $sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'; $sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]); $sth->execute([':calories' => 150, ':colour' => 'red']); $red = $sth->fetchAll(); $sth->execute([':calories' => 175, ':colour' => 'yellow']); $yellow = $sth->fetchAll();
<?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 = '';
protected $dsn;
protected $dbh;
/**
* 构造
*
* @return DAOPDO
*/
private function __construct($dbHost, $dbUser, $dbPasswd, $dbName, $dbCharset)
{
try {
$this->dsn = 'mysql:host='.$dbHost.';dbname='.$dbName;
$this->dbh = new PDO($this->dsn, $dbUser, $dbPasswd);
$this->dbh->exec('SET character_set_connection='.$dbCharset.', character_set_results='.$dbCharset.', character_set_client=binary');
} 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 = 'All', $debug = false)
{
if ($debug === true) $this->debug($strSql);
$recordset = $this->dbh->query($strSql);
$this->getPDOError();
if ($recordset) {
$recordset->setFetchMode(PDO::FETCH_ASSOC);
if ($queryMode == 'All') {
$result = $recordset->fetchAll();
} elseif ($queryMode == 'Row') {
$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 = '', $debug = false)
{
$this->checkFields($table, $arrayDataValue);
if ($where) {
$strSql = '';
foreach ($arrayDataValue as $key => $value) {
$strSql .= ", `$key`='$value'";
}
$strSql = substr($strSql, 1);
$strSql = "UPDATE `$table` SET $strSql WHERE $where";
} else {
$strSql = "REPLACE INTO `$table` (`".implode('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
}
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('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
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('`,`', array_keys($arrayDataValue))."`) VALUES ('".implode("','", $arrayDataValue)."')";
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 = '', $debug = false)
{
if ($where == '') {
$this->outputError("'WHERE' 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 = '', $debug = false)
{
$strSql = "SELECT MAX(".$field_name.") AS MAX_VALUE FROM $table";
if ($where != '') $strSql .= " WHERE $where";
if ($debug === true) $this->debug($strSql);
$arrTemp = $this->query($strSql, 'Row');
$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 = '', $debug = false)
{
$strSql = "SELECT COUNT($field_name) AS NUM FROM $table";
if ($where != '') $strSql .= " WHERE $where";
if ($debug === true) $this->debug($strSql);
$arrTemp = $this->query($strSql, 'Row');
return $arrTemp['NUM'];
}
/**
* 获取表引擎
*
* @param String $dbName 库名
* @param String $tableName 表名
* @param Boolean $debug
* @return String
*/
public function getTableEngine($dbName, $tableName)
{
$strSql = "SHOW TABLE STATUS FROM $dbName WHERE Name='".$tableName."'";
$arrayTableInfo = $this->query($strSql);
$this->getPDOError();
return $arrayTableInfo[0]['Engine'];
}
//预处理执行
public function prepareSql($sql=''){
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['Field'];
}
return $fields;
}
/**
* getPDOError 捕获PDO错误信息
*/
private function getPDOError()
{
if ($this->dbh->errorCode() != '00000') {
$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('MySQL Error: '.$strErrMsg);
}
/**
* destruct 关闭数据库连接
*/
public function destruct()
{
$this->dbh = null;
}
/**
*PDO执行sql语句,返回改变的条数
*如需调试可选用execSql($sql,true)
*/
public function exec($sql=''){
return $this->dbh->exec($sql);
}
}
?>PHP PDO detailed description
PDO class
PDO::beginTransaction — Start a transactionPDO::commit — Commit a transactionPDO::__construct — Create a PDO instance that represents a database connectionPDO:: 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 affectedPDO::getAttribute — Retrieve attributes of a database connectionPDO::getAvailableDrivers — Return an array of available driversPDO::inTransaction — Check whether it is within a transactionPDO::lastInsertId — Returns the ID or sequence value of the last inserted rowPDO::prepare — Prepares the SQL statement to be executed and returns a PDOStatement objectPDO::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 attributesPDOStatement class
PDOStatement: :bindColumn — Bind a column to a PHP variablePDOStatement::bindParam — Bind a parameter to the specified variable namePDOStatement::bindValue — Bind a value to a parameterPDOStatement::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 operationsPDOStatement::errorInfo — Get extended error information related to the last statement handle operationPDOStatement::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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
