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.
Attribute list:
PDO::PARAM_BOOLhttp://www.bkjia.com/PHPjc/1104655.html
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
