PDO Learning and use (i): PDO object, Exec method, query method and anti-SQL injection

1. Installing PDO

The database abstraction layer pdo-php The data Object Extension class library defines a lightweight, consistent interface for the PHP Access database, which provides a data access abstraction layer that uses specific PDO driver access for different database servers.

PHP 5.1 or later in Windows environment installs PDO by editing the php.ini file: Remove Extension=php_pdo.dll front;

If the database used is MySQL, load MySQL's PDO driver in the php.ini file:

Add Extension=php_pdo_mysql.dll or remove the sentence from the front;

To view the available PDO drivers, you can write PHP files to view them:

<?  phpinfo

or by printing the pdo_drivers () function to see:

<? PHP Print_r (Pdo_drivers ());

Page display:

Array (    [0] = = MySQL    [1] = Sqlite2)

2. Create a PDO object

The member method in the PDO object is to unify the access interfaces for various databases and to create a PDO object before interacting with the database using PDO. In the PDO construction method, there are 4 parameters:

1. Data source name DSN, used to define a deterministic database and the drivers that must be used

2. Connect the database user name

3. Connect the database password, is optional parameter

4. All the additional options required for the connection are optional parameters

Take MySQL, for example, to create a PDO object:

// Data Source Name (DSN) $dsn = ' mysql:dbname=test;host=127.0.0.1 ';
$user = "root"; $pwd = ""; $opt Array (Pdo::mysql_attr_init_command = "Set names UTF8"); $DBH New PDO ($dsn,$user,$pwd,$opt);

The fourth parameter here indicates that the UTF8 character set is used in the SQL statement sent to the client, and the result of the server being sent back to the client is also in the UTF8 character set.

3. Calling the constructor method

The first method is to embed parameters into the constructor, which is used in the example above:

<?PHP//Data Source Name (DSN)$dsn= ' mysql:dbname=test;host=127.0.0.1 ';$user= "Root";$pwd= "";$opt=Array(Pdo::mysql_attr_init_command = "Set Names UTF8");Try{    $DBH=NewPDO ($dsn,$user,$pwd,$opt);}Catch(pdoexception$e){    Echo"Database connection failed:".$e-GetMessage (); Exit;}

The second method is to store the DSN string in a file, for example, in the dsn.txt of the corresponding directory on the D disk:

<? PHP $user = "root"; $pwd = ""; Try {    $dbhnew PDO (' Uri:file:///d:\\practise\php\pdo\dsn.txt ',$user,$ PWD);} Catch $e {    echo "database connection failed:". ) $e-GetMessage ();}

The URI (Uniform Resource Identifier) is used to locate the file location. Use the PHP statement to get the current URI:

$uri $_server [' Request_uri '];   Echo  $uri;  

The third method is to maintain the DSN information in the PHP server's configuration file.

4. Execute SQL statements

① when a query that does not have a result set, such as INSERT, UPDATE, delete, is executed with the Exec () method of PDO, the number of rows affected is returned after successful execution. This method cannot be used with SELECT queries:

1<?PHP2 3 //Data Source Name (DSN)4 $dsn= ' mysql:dbname=test;host=127.0.0.1 ';5 $user= "Root";6 $pwd= "";7 //4th parameter8 $opt=Array(Pdo::mysql_attr_init_command = "Set Names UTF8");9 Ten Try{ One  A     $DBH=NewPDO ($dsn,$user,$pwd,$opt); -  -}Catch(pdoexception$e){ the  -     Echo"Database connection failed:".$e-getMessage (); -     Exit; - } +  - $query= "Update Archives set title = ' White Nights line ' where title = ' Waltz _3 '";  +  A //use the Exec () method to perform insert,update,delete, etc. at $affected=$DBH-exec($query); -  - if($affected){ -  -     Echo' The number of rows affected in the data table archives is: '.$affected; -  in}Else{ -  to     Print_r($DBH-errorinfo ()); +}

② when executing a select query that returns a result set, or when the number of rows affected does not matter, the query () method in the PDO object should be used, and if the method executes the query successfully, a Pdostatement object is returned, and you can use Pdostatement to The RowCount () method of the elephant gets the number of data rows fetched:

1<?PHP2 3 //Data Source Name (DSN)4 $dsn= ' mysql:dbname=test;host=127.0.0.1 ';5 $user= "Root";6 $pwd= "";7 //4th parameter8 $opt=Array(Pdo::mysql_attr_init_command = "Set Names UTF8");9 Ten Try{ One  A     $DBH=NewPDO ($dsn,$user,$pwd,$opt); -     $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception); -  the}Catch(pdoexception$e){ -  -     Echo"Database connection failed:".$e-getMessage (); -     Exit; + } -  + $query= "SELECT * from archives where title= ' White Nights line '";  A  at Try{ -  -     //executes a select query and returns the Pdostatement object -     $pdostatement=$DBH->query ($query); -     Echo"Get it altogether from the table."$pdostatement->rowcount (). "Record:<br>"; -     foreach($pdostatement  as $row){ in      -         Echo $row[' Aid ']. <br> "; to     } +}Catch(pdoexception$e){ -  the     Echo $e-getMessage (); *}

Page display:

Get 11 records from the table altogether:
540
541
544
547
550
553
556
559
562
565
568

5. Using PDO to filter special characters to prevent SQL injection, you can use the quote () method of PDO :

$query = "SELECT * from user where uname=". $dbh->quote ($_post[' uname ']). "And pwd=". $dbh->quote ($_post[' pwd ']);

Reference: "PHP" 2nd edition

PDO Learning and use (i): PDO object, Exec method, query method and anti-SQL injection