Explanation of the PDO function library in PHP

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

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

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

PDO contains three predefined classes.

PDO contains three predefined classes: PDO, PDOStatement, and PDOException.

I. PDO

PDO-> beginTransaction ()-indicates the start point of rollback
PDO-> commit ()-indicates the rollback end point and executes the SQL
PDO->__ construct ()-create a PDO-Linked database instance
PDO-> errorCode ()-Get error code
PDO-> errorInfo ()-Get error information
PDO-> exec ()-processes an SQL statement and returns the number of affected items
PDO-> getAttribute ()-Get the attribute of a "database connection object"
PDO-> getAvailableDrivers ()-Get a valid PDO drive name
PDO-> lastInsertId ()-Get the primary key value of the last data entry written
PDO-> prepare ()-generate a "query object"
PDO-> query ()-processes an SQL statement and returns a "PDOStatement"
PDO-> quote ()-add quotation marks for strings in an SQL statement
PDO-> rollBack ()-Execute rollBack
PDO-> setAttribute ()-set attributes for a "database connection object"

Ii. 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", which is used to unify access interfaces of various databasesFunctionCompared with databases, PDO makes cross-database use more friendly; compared with ADODB and MDB2, PDO is more efficient. At present, there is a long way to implement the "database abstraction layer". Using the "database access abstraction layer" such as PDO is a good choice.

PDO contains three predefined classes.

PDO contains three predefined classes: PDO, PDOStatement, and PDOException.

I. PDO

PDO-> beginTransaction ()-indicates the start point of rollback
PDO-> commit ()-indicates the rollback end point and executes the SQL
PDO-> rollBack ()-Execute rollBack
PDO->__ construct ()-create a PDO-Linked database instance
PDO-> errorCode ()-Get error code
PDO-> errorInfo ()-Get error information
PDO-> exec ()-processes an SQL statement and returns the number of affected items
PDO-> getAttribute ()-Get the attribute of a "database connection object"
PDO-> getAvailableDrivers ()-Get a valid PDO drive name
PDO-> lastInsertId ()-Get the primary key value of the last data entry written
PDO-> prepare ()-generate a "query object"
PDO-> query ()-processes an SQL statement and returns a "PDOStatement"
PDO-> quote ()-add quotation marks for strings in an SQL statement
PDO-> setAttribute ()-set attributes for a "database connection object"

Details1) database connection in PDO
$ Dsn = 'mysql: dbname = ent; host = 127.0.0.1 ′;
$ User = 'root ';
$ Password = '000000 ′;
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 databases. Persistent connections are not closed when the script ends,
On the contrary, it will be cached and reused when another script requests a connection through the same identity.
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 instance sets the connection type to persistent connection.

Details2) Transactions in PDO
PDO-> beginTransaction (), PDO-> commit (), PDO-> rollBack () are used together when the rollBack function is supported. The PDO-> beginTransaction () method indicates the start point, the PDO-> commit () method indicates the end point of the rollBack, and executes the SQL statement. The PDO-> rollBack () method performs the 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 you have established a connection through PDO. before deploying a query, you must understand how PDO manages transactions. If you have never encountered transaction processing before (for a brief introduction), they provide four main features: Atomicity, Consistency, independence, and durability (Atomicity, Consistency, isolation and Durability, ACID) in general, when all the work in a transaction is committed, even if it is executed in stages, it must be securely applied to the database, it is not affected by other connections. The transaction can also be easily canceled automatically when an error occurs in the request.

A typical use of transactions is to "save" batch changes and execute them immediately. In this way, the update efficiency will be improved thoroughly. In other words, transactions can make your scripts faster and more robust (you still need to use them correctly to achieve this advantage ).

