PHP PDO usage

PDO (PHP Data Object) is a new thing in PHP 5, when PHP 6 is out, PHP 6 only uses PDO to process the database, will all the database extension to pecl, then the default is not our favorite php_ Mysql.dll kind of, how to do pinch, we only with the times, I have tried a small PDO. (This article is only entry-level, master can skip, hehe)

"What is PDO?"

PDO is a significant feature of new PHP 5, since php4/php3 before PHP 5 is a bunch of database extensions to connect and process with each database, what Php_mysql.dll, Php_pgsql.dll, Php_mssql.dll, Php_ Sqlite.dll and so on extension to connect MySQL, PostgreSQL, MS SQL Server, SQLite, similarly, we have to use ADODB, PEAR::D B, Phplib::D B, such as database abstract class to help us, extremely cumbersome and inefficient , after all, how efficient is the PHP code that we write directly in C + + with the extended slope high pinch? So, the appearance of PDO is inevitable, we have to calm learning attitude to accept the use, perhaps you will find that you can reduce a lot of kung fu Oh.

"Installing PDO"

I am on Windows XP SP2 above, so, the whole process is on the Windows line, as for the LINUX/FREEBSD platform, please find the data set installation.
My is PHP 5.1.4, already has the extension of the Php_pdo.dll, but need a little set up to use.

Open C:\windows\php.ini, that is my php config file, find the following line:
Extension_dir
This is the directory where our extension exists, and my PHP 5 extension is in: C:\php5\ext, then I will change this line to:

Extension_dir = "C:/php5/ext"

Then go to php.ini below to find:

; ;;;;;;;;;;;;;;;;;;;;;
; Dynamic Extensions;
; ;;;;;;;;;;;;;;;;;;;;;

Here's a bunch of things like, Extension=php_mbstring.dll, here's the PHP extension load configuration, and we'll add our PDO extensions on the last side:

Extension=php_pdo.dll
Extension=php_pdo_mysql.dll
Extension=php_pdo_pgsql.dll
Extension=php_pdo_sqlite.dll
Extension=php_pdo_mssql.dll
Extension=php_pdo_odbc.dll
Extension=php_pdo_firebird.dll
; Extension=php_pdo_oci8.dll

A variety of PDO driver, can give plus, but the back of the Php_pdo_oci8.dll, because I did not install the Oralce database, so without this, use a semicolon to comment out it. Then restart our web server, Iis/apache, my is IIS, hehe, table despise me, on windows, simple.
After rebooting, write a phpinfo.php file in our Web server's documentation directory, plus these:

Phpinfo ();
?>

Then open our lovely browser: Ie/firefox, my is FireFox 2.0, just download, very cool, not afraid of rogue software, haha.
Enter in the browser: http://localhost/phpinfo.php, if your page path is inconsistent, please enter it yourself.
The output of the content, if you can see smoothly:

Pdo
PDO Support Enabled
PDO drivers MySQL, Pgsql, SQLite, MSSQL, ODBC, Firebird

There is a description of the various drivers behind,
Pdo_firebird,pdo_mssql,pdo_mysql,pdo_odbc,pdo_pgsql,pdo_sqlite

Well, congratulations on your installation, otherwise please check the above steps carefully.

"Sledgehammer Little Experiment"

I use MySQL 4.0.26, but I personally recommend that you use MySQL 4.1.x or MySQL 5.0.x, because those editions have a lot of interesting things worth learning. We here PDO need to connect is my MySQL 4.0, if you do not install MySQL, please install yourself. We built MySQL and added table foo to the test library, including four fields such as Id,name,gender,time.

We started constructing the first PDO application, creating a pdo.php file under the Web document directory:

