Detailed explanation of usage of php pdo function library-PHP Tutorial-php.cn

pdo->begintransaction() — Mark the starting point of rollback
pdo->commit() — Mark the end point of rollback and execute sql
pdo->__construct() — Establish a pdo link database Example of
pdo->errorcode() — Get error code
pdo->errorinfo() — Get 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 driver name
pdo->lastinsertid() — Get the primary key of the last piece of data written Value
pdo->prepare() — Generate a "query object"
pdo->query() — Process a sql statement and return a "pdostatement"
pdo->quote() — For a certain sql Add quotes to the string
pdo->rollback() — perform rollback
pdo->setattribute() — set attributes for a "database connection object"

Copy code

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

Copy code

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();
}

Copy code

Many web applications will be optimized by using persistent connections to the database. Persistent connections are 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 a persistent connection.

Detailed explanation 2) Transactions in pdo The three methods pdo->begintransaction(), pdo->commit(), and 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();
}
? >

Copy code

Now that the connection has been established through pdo, you must understand how pdo manages transactions before deploying the query. If you have never encountered transaction processing before, (now a brief introduction. :) They provide 4 main features: atomicity, consistency, isolation and durability (acid). In layman's terms, all the work in a transaction is committed when it is committed, even if it is It is executed in stages and must be applied safely to the database without being interfered by other connections. The transaction work can also be easily canceled automatically when an error occurs in the 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 running 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 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

// Modify the default error display level
$dbh->setattribute(pdo::attr_errmode, pdo::errmode_warning);
?>

Copy code

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 sql char, varchar type in sql pdo::param_lob Represents a large object type in SQL pdo::param_stmt Represents a recordset type in SQL, which is not supported yet. 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 Return each row of results as an object pdo::fetch_assoc Only the result set of the query with the key value as the subscript is returned. Only one data with the same name is returned. pdo::fetch_named Only the result set of the query with the key value as the subscript is returned. Data with the same name is returned in the form of an array. pdo::fetch_num Returns only result sets for queries with numbers as subscripts pdo::fetch_both Returns the result set of queries with key values and numbers as subscripts at the same time pdo::fetch_obj Return the result set as an object pdo::fetch_bound Assign the value bound to pdostatement::bindparam() and pdostatement::bindcolumn() as a variable name and return it pdo::fetch_column Indicates 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 merging data into an existing class for return pdo::fetch_func pdo::fetch_group pdo::fetch_unique pdo::fetch_key_pair Return the result set in the form of a table below the first key value and a table below the following numbers. pdo::fetch_classtype pdo::fetch_serialize Indicates merging data into an existing class and serializing 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 commissions and start executing pdo::attr_prefetch Set the data size that the application obtains in advance. Not all databases support it. pdo::attr_timeout Set the value of database connection timeout pdo::attr_errmode Set error handling 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 pdo driver version of the pdo connection pdo::attr_server_info Read-only attribute, indicating the meta information of the server to which pdo is connected pdo::attr_connection_status pdo::attr_case Operate the column form through the contents in pdo::case_* pdo::attr_cursor_name Get or set the name of the pointer pdo::attr_cursor Set the type of pointer, pdo now supports pdo::cursor_fwonly and pdo::cursor_fwonly pdo::attr_driver_name Returns the name of the pdo driver used pdo::attr_oracle_nulls Convert the returned empty string to sql's 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 in place of field names. pdo::attr_fetch_table_names In the returned result set, use custom table names in place 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 No error message is reported when an error occurs, which is the default value. pdo::errmode_warning Send 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 for reply columns pdo::case_lower Force column names to be lowercase pdo::case_upper Force column names to be 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 using pointer function pdo::fetch_ori_prior Get the previous row of data in the result set. It is only valid when there is a pointer function. pdo::fetch_ori_first Get the first row of data in the result set, only valid when there is a pointer function pdo::fetch_ori_last Get the last row of data in the result set, only valid when using pointer function pdo::fetch_ori_abs Get a certain row of data in the result set, only valid when there is a pointer function 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_fwonly Create a backward-only pointer operation object pdo::cursor_scroll Create a pointer operation object and pass the contents 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 subsequently triggered 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 subsequently triggered to fetch a result from a resultset. pdo::param_evt_normalize event triggered during bound parameter registration allowing the driver to normalize the parameter name.