How to use PDO in PHP _ PHP Tutorial

Use of PDO in PHP. This article does not involve the installation of PDO, but simply summarizes the use of PDO. Start to construct the first PDO application and create a pdo. php file in the Web document directory: [php]? Php $ dsnmysql: hos this article does not involve the installation of PDO, but simply summarizes the use of PDO.

Start 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 = 'lix', gender = 'mail', time = NOW ()");
Echo $ count;
$ Db = null;
?>
$ Dsn = "mysql: host = localhost; dbname = test ";
It is used to construct a DSN (data source). The information in it includes: 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.

$ Count = $ db-> exec ("insert into foo SET name = 'lix', gender = 'mail', 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. 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 ));

If you want to obtain data
[Php]
$ Rs = $ db-> query ("SELECT * FROM foo ");
Foreach ($ rs-> fetch () as $ row ){
Print_r ($ row );
}
?>
You can also use

[Php]
$ Rs = $ db-> query ("SELECT * FROM foo ");
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>
Retrieve all data at a time

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

[Php]
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 );
?>
The setAttribute () method is to set the PDO attribute. the main attributes include: PDO: ATTR_CASE, PDO: ATTR_ERRMODE, and so on. 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 data acquisition method, you can also:

[Php]
$ Rs = $ db-> prepare ("SELECT * FROM foo ");
$ Rs-> execute ();
While ($ row = $ rs-> fetch ()){
Print_r ($ row );
}
?>
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;
?>
Summary:

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]
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 ()."
";
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 ()
}
?>
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
[Php]
$ 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:

[Php]
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.

From Crazy Coding life of Heda Li Xin

Bytes. Start to construct the first PDO application and create a pdo. php file in the Web document directory: [php]? Php $ dsn = mysql: hos...