$DSN = "Mysql:host=localhost;dbname=test";
$db = new PDO ($dsn, ' root ', ');
$count = $db exec ("INSERT into foo SET name = ' Heiyeluren ', gender= ' man ', Time=now ()");
Echo $count;
$DB = null;
?>

I don't know what that means, let's talk about it slowly. This line:
$DSN = "Mysql:host=localhost;dbname=test";
is to construct our DSN (data source) and look at the information included: the database type is MySQL, the host address is localhost, the database name is test, just a few messages. Data sources in different databases are constructed differently.

$db = new PDO ($dsn, ' root ', ');
Initialize a PDO object, the parameters of the constructor the first one is our data source, the second is the user who connects to the database server, and the third parameter is the password. We cannot guarantee that the connection is successful, we will talk about the exception later, and here we would consider it to be a successful connection.

$count = $db->exec ("INSERT into foo SET name = ' Heiyeluren ', gender= ' man ', Time=now ()");
Echo $count;
Call our successful PDO object to execute a query that is an operation that inserts a record, and uses the Pdo::exec () method to return a result that affects the record, so we output this result. Finally, you need to end the object resource:
$DB = null;

The default is not a long connection, if you need a long database connection, you need to add one last parameter: Array (pdo::attr_persistent = True) becomes this:
$db = new PDO ($dsn, ' root ', ' ', array (pdo::attr_persistent = true));

One operation is so simple, perhaps not much different from the previous, and ADODB is somewhat similar.

"Continue to understand"

If we want to extract the data, then we should use the data acquisition function. (The $db used below are all connected objects above)

foreach ($db->query ("SELECT * from foo")) {
Print_r ($row);
}
?>

We can also use this access method:

$rs = $db->query ("SELECT * from foo");
while ($row = $rs->fetch ()) {
Print_r ($row);
}
?>

If you want to get the data in the array at once, you can:

$rs = $db->query ("SELECT * from foo");
$result _arr = $rs->fetchall ();
Print_r ($result _arr);
?>

Output:

Array
(
[0] = = Array
(
[id] = 1
[0] = 1
[Name] = Heiyeluren
[1] = Heiyeluren
[Gender] = men
[2] = = Male
[Time] = 2006-10-28 23:14:23
[3] = 2006-10-28 23:14:23
)
}

We look at the records inside, the digital index and the associated index all have, waste resources, we just need to correlate the index:

$db->setattribute (Pdo::attr_case, Pdo::case_upper);
$rs = $db->query ("SELECT * from foo");
$rs->setfetchmode (PDO::FETCH_ASSOC);
$result _arr = $rs->fetchall ();
Print_r ($result _arr);
?>

Look at the above code, the SetAttribute () method is to set some properties, the main properties are: Pdo::attr_case, Pdo::attr_errmode and so on, we need to set the pdo::attr_case here, is when we use the associated index to get the data set, whether the associated index is uppercase or lowercase, there are several choices:

Pdo::case_lower--Force column names to be lowercase
Pdo::case_natural--column names are in the original way
Pdo::case_upper--Force column name to uppercase

We use the Setfetchmode method to set the type that gets the return value of the result set, and the same type:

PDO::FETCH_ASSOC--Associative array form
Pdo::fetch_num--Digital Index array form
Pdo::fetch_both--both array form, this is the default
Pdo::fetch_obj-In the form of an object, similar to the previous mysql_fetch_object ()

Of course, in general we are using PDO::FETCH_ASSOC, specifically what to use, follow your own needs, other get type reference manuals.

In addition to the above method of obtaining data, there is this:

$rs = $db->prepare ("SELECT * from foo");
$rs->execute ();
while ($row = $rs->fetch ()) {
Print_r ($row);
}
?>

Actually, almost. If you want to get a result from a field in the specified record, you can use Pdostatement::fetchcolumn ():

$rs = $db->query ("Select COUNT (*) from foo");
$col = $rs->fetchcolumn ();
Echo $col;
?>

It is common to use Fetchcolumn () for Count statistics or some records that require only a single field to operate well.

Simply summarize the above actions:

The query operation is mainly Pdo::query (), Pdo::exec (), PDO::p repare (). Pdo::query () is primarily used for operations that have logged results returned, particularly the select operation, where Pdo::exec () is primarily for operations that do not have a result set, such as INSERT, UPDATE, delete, and so on, which returns the number of columns affected by the current operation. PDO::p repare () is mainly pre-processing operations, need to $rs->execute () to perform preprocessing inside the SQL statement, this method can bind parameters, the function is relatively strong, not this article can be simple to understand, you can refer to manuals and other documents.

The main operations for getting result sets are: Pdostatement::fetchcolumn (), Pdostatement::fetch (), Pdostatement::fetchall (). Pdostatement::fetchcolumn () is a field that gets the result that specifies the first record, and the default is the first field. Pdostatement::fetch () is used to get a record, Pdostatement::fetchall () is to get all the recordset into one, and get the result can be pdostatement by: Setfetchmode to set the type that requires the result collection.

There are also two peripheral operations, one of which is Pdo::lastinsertid () and Pdostatement::rowcount (). Pdo::lastinsertid () is the last insert operation returned, and the primary key column type is the final self-increment ID. Pdostatement::rowcount () is primarily used for pdo::query () and PDO::p Repare () for the result set affected by the delete, INSERT, update operation, for Pdo::exec () Method and select operation are not valid.

"Error Handling"

What if an error is encountered in the program? Here we describe the PDO class for error messages and exception handling.

1. Object-oriented approach

Take a look at the processing of connection errors, etc., using an object-oriented approach:

try {
$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);
$DB = null;
} catch (Pdoexception $e) {
Print "Error:". $e->getmessage (). "
";
Die ();
}
?>

This takes advantage of our PHP 5 object-oriented exception handling feature, which initializes the call Pdoexception to initialize an exception class if there is an exception.
Pdoexception the attribute structure of the exception class:

Class Pdoexception extends Exception
{
public $errorInfo = null; Error message, you can call Pdo::errorinfo () or Pdostatement::errorinfo () to access
protected $message; Exception information, you can try Exception::getmessage () to access
protected $code; SQL Status error code, you can use Exception::getcode () to access
}
?>

This exception handling class is integrated with PHP 5 built-in exception handling class, we simply look at PHP 5 built-in exception handling class structure:

Class Exception
{
Property
protected $message = ' Unknown exception '; Exception information
protected $code = 0; User-defined exception codes
protected $file; The file name of the exception that occurred
protected $line; The line number of the code where the exception occurred

Method
Final function getMessage (); Return exception information
Final function GetCode (); Return exception code
Final function getFile (); Returns the file name of the exception that occurred
Final function getLine (); Returns the line number of the code where the exception occurred
Final function gettrace (); BackTrace () array
Final function gettraceasstring (); Gettrace () information that has been rasterized into a string
}
?>

Accordingly, in the code can appropriately call GetFile () and GetLine () for error location, more convenient debugging.

2. Using a process-oriented approach
Look at the code first:

$db = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);
$rs = $db->query ("Select AA,BB,CC from foo");
if ($db->errorcode ()! = ' 00000 ') {
Print_r ($db->errorinfo ());
Exit
}
$arr = $rs->fetchall ();
Print_r ($arr);
$DB = null;
?>

