Query Mysql using the PDO class in php

In my PHP development process, mysql databases are used for database operations. database-related operations are basically using mysql extension functions in php, such as mysql_query and mysql_connect, when using these traditional methods to connect to the query database, I think there are two

In my PHP development process, mysql databases are used for database operations. database-related operations are basically using mysql extension functions in php, such as mysql_query and mysql_connect, when using these traditional methods to connect to and query a database, I think there are two drawbacks: First, there is no scalability, that is, it can only be used in the mysql database. if you want to replace the database, different PHP extension functions are used. if you want to change the database during the development process, all database-related operations will be repeated. The second is if the filter statements are not strict, there is a risk of SQL injection, leading to malicious attacks and loss of control on the website. Although the mysql_real_escape_string () function is used to filter user submitted values, there are also defects. The prepare method extended by PDO in PHP can effectively avoid the risk of SQL injection.

1. PDO introduction

The PDO extension defines a lightweight and consistent interface for PHP to access the database. It provides a data access abstraction layer so that no matter what database is used, you can use consistent functions to query and obtain data. PDO is released with PHP5.1 and can also be used in the PECL extension of PHP5.0, and cannot run in the previous PHP version. Compared with mysql and mysqli, PDO makes cross-database use more friendly.

2. installation and configuration of PDO

In php5.2.10, php has installed pdo by default.

Open the php. ini file, find extension = php_pdo.dll, remove the annotator, and restart apache. If this statement is not found, you can add one by yourself or check whether the system uses a dynamic link library file during installation. so, if yes, you can find a conf in the php directory. d folder, there is a pdo below. ini, if there is an extension = pdo. so indicates that PDO has been enabled.

Verify if PHP has enabled PDO. First, write a script with the content:

If the following content is displayed, the PDO extension is enabled.

3. create a PDO object

_ Construct (string dsn [, string username [, string password [, array driver_options]); // pdo constructor

Parameter resolution: the first required parameter is the data source name DSN, which defines a definite database and a required driver.

For example, the DSN format for connecting the oracle server to the mysql server is as follows:

Ocl: dbname = // 127.0.0.1: 1521/mydb // dsn connecting to the oracle Server. oci: as the driver prefix. host 127.0.0.1, port 1521, database mydb

Mysql: host = 127.0.0.1; dbname = testdb // dsn connecting to the Mysql Server, mysql: as the driver prefix, host 127.0.0.1, database testdb

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';$user = 'root';$password = 'root';try {$dbh = new PDO($dsn, $user, $password);}catch(PDOException $e) {echo "connect failed: ".$e->getMessage();}

4. set properties for PDO

1) There are three error handling methods for PDO:

? PDO: ERrmODE_SILENT does not display error messages, but only sets error codes.
? PDO: ERrmODE_WARNING
? PDO: ERrmODE_EXCEPTION throws an exception

You can use the following statement to set the error handling method to throw an exception.

$dbh->setAttribute(PDO::ATTR_ERrmODE, PDO::ERrmODE_EXCEPTION);

When set to PDO: ERrmODE_SILENT, you can call errorCode () or errorInfo () to obtain the error information. of course, you can also obtain the error information in other cases.

2) because different databases process different names of returned fields in different cases, PDO provides PDO: ATTR_CASE settings (including PDO: CASE_LOWER, PDO: CASE_NATURAL, PDO :: CASE_UPPER) to determine the case sensitivity of the returned field name.

3) specify the value of the NULL value returned by the database in php by setting the PDO: ATTR_ORACLE_NULLS type (including PDO: NULL_NATURAL, PDO: NULL_EmpTY_STRING, PDO: NULL_TO_STRING.

5. common PDO methods and their applications
PDO: query () is mainly used for operations that return records, especially SELECT operations.
PDO: exec () is mainly used for operations that do not return result sets, such as INSERT and UPDATE operations.
PDO: prepare () is mainly a pre-processing operation. you need to run the SQL statement in the pre-processing through $ rs-> execute (). This method can bind a parameter, powerful functions (this is the only option to prevent SQL injection)
PDO: lastInsertId () returns the last insert operation. the primary key column type is the last auto-increment ID of auto-increment.
PDOStatement: fetch () is used to obtain a record.
PDOStatement: fetchAll () is used to obtain all record sets to a set.
PDOStatement: fetchColumn () is a field in the first record specified in the result. the default value is the first field.
PDOStatement: rowCount (): mainly used for the results set affected by the DELETE, INSERT, and UPDATE operations on PDO: query () and PDO: prepare (). For PDO :: the exec () method and SELECT operation are invalid.

6. PDO for MYSQL database instances

$sql = "UPDATE article SET title="haha" WHERE id=1";$affected = $dbh->exec($query);if($affected) {echo "successed";}else {print_r($dbh->errorINdo());}

Preprocessing method:

$ SQLStatament = "insert into article VALUES (": title,: content ")"; $ param = array (": title" => "something ",": content "=>" aaaa "); // prepared parameter, corresponding to the database field $ stmt = $ dbh-> prepare ($ SQLStatement ); $ stmt-> execute ($ param );