Simple use of PDO in PHP5

The simple use of PDO in PHP5, the simple use of PDO in PHP5, PDO (PHPDataObject) is new to PHP5, when PHP6 is coming out, PHP6 only uses PDO to process databases by default. all database extensions will be moved to PECL. by default, php_mysql.dll is absent. what should we do? "> <

PDO (PHP Data Object) is a new feature of PHP 5. When PHP 6 is ready, PHP 6 uses PDO to process databases by default, by default, all the database extensions are moved to PECL, so we don't have php_mysql.dll and so on. So what should we do? we have to keep up with the times, so I tried PDO. (This article is only an entry level. you can skip it)

[What is PDO]

PDO is a major new feature of PHP 5, because php4/php3 before PHP 5 was a bunch of database extensions to connect to and process various databases, php_mysql.dll, php_pgsql.dll, php_mssql.dll, php_sqlite.dll, and other extensions to connect to MySQL, PostgreSQL, ms SQL Server, and SQLite. Similarly, we must use ADOdb, PEAR: DB, PHPlib :: database abstract classes such as DB are very cumbersome and inefficient to help us. after all, how can we directly use C/C ++ to write php code with a high scaling slope? Therefore, the emergence of PDO is inevitable. you should accept it with a calm learning attitude. maybe you will find it can reduce your efforts.

[Install PDO]

I am on Windows XP SP2, so the whole process is performed on Windows. for Linux/FreeBSD and other platforms, please find the information to set and install.
My PHP version is 5.1.4 and I already have the php_pdo.dll extension, but I need to set it up a little before it can be used.

Open c: \ windows \ php. ini, which is my PHP configuration file. find the following line:

Extension_dir

This is the directory of our extension. my PHP 5 extension is in: C: \ php5 \ ext, so I will change this line:

Extension_dir = "C:/php5/ext"

Then find the following link under php. ini:

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

Below are a bunch of things similar to extension = php_mbstring.dll. here is the PHP extension loading configuration. we will add our PDO extension at the end:

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

All kinds of PDO drivers can be added, but the php_pdo_oci8.dll in the future will be commented out using semicolons because I have not installed the Oralce database. Then restart our Web server, IIS/Apache, and I use IIS. hey, the table despise me. it's easy on Windows.

After the restart, write a phpinfo. php file under the document directory of our Web server, and add the following:

<?
Phpinfo ();
?>

Then open our lovely browser: IE/FireFox. I use FireFox 2.0. I just downloaded it. it's so nice. I'm not afraid of rogue software. haha.

Enter http: // localhost/phpinfo. php in the browser. if the path on the page is inconsistent, enter the path on your own.

In the output content, if you can see smoothly:

PDO
PDO support enabled
PDO drivers mysql, pgsql, sqlite, mssql, odbc, firebird

The following describes various drivers: PDO_Firebird, pdo_mssql, pdo_mysql, PDO_ODBC, pdo_pgsql, and pdo_sqlite.

Congratulations, you have successfully installed it. otherwise, please carefully check the above steps.

[Try a knife]

I use MySQL 4.0.26, but I personally recommend that you use MySQL 4.1.x or MySQL 5.0.x because there are many interesting things worth learning. Here, we need to connect to my MySQL 4.0. if you have not installed MySQL, install it on your own. We have created MySQL and added table foo in the test Library, including four fields: id, name, gender, and time.

We started to construct the first PDO application and create a pdo. php file under 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 = 'male', 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 ";

It is to construct our DSN (data source). let's see the following information: the database type is mysql, the host address is localhost, and the database name is test. The data source construction methods for different databases are different.

$ Db = new PDO ($ dsn, 'root ','');

Initialize a PDO object. The first parameter of the constructor is our data source, the second parameter 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 exceptions later. here we will consider it successful.

$ Count = $ db-> exec ("insert into foo SET name = 'heiyeluren', gender = 'male', time = NOW ()");
Echo $ count;

Call the successfully connected PDO object to execute a query. this query is an operation to insert a record. Using the PDO: exec () method, a result that affects the record is returned, so we output this result. Finally, you need to end the object resource:

$ Db = null;

