PHP PDO function library detailed _php skills

For now, implementing the "Database Abstraction Layer" is a great way to go, and using a database access abstraction layer like PDO is a good choice.

The PDO contains three predefined classes

The PDO contains three predefined classes, which are PDO, pdostatement, and Pdoexception respectively.

First, PDO

Pdo->BeginTransaction ()-Indicate rollback start point
Pdo->commit ()-Indicates the rollback end point and executes the SQL
Pdo->__construct ()-Create an instance of a PDO linked database
Pdo->errorcode ()-Get the error code
Pdo->errorinfo ()-Get the wrong information
Pdo->exec ()-Processes an SQL statement and returns the number of entries affected
Pdo->getattribute ()-Get a property of a "Database connection object"
Pdo->getavailabledrivers ()-Get a valid PDO drive name
Pdo->lastinsertid ()-Gets the primary key value of the last piece of data written
Pdo->prepare ()-Generate a "query object"
Pdo->query ()-Processes an SQL statement and returns a "Pdostatement"
Pdo->quote ()-add quotation marks to a string in a SQL
Pdo->rollback ()-Perform a rollback
Pdo->setattribute ()-Set properties for a database connection object

Second, 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, 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
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", the role is to unify the access interface of various databases, compared with MySQL and mysqli function library, PDO makes the use of cross database more affinity; compared with ADODB and MDB2, PDO is more efficient. For now, implementing the "Database Abstraction Layer" is a great way to go, and using a database access abstraction layer like PDO is a good choice.

The PDO contains three predefined classes

The PDO contains three predefined classes, which are PDO, pdostatement, and Pdoexception respectively.

First, PDO

Pdo->BeginTransaction ()-Indicate rollback start point
Pdo->commit ()-Indicates the rollback end point and executes the SQL
Pdo->rollback ()-Perform a rollback
Pdo->__construct ()-Create an instance of a PDO linked database
Pdo->errorcode ()-Get the error code
Pdo->errorinfo ()-Get the wrong information
Pdo->exec ()-Processes an SQL statement and returns the number of entries affected
Pdo->getattribute ()-Get a property of a "Database connection object"
Pdo->getavailabledrivers ()-Get a valid PDO drive name
Pdo->lastinsertid ()-Gets the primary key value of the last piece of data written
Pdo->prepare ()-Generate a "query object"
Pdo->query ()-Processes an SQL statement and returns a "Pdostatement"
Pdo->quote ()-add quotation marks to a string in a SQL
Pdo->setattribute ()-Set properties for a database connection object

Detailed 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 because they use persistent connections to the database. Persistent connections do not close at the end of the script.
Instead, it is cached and recycled when another script requests a connection through the same identity.
A persistent connection cache 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 a persistent connection.

Detailed 2) transactions in the PDO
Pdo->BeginTransaction (), Pdo->commit (), Pdo->rollback () These three methods are used together when the rollback feature is supported. the Pdo-> BeginTransaction () method indicates the starting point, the Pdo->commit () method indicates the rollback end point, and the Sql,pdo->rollback () performs the rollback.
<?php
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 have to figure out how PDO manages the transaction before you deploy the query. If you've never had a transaction before, (now briefly:) They provide 4 main features: Atomicity, consistency, independence, and persistence (atomicity, consistency, isolation and durability,acid) in layman's terms. , when all work in a transaction is committed, even if it is staged in stages, it is guaranteed to be securely applied to the database and not interfered with by other connections. Transactional work can also be easily canceled automatically when an error occurs on a request.

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

Unfortunately, not every database supports transactions, so PDO needs to run when connecting to a mode that is considered "autocommit." Autocommit mode means that every query you perform has its own implicit transaction, regardless of whether the database supports transactions or the database does not support them. 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 is thrown (regardless of your exception handling settings, as this is always a serious error state). In one thing, you can use Pdo->commit () or pdo->rollback () to end it, depending on whether the code runs successfully in the transaction.

When the script ends or a connection is closed, if you have an unhandled transaction, PDO will automatically roll it back. This is a safe scenario for a script to terminate unexpectedly-if you do not explicitly commit the transaction, it will assume that some errors have occurred and, for the sake of the security of your data, the rollback is performed.

Second, 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, 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
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

three, pdoexception

The

PDO provides different error handling policies in 3.
1. Pdo::errmode_silent
This is the mode that is used by default. PDO will set a simple error code on the statement and database objects, and 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 to get the error message on that object. If the error is caused by a call to a database object, you should call the two methods on the database object.
2. Pdo::errmode_warning
As an add-on to setting the error code, PDO will emit a traditional e_warning message. This setting is useful when debugging and debug, if you just want to see what's going on and don't want to interrupt the process.
3. Pdo::errmode_exception
is an attachment that sets the error code, PDO throws a Pdoexception exception and sets its properties to reflect the error code and error message. This setting is also useful in addition to the error, because he will be effective in the "Blow Up" script errors in the point, very quickly point to a possible error area in your code. (Remember: If the exception causes the script to break, the transaction is automatically rolled back.)
Exception mode is also useful because you can use more structured error-handling errors than you used to use traditional PHP-style fault handling structures, use less code and nesting than quiet mode, and check the return value of each database access more explicitly.
For more information about exceptions in PHP, see the Exceptions section
PDO use the error code string based on the SQL-92 SQLSTATE; the specific PDO driver should correspond its own code name to the appropriate SQLSTATE code. The Pdo->errorcode () method returns only a single SQLSTATE code. If you need more targeted information about a bug, PDO also provides a pdo->errorinfo () method that returns a description string containing the SQLSTATE code, a specific database-driven error code, and a specific database-driven error.

