Detailed explanation of PHP PDO function library-PHP开发-php.cn

Currently, there is a long way to go to implement the "database abstraction layer". Using a "database access abstraction layer" like PDO is a good choice.

PDO contains three predefined classes

PDO contains three predefined classes, which are PDO, PDOStatement and PDOException.

1. PDO

PDO->beginTransaction() - Indicates the starting point of rollback
PDO->commit() - Indicates the end point of rollback and executes SQL
PDO->__construct() — Create an instance of PDO link database
PDO->errorCode() — Get the error code
PDO->errorInfo() — Get the error information
PDO- >exec() — Process a SQL statement and return the number of entries affected
PDO->getAttribute() — Get the attributes of a "database connection object"
PDO->getAvailableDrivers() — Get Valid PDO drive name
PDO->lastInsertId() — Get the primary key value of the last piece of data written
PDO->prepare() — Generate a "query object"
PDO-> ;query() — Process a SQL statement and return a "PDOStatement"
PDO->quote() — Add quotation marks to a string in a SQL
PDO->rollBack() — Execute return Roll
PDO->setAttribute() — Set attributes for a "database connection object"

2. PDOStatement

PDOStatement->bindColumn() — Bind a column to a PHP variable
PDOStatement->bindParam() — Binds a parameter to the specified variable name
PDOStatement->bindValue() — Binds a value to a parameter
PDOStatement->closeCursor() — Closes the cursor, enabling the statement to be executed again.
PDOStatement->columnCount() — Returns the number of columns in the result set
PDOStatement->errorCode() — Fetch the SQLSTATE associated with the last operation on the statement handle
PDOStatement->errorInfo() — Fetch extended error information associated with the last operation on the statement handle
PDOStatement->execute() — Executes a prepared statement
PDOStatement-> fetch() — Fetches the next row from a result set
PDOStatement->fetchAll() — Returns an array containing all of the result set rows
PDOStatement->fetchColumn() — Returns a single column from the next row of a result set
PDOStatement->fetchObject() — Fetches the next row and returns it as an object.
PDOStatement->getAttribute() — Retrieve a statement attribute
PDOStatement-> getColumnMeta() — Returns metadata for a column in a result set
PDOStatement->nextRowset() — Advances to the next rowset in a multi-rowset statement handle
PDOStatement->rowCount() — Returns the number of rows affected by the last SQL statement
PDOStatement->setAttribute() — Set a statement attribute
PDOStatement->setFetchMode() — Set the default fetch mode for this statement

PDO is A "database access abstraction layer" is used to unify the access interfaces of various databases. Compared with the function libraries of mysql and mysqli, PDO makes cross-database use more friendly; compared with ADODB and MDB2, PDO is more efficient. At present, there is a long way to go to implement the "database abstraction layer". Using a "database access abstraction layer" such as PDO is a good choice.

PDO contains three predefined classes

PDO contains three predefined classes, which are PDO, PDOStatement and PDOException.

1. PDO

PDO->beginTransaction() - Indicates the starting point of rollback
PDO->commit() - Indicates the end point of rollback and executes SQL
PDO->rollBack() — Perform rollback
PDO->__construct() — Create an instance of PDO link database
PDO->errorCode() — Get the error code
PDO-> ;errorInfo() — Get error information
PDO->exec() — Process a SQL statement and return the number of entries affected
PDO->getAttribute() — Get a "database connection object" Properties
PDO->getAvailableDrivers() — Get the valid PDO driver name
PDO->lastInsertId() — Get the primary key value of the last piece of data written
PDO->prepare( ) — Generate a "query object"
PDO->query() — Process a SQL statement and return a "PDOStatement"
PDO->quote() — Add to a string in a SQL Quotation marks
PDO->setAttribute() — Set attributes for a "database connection object"