Unfortunately, not every database supports transactions, so PDO needs to run in the "auto commit" mode when establishing a connection. The automatic commit Mode means that each query you execute has its own implicit transaction processing, no matter whether the database supports transactions or because the database does not support transactions. If you need a transaction, you must use the PDO-> beginTransaction () method to create one. If the underlying driver does not support transaction processing, a PDOException will be thrown (it is irrelevant to your Exception Processing settings because it is always a serious error state ). In a transaction, you can end it with PDO-> commit () or PDO-> rollBack (), depending on whether the code in the transaction runs 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 for unexpected termination of the script-if you do not explicitly commit the transaction, it will assume that some errors have occurred for your data security, therefore, rollback is performed.

Ii. 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 three different error handling policies.
1. PDO: ERRMODE_SILENT
This is the default mode. PDO sets simple error codes on statement and database objects. You can use the PDO-> errorCode () and PDO-> errorInfo () methods to check errors; if the error is caused by calling the statement object, you can use the PDOStatement-> errorCode () or PDOStatement-> errorInfo () method on the object to get the error information. If the error is caused by calling the database object, you should call the two methods on the database object.
2. PDO: ERRMODE_WARNING
As an additional error code, PDO sends out a traditional E_WARNING message. This setting is useful in debugging and debugging. If you just want to see what went wrong and don't want to interrupt the program process.
3. PDO: ERRMODE_EXCEPTION
As an attachment for setting the error code, PDO throws a PDOException and sets its attributes to reflect the error code and error information. This setting is also useful in addition to errors, because it will effectively point to an error point in the "enlarge (blow up)" script, very quickly pointing to a possible error area in your code. (Remember: if an exception causes a script interruption, the transaction will be automatically rolled back .)
Exception mode is also very useful, because you can use a structure that is clearer than the traditional PHP-style error handling structure, it uses less code and nesting than quiet mode, and it can also check the return values of each database access more clearly.
For more information about Exceptions in PHP, see the Exceptions section.
PDO uses an error code string based on the SQL-92 SQLSTATE; a specific PDO driver should map its own code to the appropriate SQLSTATE code. The PDO-> errorCode () method returns only a single SQLSTATE code. If you need more targeted information about an error, PDO also provides a PDO-> errorInfo () method, which can return a code containing the SQLSTATE, the error code of the specific database driver and the error description string of the specific database driver.

