PHP Development Notes Series (1)-PDO Use_PHP Tutorial

Some time ago, I started researching PHP. I read some information about PDO and found it to be good. I organized and summarized it as development notes for future use. "PHP Development Notes Series (1) - PDO use".

PDO is the abbreviation of PHP Data Objects, which is a database access abstraction layer. PDO is a consistent interface for multiple databases. By analogy, what PDO does is similar to the function of the persistence layer framework (Hibernate, OpenJPA) in JAVA, providing a unified programming interface for heterogeneous databases, so that there is no need to use functions such as mysql_* and pg_*. No more writing your own "GenericDAO". PDO was released with PHP5.1, so the PHP5.2 and PHP5.3 we use are already available.

For convenience, we use MySQL5 for demonstration.

0. Establish experimental environment database and related tables

Sql code

1. CREATE TABLE `blog` (
2. `id` int(10) NOT NULL AUTO_INCREMENT,
3. `title` varchar(255) NOT NULL,
4. PRIMARY KEY (`id`)
5. ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

1. Use PDO to access the database
The steps to access the database through PDO are: a) specify dsn, username, password, b) construct the PDO object through the settings in #a, The code is as follows:

Php code

1. file:pdo-access.php
2. url:http://localhost:88/pdo/pdo-access.php
4. // Set dsn, username, passwd
5. $dsn = 'mysql:host=localhost;dbname=pdotest';
6. $username = 'root';
7. $passwd = 'password';
8.  
9. // Construct PDO object
10. try {
11. $dbh = new PDO($dsn, $username, $passwd);
12.  echo 'connect to database successfully!';
13. } catch (Exception $e) {
14. echo 'Fail to connect to database!n';
15. echo $e->getMessage();
16.  } 
17. ?>

Note: DSN is Data Source Name-data source name, which provides database connection information and includes three parts: PDO driver name (MySQL, SQLite, PostgreSQL, etc.), colon and driver-specific syntax. But under normal circumstances, it is difficult for us to remember these. You can download a php manual to check, or you can check it on the official website of php.

2. Use Query method to query data
Based on #1, after successfully connecting to the database, construct a SQL statement, call the query method to return the structure array, and use foreach To traverse the data results, the code is as follows:

Php代码  

1. file:pdo-query.php  
2. url:http://localhost:88/pdo/pdo-query.php?title=title1  
4.   
5.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
6.     $username = 'root';  
7.     $passwd = 'password';  
8.       
9.     try {  
10.         $dbh = new PDO($dsn, $username, $passwd);  
11.         echo 'connect to database successfully!'."rn";  
12.       
13.         $title = 'title1';  
14.         // 构造SQL语句  
15.         $sql = "SELECT * FROM blog WHERE title = '".$title."'";  
16.         // 执行查询并遍历结果  
17.         foreach ($dbh->query($sql) as $row){  
18.             print $row['id']."t";  
19.             print $row['title']."t";  
20.         }  
21.     } catch (PDOException $e) {  
22.         echo 'Errors occur when query data!n';  
23.         echo $e->getMessage();  
24.     }  
25. ?>  

    备注：一般情况下， 通过构造SQL语句的方法来进行query、update、insert、delete，都会需要指定where条件，因此不可避免的需要防止SQL注入的问题出现。 

    例如，正常情况下，当用户输入“title1”时，我们构造的sql语句会是SELECT * FROM blog WHERE title='title1'，但是对SQL比较熟悉的用户会输入'OR id LIKE '%，此时我们构造的SQL就会变成SELECT * FROM blog where title='' OR id LIKE '%'，这样整张blog 表中的数据都会被读取，因此需要避免，所以需要用到quote方法，把所有用户提供的数据进行转移，从而防止SQL注入的发生。使用quote方法后的sql为$sql = "SELECT * FROM blog WHERE title = ".$dbh->quote($title)，转移出来后的sql是SELECT * FROM blog WHERE title = ''OR id LIKE '%'，把所有的单引号（'）都转移了。 

3. 使用prepare和execute方法查询数据 
    如果我们用到的SQL查询是使用频率不高的查询，那么使用query或prepare和execute方法来查询都无太大差别，查询速度也不会差太远。两者不同的是，使用query时，php向数据库发送的sql，每执行一次都需要编译一次，而使用prepare和execute方法，则不需要，因此做大并发量的操作时，使用prepare和execute方法的优势会更加明显。 

