New php database connection method: PHP Data Object

The PHP Data Object (PDO) Extension defines a lightweight and consistent database access interface. each driver that implements the PDO interface shows database-related features like general extension functions. you cannot use the PDO function to execute any database function. You must use a database driver to access the database server.
PDO provides a data access abstraction layer, which means that you can use the same function to publish and query data without knowing the database in use.
PDO is bound to PHP5.1 and can be used as a PECL extension in PHP5.0. PDO requires the new PHP5 core object-oriented feature and cannot be run in earlier versions.

Zookeeper -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The following is a new entry book.

★Database connection:
We use the following example to analyze how PDO connects to the database,
<? Php
$ Dbms = mysql; // Database Type Oracle uses ODI. for developers, if they use different databases, they do not need to remember so many functions.
$ Host = localhost; // Database host Name
$ DbName = test; // database used
$ User = root; // database connection username
$ Pass =; // Password
$ Dsn = "$ dbms: host = $ host; dbname = $ dbName ";
//

Try {
$ Dbh = newPDO ($ dsn, $ user, $ pass); // initialize a PDO object, that is, create a database connection object $ dbh
Echo "connection successful <br/> ";
/* You can perform a search operation.

Foreach ($ dbh-> query (SELECT * from FOO) as $ row ){
Print_r ($ row); // you can use echo ($ GLOBAL); to view these values
}
*/
$ Dbh = null;
} Catch (PDOException $ e ){
Die ("Error! : ". $ E-> getMessage ()." <br/> ");
}
// By default, this is not a persistent connection. If you need a persistent connection to the database, you need to add the following parameter: array (PDO: ATTR_PERSISTENT => true:
$ Db = newPDO ($ dsn, $ user, $ pass, array (PDO: ATTR_PERSISTENT => true ));
?>

★Database Query:

We have performed a query above, and we can also use the following query:
<? Php
$ Db-> setAttribute (PDO: ATTR_CASE, PDO: CASE_UPPER); // set attributes
$ Rs = $ db-> query ("SELECT * FROM foo ");
$ Rs-> setFetchMode (PDO: FETCH_ASSOC );
$ Result_arr = $ rs-> fetchAll ();
Print_r ($ result_arr );
?>

Because the setAttribute () method is used, place the two parameters and forcibly convert the field name to uppercase. The following lists the parameters with multiple PDO: setAttribute:

PDO: ATTR_CASE: force the column name to a format, as detailed below (second parameter ):

PDO: CASE_LOWER: the column name must be in lower case.

PDO: CASE_NATURAL: column names follow the original method

PDO: CASE_UPPER: Force column names to be uppercase.

PDO: ATTR_ERRMODE: error message.

PDO: ERRMODE_SILENT: only error codes are displayed.

PDO: ERRMODE_WARNING: displays a warning error.

PDO: ERRMODE_EXCEPTION: throw an exception.

PDO: ATTR_ORACLE_NULLS (not only effective in ORACLE, but also in other databases): Specifies the value of the NULL value returned by the database in php.

PDO: NULL_NATURAL: Unchanged.

PDO: NULL_EMPTY_STRING: Empty string is converted toNULL.

PDO: NULL_TO_STRING: NULL is converted to an empty string.

PDO: ATTR_STRINGIFY_FETCHES: Convert numeric values to strings when fetching. Requires bool.

PDO: ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requiresarray (string classname, array (mixed constructor_args )).

PDO: ATTR_AUTOCOMMIT (available in OCI, Firebird and MySQL): Whether to autocommit every single statement.

PDO: MYSQL_ATTR_USE_BUFFERED_QUERY (available in MySQL): Use buffered queries.

In the example, $ rs-> setFetchMode (PDO: FETCH_ASSOC); is PDOStatement: setFetchMode (), which declares the return type.
There are:
PDO: FETCH_ASSOC -- join array form
PDO: FETCH_NUM -- numeric index array format
PDO: FETCH_BOTH -- both arrays are available, which is the default
PDO: FETCH_OBJ -- according to the object form, similar to the previous mysql_fetch_object ()

For more information about the return type declaration (PDOStatement: method name), see the manual.

★Insert, update, and delete data,
$ Db-> exec ("delete from 'xxxx _ menu 'where mid = 43 ");

