PHP PDO function library detailed explanation, pdo function library detailed explanation

Detailed explanation of PHP PDO function library, detailed explanation of pdo function library

PDO is a "database access abstraction layer" that unifies 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() — Mark the starting point of rollback
PDO->commit() — Mark the end point of rollback and execute SQL
PDO->rollBack() — Perform rollback
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 the 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 quotes to a string in a SQL
PDO->setAttribute() — Set properties 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 are 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 sets the connection type to persistent connection.

Detailed explanation 2) Transactions in PDO
PDO->beginTransaction(), PDO->commit(), PDO->rollBack() are used together when the rollback function is supported. . 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 a connection through PDO, you must understand how PDO manages transactions before deploying queries. If you've never encountered transactions before, (here's a brief introduction:) they provide 4 main properties: Atomicity, Consistency, Isolation and Durability (ACID) In layman's terms To put it simply, 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 interfered by other connections. Transaction work can also be easily automatically canceled if an error occurs with a request.

The typical use of transactions is to "save" batch changes and then execute them immediately. This will have the benefit of completely improving update efficiency. In other words, transactions can make your scripts faster and potentially more robust (you still need to use them correctly to realize this benefit).

Unfortunately, not every database supports transactions, so PDO needs to be run in what is considered "autocommit" mode when the connection is established. Autocommit mode means that every query you execute has its own implicit transaction processing, whether the database supports transactions or there is no transaction because the database does not support it. 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 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

3. PDOException

PDO provides 3 different error handling strategies.
1. PDO::ERRMODE_SILENT
This is the default mode. PDO will set simple error codes on the statement and database objects. You can use the PDO->errorCode() and PDO->errorInfo() methods to check for errors; if the error is caused by a call to the statement object, You can use the PDOStatement->errorCode() or PDOStatement->errorInfo() method on that object to obtain the error information. And if the error is caused when calling the database object, you should call those two methods on the database object.
2. PDO::ERRMODE_WARNING
In addition to setting the error code, PDO will issue a traditional E_WARNING message. This setting is useful when debugging and debugging if you just want to see what went wrong without interrupting the flow of the program.
3. PDO::ERRMODE_EXCEPTION
As an attachment to set the error code, PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is also very useful when debugging, because it will effectively "blow up" the error point in the script, very quickly pointing to a possible error area in your code. (Remember: if an exception causes the script to abort, the transaction will automatically roll back.)
Exception mode is also very useful because you can use a clearer structure than the traditional PHP-style error handling structure. Errors use less code and nesting than using quiet mode, and can more explicitly check the return value of each database access.
For more information about exceptions in PHP, please see the Exceptions chapter
PDO uses error code strings based on SQL-92 SQLSTATE; specific PDO drivers should map their own codenames to the appropriate SQLSTATE codenames. The PDO->errorCode() method only returns a single SQLSTATE code. If you need more targeted information about an error, PDO also provides a PDO->errorInfo() method, which can return an error message containing the SQLSTATE code, the error code of the specific database driver, and the error description of the specific database driver. String.

// Modify the default error display level
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
?>

Attribute list:

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 in SQL, VARCHAR type
PDO::PARAM_LOB
represents a large object type in SQL
PDO::PARAM_STMT
represents a recordset type in SQL , not yet supported
PDO::PARAM_INPUT_OUTPUT
Specifies that the parameter is an INOUT parameter for a stored procedure. You must bitwise-OR this value with an explicit PDO::PARAM_* data type.
PDO ::FETCH_LAZY
Returns 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 simultaneously
PDO::FETCH_OBJ
Returns the result set in the form of object
PDO::FETCH_BOUND
Converts PDOStatement: The values bound to :bindParam() and PDOStatement::bindColumn() are returned as variable names after assignment
PDO::FETCH_COLUMN
, indicating 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
Returns the result set in the form of a table with the first key value and a table with the following numbers
PDO::FETCH_CLASSTYPE
PDO::FETCH_SERIALIZE
means merging the data into An existing class and serialized return
PDO::FETCH_PROPS_LATE
Available since PHP 5.2.0
PDO::ATTR_AUTOCOMMIT
When set to true, PDO will automatically try to stop accepting delegates. 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 client of the PDO connection End 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
Passed in PDO::CASE_* The content operates on the column form
PDO::ATTR_CURSOR_NAME
Gets or sets 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
Converts 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
Use custom table names in place of field names in the returned result set.
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
Reply the default display format of the 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
Gets the previous row of data in the result set. It is only valid when the pointer function is available.
PDO::FETCH_ORI_FIRST
Gets the first row of data in the result set. It is only valid when the pointer function is available.
PDO::FETCH_ORI_LAST
Gets the last row of data in the result set, which is only valid when the pointer function is available
PDO::FETCH_ORI_ABS
Gets a certain 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, which 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. ::PARAM_EVT_FETCH_PRE
Event triggered priority to fetching a result from a resultset.
PDO::PARAM_EVT_FETCH_POST
Event triggered subsequent to fetching a result from a resultset. ZE
Event triggered during bound parameter registration allowing the driver to normalize the parameter name.

http://www.bkjia.com/PHPjc/1104655.html