The PDO and Pdostatement objects have the ErrorCode () and ErrorInfo () methods, and if there are no errors, ErrorCode () returns: 00000, or some error code is returned. ErrorInfo () returns an array that includes PHP-defined error codes and MySQL error codes and error messages, with the following array structure:

Array
(
[0] = 42s22
[1] = 1054
[2] = = Unknown column ' aaa ' in ' Field list '
)

After each query execution, the results of ErrorCode () are up-to-date, so we can easily control the display of error messages.

"Simple Summary"

As seen from the above, the PDO function is really powerful, and there are some things I have not mentioned, such as binding parameters, preprocessing, stored procedures, transaction processing and so on. There are different data extension DSN constructs, Oracle database own a lot of special things, need to go deep to learn to understand, this article simply describes some of the introductory knowledge, is a simple understanding of PDO.

Resources
PHP 5 Data Object (PDO) abstraction layer with Oracle

PDO Functions

What is PDO?
The POD (PHP Data Object) extension is added in PHP5, and PHP6 connects the database with PDO, and all non-PDO extensions will be removed from the extension in PHP6. The extension provides PHP built-in class PDO to access the database, different databases use the same method name, to solve the problem of non-uniform database connection.
I was configured to do development under Windows.


The goal of PDO
Provides a lightweight, clear, and convenient API to unify the common features of various RDBMS libraries, without excluding more advanced features. Provides an optional, large degree of abstraction/compatibility through PHP scripting.

