PHP PDO Connection Database

"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 to connect MySQL,PostgreSQL,MS SQL Server, SQLite, again, we have to help us with the ADOdb,PEAR::D b,phplib::D B Database abstraction class, which is incredibly cumbersome and inefficient, after all, The efficiency of PHP code how can we write directly in C/C + + extension 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:

" C:/php5/ext "

Then go to php.ini below to find:

extension=php_pdo.dllextension=php_pdo_mysql.dllextension=php_pdo_pgsql.dllextension =php_pdo_sqlite.dllextension=php_pdo_mssql.dllextension=php_pdo_odbc.dllextension =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 reboot

$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)

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 (    [                    Array            ] (= 1            [0] = 1 =  Heiyeluren            [1] = =            Heiyeluren= man            [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 {   $dbnew$user$pass);    $db NULL  catch$e) {   print$e->getmessage (). "<br/>";     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 extends Exception {    public$errorInfonull;  // 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, which can be accessed using Exception::getcode () }

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{    //Properties    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 code line number//method where the exception occurred    Final functionGetMessage ();//Return exception Information    Final functionGetCode ();//return Exception Code    Final functionGetFile ();//returns the file name of the exception that occurred    Final functionGetLine ();//returns the line number of the code where the exception occurred    Final functionGettrace ();//backtrace () array    Final functionGettraceasstring ();//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.

PHP PDO Connection Database