There are not many steps to use the prepare and execute methods. a) Construct the SQL, b) Pass the SQL into the PDO->prepart method to get a PDOStatement object, 3) Call the execute method of the PDOStatement object, 4) Pass the PDOStatement->fetch Or PDOStatement->fetchObject to traverse the result set. The code is as follows:

Php code

1. file:pdo-prepare-fetch.php
2. url:http://localhost:88/pdo/pdo-prepare-fetch.php?title=title1
4. $dsn = 'mysql:host=localhost;dbname=pdotest';
5. $username = 'root';
6. $passwd = 'password';
7.  
8. // Get the title parameter value from the request
9. $title = $_GET['title'];
10. try {
11. $dbh = new PDO($dsn, $username, $passwd);
12.  echo 'connect to database successfully!'."
    ";
13.  
14.  // Construct SQL statement and use bind variables
15.  $sql = "SELECT * FROM blog WHERE title = :title";
16.  //Compile SQL 
17. $stmt = $dbh->prepare($sql);
18.  //Assign a value to the bind variable 
19.  $stmt->bindParam(":title", $title, PDO::PARAM_STR); 
20.  //Execute SQL 
21. $stmt->execute();
22.  // Get the result as an associative array and traverse the result
23.  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
24. print $row['id']."t";
25. print $row['title']."t";
26. }  
27. } catch (PDOException $e) {
28. echo 'Errors occur when query data!n';
29. echo $e->getMessage();
30.  } 
31. ?>

In addition to using the above PDO::FETCH_ASSOC to return the associative array, you can also use the fetchObject method to return the result set object. The code is as follows:

Php代码  

1. file:pdo-prepare-fetch-object.php  
2. url:http://localhost:88/pdo/pdo-prepare-fetch-object.php?title=title1  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.     $username = 'root';  
6.     $passwd = 'password';  
7.       
8.     $title = $_GET['title'];  
9.     try {  
10.         $dbh = new PDO($dsn, $username, $passwd);  
11.         echo 'connect to database successfully!'."
    ";  
12.       
13.         $sql = "SELECT * FROM blog WHERE title = :title";  
14.         $stmt = $dbh->prepare($sql);  
15.         $stmt->bindParam(":title", $title, PDO::PARAM_STR);  
16.         $stmt->execute();  
17.         // 以对象数组方式获取结果，并遍历结果       
18.         while ($row = $stmt->fetchObject()) {  
19.             print $row->id."t";  
20.             print $row->title."t";  
21.         }  
22.     } catch (Exception $e) {  
23.         echo 'Errors occur when query data!n';  
24.         echo $e->getMessage();  
25.     }  
26. ?>  

4. 设置PDO的错误级别 
    PDO的错误级别分成PDO::ERRMODE_SILENT（默认）、PDO::ERRORMODE_WARNING、PDO::ERRORMODE_EXCEPTION三种。 
    PDO::ERRMODE_SILENT级别，当出现错误时，会自动设置PDOStatement对象的errorCode属性，但不进行任何其他操作，因此需要我们手工检查是否出现错误（使用empty($stmt->errorCode())），否则程序将继续走下去。 
    PDO::ERRORMODE_WARNING级别，基本与PDO::ERRMODE_SILENT一致，都是需要使用empty($stmt->errorCode())手工检查。 
    只需要在创建PDO对象后，加入以下代码即可：$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);或$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); 
    PDO::ERRORMODE_WARNING级别，当出现错误时，系统将抛出一个PDOException，并设置errorCode属性，程序可以通过try{...}catch{...}进行捕捉，否则未catch的exception会导致程序中断，加入以下代码即可：$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

Php code

2.  … 
3. try {
4.  … 
5. } catch (Exception $e) {
6.  echo 'Errors occur when operation!'."
   ";
7. // Get Exception information
8.  echo $e->getMessage()."
   ";
9.  //Get error code 
10.  echo $e->getCode()."
    ";
11. // Get the error file name
12.  echo $e->getFile()."
    ";
13.  //Get the error line
14.  echo $e->getLine()."
    ";
15.  //Return the exception as a string 
16. echo $e->getTraceAsString();
17.  } 
18. ?>

5. Use prepare and execute methods to insert/update data
The method is similar to the query in #3, except that the constructed SQL statement is an insert statement or update statement. The code is as follows :

Php代码  