Features of PDO:
Performance. PDO has learned from the outset the successes and failures of existing database extensions. Because PDO's code is brand new, we have the opportunity to start designing performance again to take advantage of the latest features of PHP 5. Ability. PDO is designed to provide the basis for common database functionality while providing easy access to the unique features of the RDBMS. Simple. PDO is designed to make it easy to use your database. The API does not force you into your code, and it clearly shows the process of each function call. The runtime is extensible. The PDO extension is modular, allowing you to load drivers at run time for your database backend without having to recompile or reinstall the entire PHP program. For example, the Pdo_oci extension implements the Oracle database API instead of the PDO extension. There are also drivers for MySQL, PostgreSQL, ODBC, and Firebird, and more drivers are still being developed.
Installing PDO
I am here for the PDO extension under Windows development, if you want to install the configuration under Linux, please look elsewhere.
Version requirements:
php5.1 and later versions of the package have been brought in;
php5.0.x to pecl.php.net download, put in your extension library, PHP is located in the folder of the Ext folder;
The manual says that versions prior to 5.0 cannot run the PDO extension.

Configuration:
Modify your php.ini profile so that it supports PDO. (php.ini this thing is not understood, first figure out, to modify the call your Phpinfo () function shows the php.ini)
Put
Extension=php_pdo.dll in front of the semicolon is removed, the other is a PHP configuration file comment symbol, this extension is necessary.
and down there.
; Extension=php_pdo.dll
; Extension=php_pdo_firebird.dll
; Extension=php_pdo_informix.dll
; Extension=php_pdo_mssql.dll
; Extension=php_pdo_mysql.dll
; Extension=php_pdo_oci.dll
; Extension=php_pdo_oci8.dll
; Extension=php_pdo_odbc.dll
; Extension=php_pdo_pgsql.dll
; Extension=php_pdo_sqlite.dll
The corresponding databases for each extension are:
Driver namesupported databasespdo_dblibfreetds/microsoft SQL server/sybasepdo_firebirdfirebird/interbase 6PDO_ INFORMIXIBM Informix Dynamic serverpdo_mysqlmysql 3.x/4.xpdo_ocioracle call Interfacepdo_odbcodbc v3 (IBM DB2, UnixODBC a nd win32 ODBC) Pdo_pgsqlpostgresqlpdo_sqlitesqlite 3 and SQLite 2

What kind of database would you like to use, as long as you put the appropriate pre-extension comment symbol ";" get rid of it.

Using PDO
I suppose you have already installed MySQL, if not installed, trouble first try to install, my is mysql5.0.22, night passers-by IS MySQL 4.0.26 can also use.
★ Database Connection:
We use the following example to analyze the PDO connection database,

$dbms = ' MySQL '; Database type Oracle with ODI, for developers, using different databases, just change this, don't remember so many functions $host = ' localhost ';//Database host name $dbName = ' Test '; The database used $user = ' root '; Database connection user Name $pass = "; The corresponding password $DSN = "$dbms: host= $host;d bname= $dbName"; // try{ $DBH =newpdo ($dsn, $user, $pass);//Initialize a PDO object to create a database connection object $DBH echo "Connection successful "; /* You can also perform a search operation foreach ($dbh->query (' SELECT * from FOO ') as$row) { Print_r ($row);//You can use Echo ($GLOBAL); To see these values } */ $DBH =null; }catch (pdoexception$e) { Die ("error!:". $e->getmessage (). " "); } The default is not a long connection, if you need a long database connection, you need to add one last parameter: Array (pdo::attr_persistent = True) becomes this: $db =newpdo ($dsn, $user, $pass, Array (pdo::attr_persistent=>true)); ?>

★ Database query:
We have made a query above, and we can use the following query:

