Use PDO class query mysql_php tutorial in PHP

In my PHP development process, the database is used in the MySQL database, and the database-related operations are basically using the MySQL extension functions in PHP, such as Mysql_query,mysql_connect, and other functions, Using these traditional methods to connect the query database, the personal feel there are two drawbacks, one is not extensibility, is only used in the MySQL database, if you want to replace the database, the use of PHP extension function is different, if in the development process to replace the database, Then all the database-related operations will be repeated, the second is that if the filter statement is not strict, there will be the risk of SQL injection, resulting in malicious attacks on the site, loss of control. Although user-submitted values are filtered using the mysql_real_escape_string () function, they are also defective. The Prepare method of using the PDO extension of PHP can effectively avoid the SQL injection risk.

1. PDO Introduction

The PDO extension defines a lightweight, consistent interface for the PHP Access database, which provides a data access abstraction layer so that queries and data can be executed through a consistent function, regardless of the database used. PDO is released with PHP5.1 and can be used in the PHP5.0 pecl extension and cannot be run in previous versions of PHP. Compared to MySQL and mysqli, PDO makes cross-database usage more approachable.

2. Installation and configuration of PDO

In php5.2.10, PHP already has PDO installed by default.

Open the php.ini file, find Extension=php_pdo.dll This sentence, remove the previous comment symbol, and then restart Apache. If you do not find this sentence, you can add a sentence yourself or see if the system is installed with a dynamic link library file. So, if so, you can find a conf.d folder under the PHP directory, there is a pdo.ini link file, if there is a sentence extension= Pdo.so indicates that PDO has been turned on.

Verify that PHP has the PDO turned on, and first write a script that reads

If the results shown below indicate that the PDO extension has been turned on.

3. Create a PDO object

__construct (string dsn[,string username [, string password [, array driver_options]]);//pdo Construction method

Parameter resolution: The first required parameter is the data source name DSN, which defines a deterministic database and the drivers that must be used.

For example, the DSN format that connects the Oracle server and the MySQL server is as follows:

Ocl:dbname=//127.0.0.1:1521/mydb//DSN,OCI for connecting Oracle server: As driver prefix, host 127.0.0.1, Port 1521, database MyDB

MYSQL:HOST=127.0.0.1;DBNAME=TESTDB//connection to MySQL server dsn,mysql: As 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. PDO Settings Properties

1) PDO has three kinds of error handling methods:

? Pdo::errmode_silent do not display error message, only set error code
? Pdo::errmode_warning Display warning Error
? Pdo::errmode_exception throws an exception

You can set the error handling to throw an exception by using the following statement

$DBH->setattribute (Pdo::attr_errmode, pdo::errmode_exception);

When set to Pdo::errmode_silent, you can get an error message by calling ErrorCode () or errorinfo (), and of course in other cases.

2) because different databases have different case handling for returning field names, PDO provides pdo::attr_case settings (including Pdo::case_lower,pdo::case_natural,pdo::case_upper), To determine the case of the returned field name.

3) by setting the Pdo::attr_oracle_nulls type (including pdo::null_natural,pdo::null_empty_string,pdo::null_to_ STRING) to specify the value that the null value returned by the database corresponds to in PHP.

5. Pdo common method and its application
Pdo::query () is primarily used for operations that have logged results returned, especially the select operation
Pdo::exec () is primarily for operations returned without a result set, such as INSERT, update, and so on
PDO::p Repare () is primarily a preprocessing operation that requires $rs->execute () to execute the SQL statements in the preprocessing, which can bind parameters and be powerful (preventing SQL injection)
Pdo::lastinsertid () returns the last insert operation, the primary key column type is the final self-increment ID
Pdostatement::fetch () is used to get a record
Pdostatement::fetchall () is to get all recordsets to a collection
Pdostatement::fetchcolumn () is a field that gets the result that specifies the first record, the default is the first field
Pdostatement::rowcount (): Mainly for Pdo::query () and PDO::p Repare () The result set affected by the delete, INSERT, update operation, to Pdo::exec () Method and select operation are not valid.

6. PDO operation MySQL Database instance

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

Pretreatment method:

$SQLStatament = "INSERT into article VALUES (": Title,: Content "); $param = Array (": title "=" Something ",": Content "=&G T "AAAA");//prepared parameters, corresponding database fields $stmt = $dbh->prepare ($SQLStatement); $stmt->execute ($param);

http://www.bkjia.com/PHPjc/621626.html www.bkjia.com true http://www.bkjia.com/PHPjc/621626.html techarticle in my PHP development process, the database is used by the MySQL database, database-related operations are basically using the MySQL extension functions in PHP, such as Mysql_query,mysql_co ...