1. file:pdo-prepare-insert.php  
2. url:http://localhost:88/pdo/pdo-insert.php?title=title11  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.       
6.     $username = 'root';  
7.     $passwd = 'password';  
8.       
9.     $title = $_GET['title'];  
10.     try {  
11.         $dbh = new PDO($dsn, $username, $passwd);  
12.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
13.         echo 'connect to database successfully!'."
    ";  
14.       
15.         // 构造Insert语句  
16.         $sql = "INSERT INTO blog(title) VALUES(:title)";  
17.         $stmt = $dbh->prepare($sql);  
18.         $stmt->bindParam(":title", $title);  
19.         $stmt->execute();  
20.     } catch (Exception $e) {  
21.         echo 'Errors occur when query data!n';  
22.         echo $e->getMessage();  
23.     }  
24. ?>  

Php代码  

1. file:pdo-prepare-update.php  
2. url:http://localhost:88/pdo/pdo-update.php?id=1&title=title12  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.       
6.     $username = 'root';  
7.     $passwd = 'password';  
8.       
9.     $id = $_GET['id'];  
10.     $title = $_GET['title'];  
11.     try {  
12.         $dbh = new PDO($dsn, $username, $passwd);  
13.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
14.         echo 'connect to database successfully!'."
    ";  
15.       
16.         // 构造update语句  
17.         $sql = "UPDATE blog SET title=:title where id=:id";  
18.         $stmt = $dbh->prepare($sql);  
19.         $stmt->bindParam(":id", $id);  
20.         $stmt->bindParam(":title", $title);  
21.         $stmt->execute();  
22.     } catch (Exception $e) {  
23.         echo 'Errors occur when query data!n';  
24.         echo $e->getMessage();  
25.     }  
26. ?>  

6. 获取返回的行数 
    使用#3中的prepare和execute方法，然后将sql语句改成count的，例如SELECT COUNT(id) FROM article ...，代码如下： 

Php代码  

1. file:pdo-prepare-fetch-column.php  
2. url:http://localhost:88/pdo/pdo-prepare-fetch-column.php?id=1&title=title12  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.     $username = 'root';  
6.     $passwd = 'password';  
7.       
8.     try {  
9.         $dbh = new PDO($dsn, $username, $passwd);  
10.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);  
11.         echo 'connect to database successfully!'."
    ";  
12.       
13.         // 构造count语句  
14.         $sql = "SELECT COUNT(id) FROM blog";  
15.         $stmt = $dbh->prepare($sql);  
16.         $stmt->execute();  
17.         // 使用fetchColumn获取0列值  
18.         echo $stmt->fetchColumn()." rows returned!";  
19.     } catch (Exception $e) {  
20.         echo 'Errors occur when query data!n';  
21.         echo $e->getMessage();  
22.     }  
23. ?>  

7. 获取受影响的行数 
    使用#3中的prepare和execute方法，然后将SQL语句改成insert、update、delete语句即可，代码如下： 

Php代码  

1. file:pdo-prepare-row-count.php  
2. url:http://localhost:88/pdo/pdo-prepare-row-count.php?id=1  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.     $username = 'root';  
6.     $passwd = 'password';  
7.       
8.     $id = $_GET['id'];  
9.     try {  
10.         $dbh = new PDO($dsn, $username, $passwd);  
11.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);  
12.         echo 'connect to database successfully!'."
    ";  
13.       
14.         $sql = "DELETE FROM blog WHERE id=:id";  
15.         $stmt = $dbh->prepare($sql);  
16.         $stmt->bindParam(":id", $id);  
17.         $stmt->execute();  
18.         // 获取update、insert、delete操作后影响的行数  
19.         echo $stmt->rowCount()." rows affected!";  
20.     } catch (Exception $e) {  
21.         echo 'Errors occur when data operation!n';  
22.         echo $e->getMessage();  
23.     }  
24. ?>  

8. 获得新插入行的ID值 
    为数据库表插入新数据行时，我们需要获得刚刚插入的新行的ID值，此时我们需要使用到PDO的lastInsertId()方法，代码如下： 

Php代码  

1. file:pdo-prepare-last-insertid.php  
2. url:http://localhost:88/pdo/pdo-prepare-last-insertid.php?title=title13  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.       
6.     $username = 'root';  
7.     $passwd = 'password';  
8.       
9.     $title = $_GET['title'];  
10.     try {  
11.         $dbh = new PDO($dsn, $username, $passwd);  
12.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
13.         echo 'connect to database successfully!'."
    ";  
14.       
15.         $sql = "INSERT INTO blog(title) VALUES(:title)";  
16.         $stmt = $dbh->prepare($sql);  
17.         $stmt->bindParam(":title", $title);  
18.         $stmt->execute();  
19.         // 获取上一个之行的insert语句插入的数据的id值  
20.         echo $dbh->lastInsertId();  
21.     } catch (Exception $e) {  
22.         echo 'Errors occur when query data!n';  
23.         echo $e->getMessage();  
24.     }  
25. ?>  