$db->setattribute (Pdo::attr_case,pdo::case_upper); Setting properties $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, the two parameters are placed, and the field names are cast to uppercase. The following is a list of parameters with Pdo::setattribute ():

Pdo::attr_case: Force the column name to become a format, as detailed below (the second parameter):
Pdo::case_lower: Enforces that the column name is lowercase. Pdo::case_natural: Column names are pdo::case_upper in the original way: Force column names to be uppercase. Pdo::attr_errmode: Error prompt.
Pdo::errmode_silent: The error message is not displayed, only the error code is displayed. Pdo::errmode_warning: A warning error is displayed. Pdo::errmode_exception: Throws an exception. Pdo::attr_oracle_nulls (not just ORACLE valid, other databases are also valid):) Specifies the value that the null value returned by the database corresponds to in PHP.
Pdo::null_natural: no change. 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 is 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.

$rs->setfetchmode (PDO::FETCH_ASSOC) In the example, is a declaration of the return type of Pdostatement::setfetchmode ().
There are the following:
pdo::fetch_assoc--Associative Array Form
Pdo::fetch_num--Digital Index array form
Pdo::fetch_both--both array form, this is the default
Pdo::fetch_obj-In the form of an object, similar to the previous mysql_fetch_object ()

See the Manual for more return type declarations (pdostatement:: Method names).

★ INSERT, UPDATE, delete data,

$db->exec ("DELETE from ' Xxxx_menu ' where mid=43");

Simply summarize the above actions:
The query operation is mainly Pdo::query (), Pdo::exec (), PDO::p repare ().
Pdo::query () is primarily used for operations that have logged results returned, in particular the select operation,
Pdo::exec () is primarily for operations that do not have a result set, such as INSERT, UPDATE, delete, and so on, which returns the number of columns affected by the current operation.
PDO::p repare () is mainly pre-processing operations, need to $rs->execute () to perform preprocessing inside the SQL statement, this method can bind parameters, the function is relatively strong, not this article can be simple to understand, you can refer to manuals and other documents.
The main operations for getting result sets are: Pdostatement::fetchcolumn (), Pdostatement::fetch (), Pdostatement::fetchall ().
Pdostatement::fetchcolumn () is a field that gets the result that specifies the first record, and the default is the first field.
Pdostatement::fetch () is used to get a record,
Pdostatement::fetchall () is to get all recordsets into one, and get results can be set by Pdostatement::setfetchmode to the type that requires the result collection.
There are also two peripheral operations, one of which is Pdo::lastinsertid () and Pdostatement::rowcount (). Pdo::lastinsertid () is the last insert operation returned, and the primary key column type is the final self-increment ID.
Pdostatement::rowcount () is primarily used for pdo::query () and PDO::p Repare () for the result set affected by the delete, INSERT, update operation, for Pdo::exec () Method and select operation are not valid.



★ Transaction and Auto Commit