// 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
Indicates the NULL type in an SQL statement.
PDO: PARAM_INT
Indicates the INTEGER type in an SQL statement.
PDO: PARAM_STR
SQLCHAR and VARCHAR types in an SQL statement
PDO: PARAM_LOB
Represents the largeobject type in an SQL statement.
PDO: PARAM_STMT
Indicates the recordset type in an SQL statement, which is not supported yet.
PDO: PARAM_INPUT_OUTPUT
SpecifiesthattheparameterisanINOUTparameterforastoredprocedure. Youmustbitwise-ORthisvaluewithanexplicitPDO: PARAM _ * daTatype.
PDO: FETCH_LAZY
Returns the result of each row as an object.
PDO: FETCH_ASSOC
Returns only the result set of the query with the key value as the underlying object. Only one data set with the same name is returned.
PDO: FETCH_NAMED
Returns only the result set of the query with the key value as the underlying object. data with the same name is returned in an array.
PDO: FETCH_NUM
Returns only the result set of a query whose number is used as the base object.
PDO: FETCH_BOTH
Returns the result set of the query that uses both the key value and number as the base object.
PDO: FETCH_OBJ
Returns the result set as an object.
PDO: FETCH_BOUND
Return the value bound to PDOStatement: bindParam () and PDOStatement: bindColumn () as the variable name value.
PDO: FETCH_COLUMN
Only one column in The result set is returned.
PDO: FETCH_CLASS
Returns the result set in the form of a class.
PDO: FETCH_INTO
Merge 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 the following table with the first key value and the following number
PDO: FETCH_CLASSTYPE
PDO: FETCH_SERIALIZE
Merges data into an existing class and serializes the returned data.
PDO: FETCH_PROPS_LATE
AvailablesincePHP5.2.0
PDO: ATTR_AUTOCOMMIT
When it is set to true, PDO will automatically stop accepting delegation and start execution.
PDO: ATTR_PREFETCH
Set the data size that the application obtains in advance. Not all databases are supported.
PDO: ATTR_TIMEOUT
Set the database connection timeout value
PDO: ATTR_ERRMODE
Set the Error handling mode
PDO: ATTR_SERVER_VERSION
Read-only attribute, indicating the version of the server database connected to PDO
PDO: ATTR_CLIENT_VERSION
Read-only attribute, indicating the client PDO driver version connected to PDO
PDO: ATTR_SERVER_INFO
Read-only attribute, indicating the meta information of the server connected to PDO
PDO: ATTR_CONNECTION_STATUS
PDO: ATTR_CASE
Operate on the column form using the content in PDO: CASE _ *
PDO: ATTR_CURSOR_NAME
Get or set the pointer name
PDO: ATTR_CURSOR
Set the pointer type. 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 NULL string to the SQL NULL String.
PDO: ATTR_PERSISTENT
Obtain an existing connection
PDO: ATTR_STATEMENT_CLASS
PDO: ATTR_FETCH_CATALOG_NAMES
In the returned result set, use the custom directory name instead of the field name.
PDO: ATTR_FETCH_TABLE_NAMES
In the returned result set, use the custom table name instead of the field name.
PDO: ATTR_STRINGIFY_FETCHES
PDO: ATTR_MAX_COLUMN_LEN
PDO: ATTR_DEFAULT_FETCH_MODE
AvailablesincePHP5.2.0
PDO: ATTR_EMULATE_PREPARES
AvailablesincePHP5.1.3.
PDO: ERRMODE_SILENT
No error message is reported when an error occurs. It is the default value.
PDO: ERRMODE_WARNING
When an error occurs, a php E_WARNING message is sent.
PDO: ERRMODE_EXCEPTION
A PDOException is thrown when an error occurs.
PDO: CASE_NATURAL
Default display format of the reply Column
PDO: CASE_LOWER
Lowercase name of mandatory Columns
PDO: CASE_UPPER
Uppercase names of mandatory Columns
PDO: NULL_NATURAL
PDO: NULL_EMPTY_STRING
PDO: NULL_TO_STRING
PDO: FETCH_ORI_NEXT
Obtains the next row of data in the result set, which is only valid when the pointer function is available.
PDO: FETCH_ORI_PRIOR
Obtains the data of the previous row in the result set. It is valid only when the pointer function is available.
PDO: FETCH_ORI_FIRST
Obtain the first row of data in the result set. Valid only when the pointer function is available.
PDO: FETCH_ORI_LAST
Obtain the last row of data in the result set, which is only valid when the pointer function is available.
PDO: FETCH_ORI_ABS
Obtains a row of data in the result set, which is valid only when the pointer function is available.
PDO: FETCH_ORI_REL
Obtains the data of a row after the current row in the result set. It is valid only when the pointer function is available.
PDO: CURSOR_FWDONLY
Create a pointer operation object that can only be backward
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 no error occurs
PDO: PARAM_EVT_ALLOC
Allocationevent
PDO: PARAM_EVT_FREE
Deallocationevent
PDO: PARAM_EVT_EXEC_PRE
Eventtriggeredpriortoexecutionofapreparedstatement.
PDO: PARAM_EVT_EXEC_POST
Eventtriggeredsubsequenttoexecutionofapreparedstatement.
PDO: PARAM_EVT_FETCH_PRE
Eventtriggeredpriortofetchingaresultfromaresultset.
PDO: PARAM_EVT_FETCH_POST
Eventtriggeredsubsequenttofetchingaresultfromaresultset.
PDO: PARAM_EVT_NORMALIZE
Eventtriggeredduringboundparameterregistrationallowingthedrivertonormalizetheparametername.