<?php
To modify the default error display level
$DBH->setattribute(PDO::attr_errmode, PDO::errmode_warning );
?>

List of properties:

Pdo::Param_bool
Represents a Boolean type
Pdo::Param_null
Represents aSqlIn theNullType
Pdo::Param_int
Represents aSqlIn theINTEGERType
Pdo::Param_str
Represents aSqlIn theSql CHAR，VARCHARType
Pdo::Param_lob
Represents aSqlIn theLarge ObjectType
Pdo::Param_stmt
Represents aSqlIn theRecordsetType that has not yet been 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 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 object, and the data with the same name returns only one
Pdo::Fetch_named
Returns only the result set of the query with the key value as the underlying object, with the same name as an array
Pdo::Fetch_num
Returns only the result set of a query with a number as the object
Pdo::Fetch_both
Returns the result set of a query with both a key value and a number as the object
Pdo::Fetch_obj
Returns the result set as an object
Pdo::Fetch_bound
WillPdostatement::Bindparam()AndPdostatement::Bindcolumn()When the value is assigned as a variable name, it returns
Pdo::Fetch_column
Indicates that only one column in the result set is returned
Pdo::Fetch_class
Indicates that the result set is returned as a class
Pdo::Fetch_into
Indicates that the data is merged into an existing class to return
Pdo::Fetch_func
Pdo::Fetch_group
Pdo::Fetch_unique
Pdo::Fetch_key_pair
Returns the result set with the first key value following the table, followed by a number below
Pdo::Fetch_classtype
Pdo::Fetch_serialize
Represents merging data into an existing class and serializing the return
Pdo::Fetch_props_late
Available Since Php 5.2.0
Pdo::Attr_autocommit
In the set intoTrueThe time,PdoAutomatically attempts to stop accepting delegates and start execution
Pdo::Attr_prefetch
Set the size of the data that the application gets ahead of time, not all databases Oh degree of support
Pdo::Attr_timeout
Set the value of the connection database timeout
Pdo::Attr_errmode
Set upErrorMode of processing
Pdo::Attr_server_version
A read-only property that representsPdoServer-side database version of the connection
Pdo::Attr_client_version
A read-only property that representsPdoConnected clientsPdoDriver version
Pdo::Attr_server_info
A read-only property that representsPdoof the connected serverMetaInformation
Pdo::Attr_connection_status
Pdo::Attr_case
PassPdo::Case_* To manipulate the form of a column
Pdo::Attr_cursor_name
Gets or sets the name of the pointer
Pdo::Attr_cursor
Sets the type of pointer,PdoNow supportPdo::Cursor_fwdonlyAndPdo::Cursor_fwdonly
Pdo::Attr_driver_name
Returns the use of thePdoThe name of the driver
Pdo::Attr_oracle_nulls
Converts the returned empty string to aSqlOfNull
Pdo::Attr_persistent
Gets a connection that exists
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
Available Since Php 5.2.0
Pdo::Attr_emulate_prepares
Available Since Php 5.1.3.
Pdo::Errmode_silent
Do not report any error messages when an error occurs, which is the default value
Pdo::Errmode_warning
An error occurs when a message is issuedPhpOfE_warningof information
Pdo::Errmode_exception
When an error occurs, it throws aPdoexception
Pdo::Case_natural
Default display format for reply columns
Pdo::Case_lower
Force the name of the column to lowercase
Pdo::Case_upper
Force column name to uppercase
Pdo::Null_natural
Pdo::Null_empty_string
Pdo::Null_to_string
Pdo::Fetch_ori_next
Gets the next row of data in the result set, valid only when there is a pointer function
Pdo::Fetch_ori_prior
Gets the previous row of data in the result set, valid only when there is a pointer function
Pdo::Fetch_ori_first
Gets the first row of data in the result set, valid only when there is a pointer function
Pdo::Fetch_ori_last
Gets the last row of data in the result set, valid only when there is a pointer function
Pdo::Fetch_ori_abs
Gets a row of data in the result set, valid only when there is a pointer function
Pdo::Fetch_ori_rel
Gets the data for a row after the current row in the result set, only if the pointer function is available
Pdo::Cursor_fwdonly
Create a pointer manipulation object that can only be backwards
Pdo::Cursor_scroll
Creates a pointer manipulation object, passingPdo::Fetch_ori_* Content to control the result set
Pdo::Err_none (String)
Set error message without 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 .