Now that you've connected to MySQL via PDO, you should understand how PDO manages transactions before you issue queries. If you have not touched a transaction before, you first need to know the 4 characteristics of the transaction: atomicity (atomicity), consistency (consistency), independence (isolation), and persistence (durability), which is ACID. In layman's words, for any work performed in a transaction, even if it is staged in stages, it is guaranteed that the work will be applied securely to the database and will not be affected by other connections when the work is committed. Transactional work can be automatically revoked on request (assuming you haven't committed it yet), making error handling in the script much easier.
A transaction is usually achieved by saving up a batch of changes and making it effective at the same time. The benefit of this is that the efficiency of these updates can be greatly improved. In other words, a transaction can make the script faster and may be more robust (although it is necessary to use the transaction correctly for this benefit).
Unfortunately, not every database supports transactions (MYSQL5 support transactions, Mysql4 I don't know), so when the connection is first opened, PDO needs to run in the so-called "autocommit (auto-commit)" mode. Autocommit mode means that if a database supports transactions, every query that you run has its own implicit transaction, and if the database does not support transactions, there is no such transaction 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 handling setting: This is always a critical 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 was successful.
When the script finishes, or when a connection is about to be closed, if there is an unfinished transaction, then PDO will automatically roll back the transaction. This is a security measure that helps to avoid inconsistencies when the script ends abnormally?? If the transaction is not committed explicitly, then it is assumed that there will be inconsistencies somewhere, so a rollback is performed to ensure the security of the data.

//Example from Http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0505furlong/index . html try{ $DBH =new PDO (' odbc:sample ', ' db2inst1 ', ' ibmdb2 ', Array (pdo_attr_persistent=>true)); echo "connected\n"; $dbh->setattribute (pdo_attr_errmode,pdo_errmode_exception); $dbh->begintransaction (); $dbh->exec ("insert into the staff (ID, first, last) values (at $, ' Joe ', ' Bloggs ')"); $dbh->exec ("INSERT into Salarychange (ID, amount, changedate) values (all, 50000, now ())"); $dbh->commi T (); }catch (Exception $e) { $dbh->rollback (); echo "Failed:". $e->getmessage (); }

In the example above, let's say we create a set of entries for a new employee that has an ID number, which is 23. In addition to entering this person's basic data, we also need to record the employee's salary. The two updates are simple to complete, but by including the two updates in the BeginTransaction () and commit () calls, you can ensure that the changes are not visible to others until the changes are complete. If an error occurs, the catch block can roll back all changes that have occurred since the start of the transaction and print an error message.
It is not always important to make updates in a transaction. You can also emit complex queries to extract data, and you can use that information to build more updates and queries. When a transaction is active, it can be ensured that other people cannot make changes while the work is in progress. In fact, this is not 100% correct, but if you have not heard of the matter before, this introduction is also possible.

★ Preprocessing statements and stored procedures
Many more mature databases support the concept of preprocessing statements. What is a preprocessing statement? You can think of preprocessing statements as a compiled template of the SQL you want to run, which can be customized using variable parameters. Preprocessing statements can provide two major benefits:

A query needs to be parsed (or prepared) only once, but it can be executed multiple times with the same or different parameters. When the query is ready, the database parses, compiles, and optimizes the plan for executing the query. For complex queries, this process takes a long time, and if you need to repeat the same query multiple times with different parameters, the process will greatly reduce the speed of your application. By using preprocessing statements, you can avoid repeating the parse/compile/optimize cycle. In short, preprocessing statements use fewer resources and thus run faster. The parameters provided to the preprocessing statements do not need to be enclosed in quotation marks, and the driver handles these. If the application exclusively uses preprocessing statements, you can ensure that no SQL intrusion occurs. (However, if you still have other parts of the query built on untrusted input, there is still a risk).

Preprocessing statements are so useful that PDO actually breaks the rule set in Target 4: If the driver does not support preprocessing statements, PDO will emulate the preprocessing statements.


Examples: Examples of PDO applications:

$dbms = ' mysql ';//database type for Oracle with ODI, for developers, using different databases, just change this, don't remember so many functions $host = ' localhost ';//Database host name $dbName = ' test ';//Database used $user = ' root ';//database connection user name $pass = ";//the corresponding password $DSN = "$dbms: host= $host;d bname= $dbName"; classdbextendspdo{ Publicfunction__construct () { try{ Parent::__construct ("$GLOBALS [DSN]", $GLOBALS [' User '], $GLOBALS [' Pass ']; }catch (pdoexception$e) { Die ("Error:". $e->__tostring (). " "); } } Publicfinalfunctionquery ($sql) { try{ Returnparent::query ($this->setstring ($sql)); }catch (pdoexception$e) { Die ("Error:". $e->__tostring (). " "); } } Privatefinalfunctionsetstring ($sql) { echo "I have to deal with $sql"; Return$sql; } } $db =newdb (); $db->setattribute (Pdo::attr_case,pdo::case_upper); foreach ($db->query (' SELECT * from Xxxx_menu ') as$row) { Print_r ($row); } $db->exec (' DELETE from ' Xxxx_menu ' where mid=43 '); ?>

Transferred from: http://www.cnblogs.com/xiaochaohuashengmi/archive/2010/08/12/1797753.html