Detailed explanation 1) Database connection in PDO
$dsn = 'mysql:dbname=ent;host =127.0.0.1′;
$user = 'root';
$password = '123456';
try {
$dbh = new PDO($dsn, $user, $password, array (PDO::ATTR_PERSISTENT => true));
$dbh->query('set names utf8;');
foreach ($dbh->query('SELECT * from tpm_juese') as $row) {
print_r($row);
}
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

Many web applications will be optimized by using persistent connections to the database. The persistent connection is not closed at the end of the script,
Instead it is cached and reused when another script requests a connection with the same ID.
The cache of persistent connections allows you to avoid the resource consumption of deploying a new connection every time the script needs to talk to the database, making your web application faster.
The array(PDO::ATTR_PERSISTENT => true) in the above example is to set the connection type to a persistent connection.

Detailed explanation 2) Transactions in PDO
PDO->beginTransaction(), PDO->commit(), PDO->rollBack() are the three methods when the rollback function is supported used together. The PDO->beginTransaction() method marks the starting point, the PDO->commit() method marks the rollback end point and executes SQL, and PDO->rollBack() performs rollback.
try {
$dbh = new PDO('mysql:host=localhost;dbname=test', 'root', ”);
$dbh->query( 'set names utf8;');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dbh->beginTransaction();
$dbh- >exec("INSERT INTO `test`.`table` (`name` ,`age`)VALUES ('mick', 22);");
$dbh->exec("INSERT INTO `test `.`table` (`name` ,`age`)VALUES ('lily', 29);”);
$dbh->exec(”INSERT INTO `test`.`table` (`name` ,`age`)VALUES ('susan', 21);”);
$dbh->commit();

} catch (Exception $e) {
$dbh-> ;rollBack();
echo "Failed: " . $e->getMessage();
}
?>
Now that you have established the connection through PDO, before deploying the query you You must understand how PDO manages transactions. If you have never encountered transaction processing before, (now a brief introduction:) they provide 4 main features: Atomicity, Consistency, Independence and Durability (Atomicity) , Consistency, Isolation and Durability (ACID) In layman's terms, when all work in a transaction is submitted, even if it is executed in stages, it must be safely applied to the database and not be interfered with by other connections. It can be easily automatically canceled when an error occurs in the request. The typical use of transactions is to "save" batch changes and then execute them immediately. In other words, this will completely improve the efficiency of updates. , transactions can make your scripts faster and potentially more robust (to achieve this advantage you still need to use them correctly)

Unfortunately, not every database supports transactions, so PDO is required. Running in what is considered "autocommit" mode when establishing a connection Autocommit mode means that every query you execute has its own implicit transaction processing, whether the database supports transactions or does not exist because the database does not. affairs. If you need a transaction, you must create one using the PDO->beginTransaction() method. If the underlying driver does not support transactions, a PDOException will be thrown (regardless of your exception handling settings, since this is always a serious error condition). Within a transaction, you can end it using PDO->commit() or PDO->rollBack(), depending on whether the code in the transaction ran successfully.

When the script ends or a connection is to be closed, if you still have an unfinished transaction, PDO will automatically roll it back. This is a safe solution in case the script terminates unexpectedly - if you don't explicitly commit the transaction, it will assume that something went wrong and perform a rollback for the safety of your data.

2. PDOStatement

PDOStatement->bindColumn() — Bind a column to a PHP variable
PDOStatement->bindParam() — Binds a parameter to the specified variable name
PDOStatement->bindValue() — Binds a value to a parameter
PDOStatement->closeCursor() — Closes the cursor, enabling the statement to be executed again.
PDOStatement->columnCount() — Returns the number of columns in the result set
PDOStatement->errorCode() — Fetch the SQLSTATE associated with the last operation on the statement handle
PDOStatement->errorInfo() — Fetch extended error information associated with the last operation on the statement handle
PDOStatement->execute() — Executes a prepared statement
PDOStatement->fetch() — Fetches the next row from a result set
PDOStatement->fetchAll() — Returns an array containing all of the result set rows
PDOStatement->fetchColumn() — Returns a single column from the next row of a result set
PDOStatement->fetchObject() — Fetches the next row and returns it as an object.
PDOStatement->getAttribute() — Retrieve a statement attribute
PDOStatement->getColumnMeta() — Returns metadata for a column in a result set
PDOStatement->nextRowset() — Advances to the next rowset in a multi-rowset statement handle
PDOStatement->rowCount() — Returns the number of rows affected by the last SQL statement
PDOStatement->setAttribute() — Set a statement attribute
PDOStatement->setFetchMode() — Set the default fetch mode for this statement

三、PDOException

PDO 提供了3中不同的错误处理策略。
1. PDO::ERRMODE_SILENT
这是默认使用的模式。PDO会在statement和database对象上设定简单的错误代号，你可以使用PDO->errorCode() 和 PDO->errorInfo() 方法检查错误；如果错误是在对statement对象进行调用时导致的，你就可以在那个对象上使用 PDOStatement->errorCode() 或 PDOStatement->errorInfo() 方法取得错误信息。而如果错误是在对database对象调用时导致的，你就应该在这个database对象上调用那两个方法。
2. PDO::ERRMODE_WARNING
作为设置错误代号的附加，PDO将会发出一个传统的E_WARNING信息。这种设置在除错和调试时是很有用的，如果你只是想看看发生了什么问题而不想中断程序的流程的话。
3. PDO::ERRMODE_EXCEPTION
作为设置错误代号的附件，PDO会抛出一个PDOException异常并设置它的属性来反映错误代号和错误信息。这中设置在除错时也是很有用的，因为他会有效的“放大（blow up）”脚本中的出错点，非常快速的指向一个你代码中可能出错区域。（记住：如果异常导致脚本中断，事务处理回自动回滚。）
异常模式也是非常有用的，因为你可以使用比以前那种使用传统的PHP风格的错误处理结构更清晰的结构处理错误，比使用安静模式使用更少的代码及嵌套，也能够更加明确地检查每个数据库访问的返回值。
关于PHP中异常的更多信息请看Exceptions章节
PDO 使用基于SQL-92 SQLSTATE 的错误代号字符串；特定的PDO驱动应当将自己本身的代号对应到适当的SQLSTATE代号上。PDO->errorCode() 方法只返回单一的SQLSTATE代号。如果你需要关于一个错误的更加有针对性的信息，PDO也提供了一个PDO->errorInfo()方法，它可以返回一个包含了SQLSTATE代号，特定数据库驱动的错误代号和特定数据库驱动的错误说明字符串。

// 修改默认的错误显示级别
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
?>

属性列表：

PDO::PARAM_BOOL
Represents a Boolean type
PDO::PARAM_NULL
Represents a NULL type in SQL
PDO::PARAM_INT
Represents an INTEGER type in SQL
PDO::PARAM_STR
represents a SQL CHAR, VARCHAR type in SQL
PDO::PARAM_LOB
represents a large object type in SQL
PDO::PARAM_STMT
represents a SQL The recordset type is not supported yet
PDO::PARAM_INPUT_OUTPUT
Specifies that the parameter is an INOUT parameter for a stored procedure. Youmust bitwise-OR this value with an explicit PDO::PARAM_* data type.
PDO::FETCH_LAZY
Return each row of results as an object
PDO::FETCH_ASSOC
Only returns the result set of the query with the key value as the subscript. Data with the same name only returns one
PDO ::FETCH_NAMED
Only returns the result set of the query with the key value as the subscript, and the data with the same name is returned in the form of an array
PDO::FETCH_NUM
Only returns the result set of the query with the number as the subscript
PDO::FETCH_BOTH
Returns the result set of the query with key value and number as subscript at the same time
PDO::FETCH_OBJ
Returns the result set in the form of an object
PDO::FETCH_BOUND
will The value bound to PDOStatement::bindParam() and PDOStatement::bindColumn() is returned as a variable name after assignment
PDO::FETCH_COLUMN
means that only a certain column in the result set is returned
PDO::FETCH_CLASS
Indicates that the result set is returned in the form of a class
PDO::FETCH_INTO
Indicates that the data is merged into an existing class and returned
PDO::FETCH_FUNC
PDO::FETCH_GROUP
PDO ::FETCH_UNIQUE
PDO::FETCH_KEY_PAIR
Return the result set in the form of the first key value and the following numbers
PDO::FETCH_CLASSTYPE
PDO::FETCH_SERIALIZE
means that the data will be Merge into an existing class and serialize it back
PDO::FETCH_PROPS_LATE
Available since PHP 5.2.0
PDO::ATTR_AUTOCOMMIT
When set to true, PDO will automatically try to stop accepting Delegate, start execution
PDO::ATTR_PREFETCH
Set the data size obtained by the application in advance, not all databases support it
PDO::ATTR_TIMEOUT
Set the value of the connection database timeout
PDO ::ATTR_ERRMODE
Set the Error processing mode
PDO::ATTR_SERVER_VERSION
Read-only attribute, indicating the server-side database version of the PDO connection
PDO::ATTR_CLIENT_VERSION
Read-only attribute, indicating the PDO connection Client PDO driver version
PDO::ATTR_SERVER_INFO
Read-only attribute, indicating the meta information of the server connected to PDO
PDO::ATTR_CONNECTION_STATUS
PDO::ATTR_CASE
Through PDO::CASE_ The contents in * operate on the form of columns
PDO::ATTR_CURSOR_NAME
Get or set the name of the pointer
PDO::ATTR_CURSOR
Set the type of the pointer, PDO now supports PDO::CURSOR_FWDONLY and PDO::CURSOR_FWDONLY
PDO::ATTR_DRIVER_NAME
Returns the name of the PDO driver used
PDO::ATTR_ORACLE_NULLS
Convert the returned empty string to SQL NULL
PDO::ATTR_PERSISTENT
Get an existing connection
PDO::ATTR_STATEMENT_CLASS
PDO::ATTR_FETCH_CATALOG_NAMES
In the returned result set, use custom catalog names instead of field names.
PDO::ATTR_FETCH_TABLE_NAMES
In the returned result set, use custom table names instead of field names.
PDO::ATTR_STRINGIFY_FETCHES
PDO::ATTR_MAX_COLUMN_LEN
PDO::ATTR_DEFAULT_FETCH_MODE
Available since PHP 5.2.0
PDO::ATTR_EMULATE_PREPARES
Available since PHP 5.1.3.
PDO::ERRMODE_SILENT
Does not report any error message when an error occurs, which is the default value
PDO::ERRMODE_WARNING
Sends a php E_WARNING message when an error occurs
PDO::ERRMODE_EXCEPTION
Throws a PDOException when an error occurs
PDO::CASE_NATURAL
Default display format of the reply column
PDO::CASE_LOWER
Force the column name to be lowercase
PDO::CASE_UPPER
Force the column The name is capitalized
PDO::NULL_NATURAL
PDO::NULL_EMPTY_STRING
PDO::NULL_TO_STRING
PDO::FETCH_ORI_NEXT
Get the next row of data in the result set, only valid when there is a pointer function
PDO::FETCH_ORI_PRIOR
Get the previous row of data in the result set, which is only valid when there is a pointer function.
PDO::FETCH_ORI_FIRST
Get the first row of data in the result set, which is only valid when there is a pointer function.
PDO::FETCH_ORI_LAST
Get the last row of data in the result set, which is only valid when the pointer function is available
PDO::FETCH_ORI_ABS
Get the last row of data in the result set, which is only valid when the pointer function is available
PDO::FETCH_ORI_REL
Get the data of a row after the current row in the result set. It is only valid when there is a pointer function.
PDO::CURSOR_FWDONLY
Create a backward-only pointer operation object
PDO::CURSOR_SCROLL
Create a pointer operation object and pass the content in PDO::FETCH_ORI_* to control the result set
PDO::ERR_NONE (string)
Set the error message when there is no error
PDO::PARAM_EVT_ALLOC
Allocation event
PDO::PARAM_EVT_FREE
Deallocation event
PDO::PARAM_EVT_EXEC_PRE
Event triggered prior to execution of a prepared statement.
PDO::PARAM_EVT_EXEC_POST
Event triggered subsequent to execution of a prepared statement.
PDO::PARAM_EVT_FETCH_PRE
Event triggered prior to fetching a result from a resultset.
PDO::PARAM_EVT_FETCH_POST
Event triggered subsequent to fetching a result from a resultset.
PDO::PARAM_EVT_NORMALIZE
Event triggered during bound parameter registration allowing the driver to normalize the parameter name.