9. 使用PDO进行事务管理 
    事务是进行程序开发时，保证数据ACID（可分性、一致性、独立性、持久性）的工具。要不全部成功，要不全部不成功，这样才能保证关联数据的保存能够达到预期的目的。下面使用PDO的Transaction来进行实验，进行多比数据插入，开启事务，第一句sql是可以正常插入，第二句sql插入出错，检查是否rollback。 

Php代码  

1. file:pdo-prepare-transaction.php  
2. url:http://localhost:88/pdo/pdo-prepare-transaction.php  
4.     $dsn = 'mysql:host=localhost;dbname=pdotest';  
5.       
6.     $username = 'root';  
7.     $passwd = 'password';  
8.       
9.     try {  
10.         $dbh = new PDO($dsn, $username, $passwd);  
11.         $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
12.         echo 'connect to database successfully!'."
    ";  
13.         // 开启事务  
14.         $dbh->beginTransaction();  
15.         $sql = "INSERT INTO blog(title) VALUES(:title)";  
16.         $stmt = $dbh->prepare($sql);  
17.         $stmt->execute(array(':title'=>'insert title1'));  
18.         $stmt->execute(array(':title'=>NULL));  
19.         // 提交事务  
20.         $dbh->commit();  
21.     } catch (Exception $e) {  
22.         echo 'Errors occur when data operation!n';  
23.         echo $e->getMessage();  
24.         // 回滚事务  
25.         $dbh->rollBack();  
26.     }  
27. ?>  

10. 使用PDO进行数据库备份 
    使用system函数，将我们构造的mysqldump命令传入即可。下面为了演示，只做了简单的调用。 

Php code

1. file:pdo-backup.php
2. url:http://localhost:88/pdo/pdo-backup.php
4. $username="root";
5. $passwd="password";
6. $dbname="pdotest";
7.  $file='d:/'.$dbname.'.sql'; 
8. //Construct backup command
9.  $cmd = "mysqldump -u".$username." -p".$passwd." ".$dbname. " >".$file; //Execute backup command system($cmd,$error);
10. if($error){
11. trigger_error("backup failed".$error);
12.  }  ?>
13. Adopt factory mode: Php code
    1. file:AbstractMySQLDump.php
    3. require_once 'MySQLDump_Win.php';
    4.  
    5. abstract class AbstractMySQLDump {
    6. protected $cmd;
    7.  
    8. abstract function __construct($username, $passwd, $dbname, $file);
    9.  
    10. // According to the operating system type, use the factory method to construct the backup class
    11. public static function factory($username, $passwd, $dbname, $file){
    12.  if(strtoupper(substr(PHP_OS, 0, 3))==='WIN'){
    13.            return new MySQLDump_Win($username, $passwd, $dbname, $file); 
    14. }else{
    15. // implement MySQLDump_NIX($username, $passwd, $dbname, $file);
    16.                                                                                               
    }  
     
     
    19. //Backup Logic 
    20. public function backup(){
    system(
    21. $this->cmd, $error);
    > // Determine whether there are errors and error logic
    22.  
    if(
    23. $error){            trigger_error(
    "backup failure! command:".
    24. $this->cmd." Error:".$error);                                                                                                
    }  
     } 
    ?>

    Php code

    1. file:MySQLDump_Win.php
    3. class MySQLDump_Win extends AbstractMySQLDump {
    4.                                                 
     
    5. //Override the constructor method of the parent class 
     
    6. public function __construct($username, $passwd, $dbname, $file){ 
         
    7. $this->cmd = "mysqldump -u".$username." -p".$passwd." ".$dbname." > ".$file;
    }  
     } 
    ?>

    
    
    Php code

    
    file:MySQLDumpTest.php
    url:http:
    2. //localhost:88/pdo/MySQLDumpTest.php
14. require_once 'AbstractMySQLDump.php';
 
16. $username = "root";
17. $passwd = "password";
18. $dbname = "pdotest";
19. $file = "d:/".$dbname.".sql";
 
21. //Use factory method to generate backup class
22. $dump = AbstractMySQLDump::factory($username, $passwd, $dbname, $file);
23. //Execute the backup method of the backup class
24. $dump->backup();
?>

http://www.bkjia.com/PHPjc/820779.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/820779.htmlTechArticleSome time ago, I started researching PHP. I read some information about PDO and found it to be good. I organized and summarized it. Let's take it as a development note for future use, "PHP Development Notes Series...