sqlite uses PDO to execute SQL statements exec(), query()

In PHP scripts, executing SQL queries to interact with the database through PDO can be divided into three different strategies. Which method to use depends on what operations you want to do.

1. Use the PDO::exec() method

When executing queries without result sets such as INSERT, UPDATE, and DELETE, use the exec() method in the PDO object to execute them.该方法成功执行后，将返回受影响的行数。 Note that this method cannot be used for SELECT queries. An example looks like this:

<?php
    try {
        $pdo = new PDO ('mysql:host=localhost;dbname=testdb','root','123');
        $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
        $pdo->exec('set names "utf8"');
    } catch (PDOException $e) {
        exit("数据库连接失败: ".$e -> getMessage());
    }

    $sql = "UPDATE contactInfo SET phone='15801680168' where name='高某某'";

    //使用exec（）方法可以执行INSERT、UPDATE、DELETE等

    $affected = $pdo->exec($sql);

    if ($affected) {
        echo "数据表中受影响的行数为： ".$affected;
    } else {
        print_r($pdo->errorInfo());
    }
    
 ?>

2. Use the PDO::query() method

When executing a SELECT query that returns a result set, or when the number of rows affected is not important, the query() method on the PDO object should be used. If the method successfully executes the specified query, it returns a PDOStatement object. If you use the query() method and want to know the total number of data rows obtained, you can use the rowCount() method in the PDOStatement object to obtain it. Sample code looks like this:

 <?php  
     try {
        $pdo = new PDO ('mysql:host=localhost;dbname=testdb','root','123');
        $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
        $pdo->exec('set names "utf8"');
    } catch (PDOException $e) {
        exit("数据库连接失败: ".$e -> getMessage());
    }
    $sql = "SELECT name, phone, email FROM contactInfo WHERE departmentId='D01'";
    try {
        //执行SELECT查询，并返回PDOstatement对象　　
        $pdostatement = $pdo->query("$sql");
        echo "一共从表中获取到".$pdostatement->rowCount()."条记录：n";
        //利用循环输出
        foreach ($pdostatement as $row) {
            echo $row['name'] . "t";           //输出从表中获取到的联系人的名字
            echo $row['phone'] . "t";　　　　　  //输出从表中获取到的联系人的电话
            echo $row['email'] . "n";　　　　　  //输出从表中获取到的联系人的电子邮件
        }
    } catch (PDOException $e) {
        echo $e->getMessage();　
    }
 ?>

3. Use the two methods PDO::prepare() and PDOStatement::execute()

When the same query needs to be executed multiple times (sometimes iteratively passing in different column values), it will be more efficient to use prepared statements. To use prepared statements, you need to use the prepare() method in the PDO object to prepare a query to be executed, and then use the execute() method in the PDOStatement object to execute it.