Getting started with PDO

From: PhP PDO Study Notes

Other articles: Connecting PHP to DB2 and cloudscape through PDO

Let's get to know about the PDO extension first:

■ What is PDO? The pod (PHP Data Object) extension is added to PhP5. By default, PDO is identified in PhP6 to connect to the database. All non-PDO extensions will be removed from the extension in PhP6. This extension provides PHP built-in PDO class for accessing the database. Different databases use the same method name to solve the problem of inconsistent database connections. I configured it for development in windows. ■ PDO goals

• Provides a lightweight, clear, and convenient API
• The common features of different RDBMS databases are unified, but more advanced features are not excluded.
• PHP scripts provide optional abstract/compatibility.

■ Features of PDO:

• Performance. From the very beginning, PDO learned from the success and failure of existing database expansion. Because the PDO code is brand new, we have the opportunity to design performance again to take advantage of the latest features of PHP 5.
• Capability. PDO is designed to provide common database functions as the basis and provide convenient access to the unique functions of RDBMS.
• Simple. PDO is designed to make it easy to use databases. The API does not forcibly intervene in your code and clearly shows the process of calling each function.
• It can be expanded during runtime. The PDO extension is modular, allowing you to load drivers at the backend of your database at runtime without re-compiling or re-installing the entire PHP program. For example, the pdo_oci extension replaces the PDO extension to implement the Oracle database API. There are also some drivers for MySQL, PostgreSQL, ODBC, and Firebird. More drivers are still under development.

  Database connection:

<? PHP $ DBMS = 'mysql'; // The Database Type Oracle uses Odi. for developers, if they use different databases, you don't need to remember so many functions $ host = 'localhost'; // Database Host Name $ dbname = 'test'; // the database used $ user = 'root '; // database connection username $ pass = ''; // password $ DSN =" $ DBMS: host = $ host; dbname = $ dbname "; // try {$ DBH = new PDO ($ DSN, $ user, $ pass); // initialize a PDO object, is to 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 ); See these values} */$ DBH = NULL;} catch (pdoexception $ e) {die ("error! :". $ E-> getmessage (). "<br/>");} // This is not a persistent connection by default. If you need a persistent connection to the database, add the last parameter: array (PDO: attr_persistent => true) the result is as follows: $ db = new PDO ($ 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 be in the following format (the 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: The column name must be in uppercase.

PDO::ATTR_ERRMODE: Error message.

• PDO::ERRMODE_SILENT: No error message is displayed. Only error codes are displayed.

• PDO::ERRMODE_WARNING: A warning error is displayed.

• PDO::ERRMODE_EXCEPTION: Throw an exception.

Pdostatement: setfetchmode () is used to set the type of the return value of the result set. The same type also has:

PDO: fetch_assoc -- relational data set form
PDO: fetch_num -- data index data format
PDO: fetch_both -- both data sets are available, which is the default
PDO: fetch_obj -- in the form of an object, similar to the previous mysql_fetch_object ()

 

★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.

<? PHP // example from http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0505furlong/index.htmltry {$ DBH = new PDO ('odbc: sample', 'db2inst1', 'ibmdb', array (pdo_attr_persistent => true )); echo "connected \ n"; $ 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 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.

 

★Pre-processing statements and stored procedures

Many more mature databases support the concept of pre-processing statements. What is a pre-processing statement? You can regard the pre-processing statement as a compiled template of the SQL statement you want to run. It can be customized using variable parameters. Preprocessing statements can bring two benefits:

• The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is ready, the database will analyze, compile, and optimize the plan for executing the query. For complex queries, this process takes a long time. If you need to repeat the same query multiple times with different parameters, this process will greatly reduce the speed of the application. By using preprocessing statements, you can avoid repeated analysis, compilation, and optimization cycles. In short, preprocessing statements use fewer resources and therefore run faster.
• Parameters provided to the pre-processing statement do not need to be enclosed in quotation marks. The driver will process these parameters. If the application exclusively uses preprocessing statements, it can ensure that no SQL intrusion occurs. (However, if you still establish other parts of the query on untrusted input, there is still a risk ).

Preprocessing statements are so useful that PDO actually breaks the Rule Set in Objective 4: If the driver does not support preprocessing statements, PDO will simulate preprocessing statements.

 

PDO application instance:

<? PHP $ DBMS = 'mysql'; // The Database Type Oracle uses Odi. for developers, if they use different databases, you don't need to remember so many functions $ host = 'localhost'; // Database Host Name $ dbname = 'test'; // the database used $ user = 'root '; // database connection username $ pass = ''; // password $ DSN =" $ DBMS: host = $ host; dbname = $ dbname "; class dB extends PDO {public function _ construct () {try {parent ::__ construct ("$ globals [DSN]", $ globals ['user'], $ globals ['pass']);} catch (pdoexception $ e) {die ("error :". $ e->__ tostring (). "<Br/>") ;}} public final function query ($ SQL) {try {return parent: Query ($ this-> setstring ($ SQL ));} catch (pdoexception $ e) {die ("error :". $ e->__ tostring (). "<br/>") ;}} private final function setstring ($ SQL) {echo "I want to handle $ SQL"; return $ SQL ;}} $ db = new dB (); $ db-> setattribute (PDO: attr_case, PDO: case_upper); foreach ($ db-> query ('select * From xxxx_menu ') as $ row) {print_r ($ row);} $ db-> exec ('de Lete from 'xxxx _ menu 'Where mid = 43');?>

 

Several common methods of PHP Data Objects API:

• PDO-the PDO class
  • PDO: begintransaction-initiates a transaction
  • PDO: commit-Submit a transaction
  • PDO ::__ construct-create a PDO instance indicating a database connection
  • PDO: errorcode-Fetch the sqlstate associated with the last operation on the database handle
  • PDO: errorinfo-Fetch extended error information associated with the last operation on the database handle
  • PDO: Exec-execute an SQL statement and return the number of affected rows
  • PDO: getattribute-retrieve the attributes of a Database Connection
  • PDO: getavailabledrivers-returns an array of available drivers.
  • PDO: intransaction-check whether it is in a transaction
  • PDO: lastinsertid-return the ID or Sequence Value of the last inserted row
  • PDO: Prepare-prepares a statement for execution and returns a statement object
  • PDO: Query-executes an SQL statement, returning a result set as a pdostatement object
  • PDO: quote-quotes a string for use in a query.
  • PDO: rollback-roll back a transaction
  • PDO: setattribute-set attributes
• Pdostatement-The pdostatement class
  • 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: debugdumpparams-dump an SQL prepared command
  • 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