Briefly summarize the above operations:
Query operations are mainly PDO: query (), PDO: exec (), PDO: prepare ().
PDO: query () is mainly used for operations that return records, especially SELECT operations,
PDO: exec () is mainly used for operations that do not return result sets, such as INSERT, UPDATE, DELETE, and so on. It returns the number of columns affected by the current operation.
PDO: prepare () is mainly used for preprocessing. You need to use $ rs-> execute () to execute the SQL statements in the preprocessing. This method can bind parameters and has powerful functions, this document is not a simple description. You can refer to the Manual and other documents.
To obtain a result set, perform the following operations: PDOStatement: fetchColumn (), PDOStatement: fetch (), and PDOStatement: fetchALL ().
PDOStatement: fetchColumn () is a field of the first record specified in the result. The default value is the first field.
PDOStatement: fetch () is used to obtain a record,
PDOStatement: fetchAll () is to obtain all the record sets to one. You can use PDOStatement: setFetchMode to set the type of the desired result set.
There are also two peripheral operations: PDO: lastInsertId () and PDOStatement: rowCount (). PDO: lastInsertId () is the last insert operation, and the primary key column type is the last auto-increment ID of auto-increment.
PDOStatement: rowCount () is the result set affected by the DELETE, INSERT, and UPDATE operations on PDO: query () and PDO: prepare :: the exec () method and SELECT operation are invalid.

★Transactions and automatic submission

So far, you have connected to mysql through PDO. Before sending a query, you should understand how PDO manages transactions. If you have never touched on a transaction before, you must first understand the four features of the transaction: Atomicity, Consistency, Isolation, and Durability ), ACID. In layman's words, any work performed in a transaction can be safely applied to databases even if it is executed in stages, when a job is submitted, it is not affected by other connections. Transactional work can be automatically revoked based on the request (assuming you have not submitted it), which makes it easier to handle errors in the script.
Transactions are usually implemented by saving up a batch of changes to make them take effect at the same time. This can greatly improve the efficiency of these updates. In other words, a transaction can make the script faster and more robust (but you need to use the transaction correctly to get this benefit ).
Unfortunately, not every database supports transactions (Mysql5 supports transactions, I don't know about mysql4), so when the connection is opened for the first time, PDO needs to run in the so-called "auto-commit" mode. The automatic commit Mode means that if the database supports transactions, each query you run has its own implicit transaction. If the database does not support transactions, no such transaction exists for each query. If you need a transaction, you must use the PDO: beginTransaction () method to start a transaction. If the underlying driver does not support transactions, a PDOException will be thrown (regardless of the error processing settings: This is always a serious error state ). In a transaction, you can use PDO: commit () or PDO: rollBack () to end the transaction, depending on whether the code running in the transaction is successful.
When the script ends, or when a connection is about to be closed, if there is an unfinished transaction, PDO will automatically roll back the transaction. This is a security measure that helps avoid inconsistencies when the script ends abnormally-if the transaction is not explicitly committed, it is assumed that there will be inconsistencies somewhere, therefore, perform rollback to ensure data security.
Try {
$ Dbh = new PDO (odbc: SAMPLE, db2inst1, ibmdb2,
Array (PDO_ATTR_PERSISTENT => true ));
Echo "Connected ";
$ Dbh-> setAttribute (PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION );
$ Dbh-> beginTransaction ();
$ Dbh-> exec ("insert into staff (id, first, last) values (23, Joe, Bloggs )");
$ Dbh-> exec ("insert into salarychange (id, amount, changedate)
Values (23,500 00, NOW ())");
$ Dbh-> commit ();
 
} Catch (Exception $ e ){
$ Dbh-> rollBack ();
Echo "Failed:". $ e-> getMessage ();
}

In the preceding example, assume that we create a group of entries for a new employee. This employee has an ID number, that is, 23. In addition to entering the basic data of this person, we also need to record the employee's salary. The two updates are easy to complete, but by including the two updates in the beginTransaction () and commit () calls, you can ensure that others cannot see the changes before the changes are completed. If an error occurs, the catch block can roll back all the changes that have occurred since the beginning of the transaction and print an error message.
It is not necessary to update the transaction. You can also issue complex queries to extract data, and use that information to create more updates and queries. When a transaction is active, other persons cannot make changes during work. In fact, this is not 100% correct, but if you have never heard of a transaction before, it is not worth mentioning. <