Simple use of PDO in PHP5

PhP5

PDO (PHP Data Object) is a new PHP 5 things, when PHP 6 are coming out, PHP 6 only by default use PDO to process the database, will all the database expansion to the PECL, then the default is not our favorite php_ Mysql.dll and so on, then how to pinch, we only with the times, I have a small try a PDO. (This article is only entry-level, master can skip, hehe)

"What's PDO?"

PDO is a major feature of PHP 5, because before PHP 5 php4/php3 are 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 to connect MySQL, PostgreSQL, MS SQL Server, SQLite, we must, by virtue of ADOdb, PEAR::D B, Phplib: Database abstract classes such as:D B to help us, extremely cumbersome and inefficient , after all, how efficient the PHP code can we write directly with C + + the expansion slope of high pinch? Therefore, the emergence of PDO is inevitable, we have to calm the attitude of learning to accept the use, perhaps you will find that you can reduce a lot of kung fu Oh.

"Install PDO"

I am on the Windows XP SP2, so, the whole process is in the Windows line, as for Linux/freebsd platform, please find the data setup.
I'm PHP 5.1.4, already with the Php_pdo.dll extensions, but need a little setup to use.

Open C:\windows\php.ini, that's my PHP profile, and find the following line:
Extension_dir
This is the directory where we expand, and my PHP 5 extension is: C:\php5\ext, so I'll change the line to:

Extension_dir = "C:/php5/ext"

And then find it below the php.ini:

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

Here's a bunch of similar, extension=php_mbstring.dll stuff, and here's the PHP extension load configuration, and we're going to 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 drivers, can be added to the full 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 annotate it. Then restart our web server, Iis/apache, my IIS, hey, table despise me, on windows, simple.
After restarting, write a phpinfo.php file in the document directory of our Web server, plus these:

?
Phpinfo();
?>

Then open our lovely browser: Ie/firefox, my FireFox 2.0, just downloaded, 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 are a variety of driver instructions behind it,
Pdo_firebird,pdo_mssql,pdo_mysql,pdo_odbc,pdo_pgsql,pdo_sqlite

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

"A little test of a sledgehammer."

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 PDO need to connect here 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 in the Web document directory:

<?php
$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;
?>

Don't understand what it means, let's talk about it slowly. This line:
$DSN = "Mysql:host=localhost;dbname=test";
is to construct our DSN (data source), to see the information included: the database type is MySQL, the host address is localhost, the database name is test, so a few information. The data sources in different databases are constructed differently.

$db = new PDO ($dsn, ' root ', ');
Initializes a PDO object, the constructor's arguments the first one is our data source, the second is the user connecting to the database server, and the third parameter is the password. We can not guarantee the success of the connection, we will talk about the exception later, here we think it is connected successfully.

$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 inserts a record and uses the Pdo::exec () method to return a result that affects the record, so we output the result. Finally, you need to end the object resource:
$DB = null;

The default is not a long connection, and if you need a database long connection, you need to add a final parameter: Array (pdo::attr_persistent => true) to 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 data, then we should use the data acquisition function. (The $db used below are the objects that are already connected above)

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

We can also use this method of obtaining:

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

If you want to get all the data in an array at once, you can do this:

<?php
$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] => male
[2] => Male
[Time] => 2006-10-28 23:14:23
[3] => 2006-10-28 23:14:23
)
}

We look inside the records, the digital index and the associated index all have, waste resources, we only need to associate the index:

<?php
$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 code above, 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 up here is Pdo::attr_case, When we use the associated index to get the dataset, the associated index is uppercase or lowercase, and there are several options:

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

We use the Setfetchmode method to set the type to get the return value of the result set, as well as the same type:

PDO::FETCH_ASSOC--Associative array form
Pdo::fetch_num--Digital indexed array form
Pdo::fetch_both--both array forms are available, which is the default
Pdo::fetch_obj-Similar to previous mysql_fetch_object () in the form of objects

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

In addition to this way of getting the data, there is this:

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

Actually, it's almost. If you want to obtain a field result in a specified record, you can use Pdostatement::fetchcolumn ():

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

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

Simply summarize the above actions:

Query operations are mainly Pdo::query (), Pdo::exec (), PDO::p repare (). Pdo::query () is used primarily for operations that have record results returned, especially select operations, where the pdo::exec () is primarily for operations returned without a result set, such as INSERT, UPDATE, delete, which returns the number of columns affected by the current operation. PDO::p repare () is mainly preprocessing operations, the need to $rs->execute () to perform preprocessing of the SQL statements, this method can be binding parameters, powerful, not this article can be simple to say, you can refer to the manual and other documents.

Get result set operations mainly: Pdostatement::fetchcolumn (), Pdostatement::fetch (), Pdostatement::fetchall (). Pdostatement::fetchcolumn () is a field that gets the result to specify 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 the results can be obtained through Pdostatement:: Setfetchmode to set the type that requires the result collection.

In addition there are two peripheral operations, one is Pdo::lastinsertid () and Pdostatement::rowcount (). Pdo::lastinsertid () is the last self-increasing ID that returns the last insert operation, and the primary key column type is the increment. Pdostatement::rowcount () is primarily used for pdo::query () and PDO::p Repare () The result set that is affected by the delete, INSERT, and update operations, to Pdo::exec () Method and select operation are not valid.

"Error Handling"

What if you encounter errors in your program? Here we describe the error message and exception handling for the PDO class.

1. Object-oriented approach

Let's take a look at the handling of connection errors and so on, using object-oriented methods:

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

Here we use our PHP 5 object-oriented exception handling feature to initialize an exception class if there is an exception in the initialization call pdoexception.
Pdoexception the attribute structure of the exception class:

<?php
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 the integrated PHP 5 built-in exception handling class, we simply look at the PHP 5 built-in exception handling class structure:

<?php
Class Exception
{
Property
protected $message = ' Unknown exception '; Exception information
protected $code = 0; User-defined exception codes
protected $file; Name of the exception that occurred
protected $line; 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 where the exception 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 formatted as a string
}
?>

Accordingly, in the code can be appropriate to call GetFile () and getline () for error location, more convenient for debugging.

2. Using a process-oriented approach
First look at the code:

?
$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 errorcode () and ErrorInfo () methods, and if there are no errors, errorcode () returns 00000, or it returns some error codes. ErrorInfo () returns an array that includes PHP-defined error codes and MySQL error codes and error messages, and the array structure is as follows:

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

The results of errorcode () are up to date every time the query is executed, so we can easily control the error message display.

"Simple Summary"

From the use above, I see that the PDO function is really powerful, and there are some things I did not talk about, such as binding parameters, preprocessing, stored procedures, transactions, and so on. There are different data to expand the DSN structure, the Oracle database itself a lot of special things, need to go deep to learn to understand, this article is just a simple description of some introductory knowledge, is a simple understanding of PDO.

Resources
PHP 5 Data Object (PDO) abstraction Layer and Oracle