By default, this is not a persistent connection. if you need a persistent connection to the database, you must add the following parameter: array (PDO: ATTR_PERSISTENT => true:

$ Db = new PDO ($ dsn, 'root', '', array (PDO: ATTR_PERSISTENT => true ));

One operation is so simple. it may not be much different from the previous one. it is somewhat similar to ADOdb.

[Continue]

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

<? Php
Foreach ($ db-> query ("SELECT * FROM foo ")){
Print_r ($ row );
}
?>

We can also use this method:

<? Php
$ Rs = $ db-> query ("SELECT * FROM foo ");
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>

If you want to get all the data to the array at one time, you can do this:

<? Php
$ Rs = $ db-> query ("SELECT * FROM foo ");
$ Result_arr = $ rs-> fetchAll ();
Print_r ($ result_arr );
?>

Array
([0] => Array (
[Id] => 1
[0] => 1
[Name] => heiyeluren
[1] => heiyeluren
[Gender] => male
[2] => male
[Time] => 23:14:23
[3] => 23:14:23
)
}

We can see that the record, the number index and the associated index both exist, which is a waste of 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 );
?>

According to the code above, the setAttribute () method is to set some attributes. the main attributes include PDO: ATTR_CASE, PDO: ATTR_ERRMODE, etc. here we need to set PDO: ATTR_CASE, when we use the associated index to retrieve a dataset, the associated index is in upper or lower case. There are several options:

PDO: CASE_LOWER -- force column name to be lowercase
PDO: CASE_NATURAL -- column names follow the original method
PDO: CASE_UPPER -- force column name to uppercase

We use the setFetchMode method to set the type of the return value for the result set. the same types include:

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

Of course, we generally use PDO: FETCH_ASSOC. For more information about the usage, see the reference manual for other obtaining types.

In addition to the above method of getting data, there are also:

<? Php
$ Rs = $ db-> prepare ("SELECT * FROM foo ");
$ Rs-> execute ();
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>

Actually, it's almost the same. If you want to obtain the results of a field in a specified record, you can use PDOStatement: fetchColumn ():

<? Php
$ Rs = $ db-> query ("select count (*) FROM foo ");
$ Col = $ rs-> fetchColumn ();
Echo $ col;
?>

FetchColumn () is generally used for count statistics or some records that only need a single field are well-performed.

Briefly summarize the above operations:

Query operations are mainly PDO: query (), PDO: exec (), PDO: prepare (). PDO: query () is mainly used for operations that return record results, especially SELECT operations. PDO: exec () is mainly used for operations that return no result set, for example, INSERT, UPDATE, DELETE, and other operations. the result returned by this operation is 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 used to obtain all the record sets to one. The obtained results can be obtained through 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.

[Error handling]

What if an error occurs in the program? Here we describe the error information and exception handling of the PDO class.

1. object-oriented approach

First, let's take a look at the handling of connection errors, and use the object-oriented method to handle them:

<? 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 the object-oriented exception handling feature of PHP 5. if there is an exception in it, we will initialize and call PDOException to initialize an exception class.

The property structure of the PDOException class:

<? Php
Class PDOException extends Exception
{
Public $ errorInfo = null; // error message, which can be accessed by calling PDO: errorInfo () or PDOStatement: errorInfo ()
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 the PHP 5 built-in exception handling class. let's take a look at the PHP 5 built-in exception handling class structure:

<? Php
Class Exception
{
// Attributes
Protected $ message = 'unknown exception'; // exception information
Protected $ code = 0; // custom exception code
Protected $ file; // file name with an exception
Protected $ line; // The code line number with an exception

// Method
Final function getMessage (); // returns exception information
Final function getCode (); // returns the exception code
Final function getFile (); // returns the file name with an exception
Final function getLine (); // return the code line number in which an exception occurs.
Final function getTrace (); // backtrace () array
Final function getTraceAsString (); // getTrace () information formatted as a string
}
?>

Correspondingly, you can call getFile () and getLine () in the code to locate the error, making debugging easier.

2. process-oriented methods

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 ()! = '000000 '){
Print_r ($ db-> errorInfo ());
Exit;
}
$ Arr = $ rs-> fetchAll ();
Print_r ($ arr );
$ Db = null;
?>

The PDO and PDOStatement objects have the errorCode () and errorInfo () methods. if there are no errors, errorCode () returns: 00000. otherwise, some error codes are returned. An array returned by errorInfo (), including the PHP-defined error code and MySQL error code and error information. the array structure is as follows:

Array
(
[0] => 42S22
[1] => 1054.
[2] => Unknown column 'AAA' in 'Field list'
)

After each query, the results of errorCode () are the latest, so we can easily control the display of error information.

[Summary]

From the above usage, we can see that the PDO function is really powerful, and there are some other features that I have not mentioned, such as binding parameters, preprocessing, stored procedures, transaction processing, and so on. In addition, there are also different data expansion DSN structures. Oracle databases have many special things that need to be learned and understood in depth. This article is just a simple description of some basic knowledge, A simple understanding of PDO.