PHP database learning: How to use PDO to execute SQL statements?

In the previous article, I brought you "PHP database learning: How to use PDO to connect to the database?" ", which gives you a detailed introduction to how to connect to the database through PDO in PHP. In this article, we will continue to look at how to use PDO to execute SQL statements in PHP. I hope everyone has to help!

In the previous article, we have learned how PHP connects to the database through PDO. To connect to the database, you must execute SQL statements. In PDO, we can use three ways to execute SQL statements, namely exec() method, query() method, and prepared statement prepare() and execute() methods. Then let’s take a look together.

<strong><span style="font-size: 20px;">exec() </span></strong>Method

When we execute INSERT , UPDATE and DELETE and other SQL statements that do not need to return a result set, we can use the exec() method in the PDO object. After successful execution using this method, the returned result will be affected. The number of lines, the syntax format of the

exec() method is as follows:

PDO::exec(string $sql)

It should be noted that:

• $ sql is the SQL statement to be executed.

• exec() The method will not obtain the corresponding results from the SELECT query statement.

Then we try to add a piece of data to the database through an example. The example is as follows:

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = "insert into user(name,age,sex) values(&#39;壹壹&#39;,&#39;21&#39;,&#39;男&#39;)";
        $res = $pdo -> exec($sql);
        if($res) echo &#39;成功添加 &#39;.$res.&#39; 条数据！&#39;;
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

Output result:

As can be seen from the above example, we successfully added a piece of data to the database through the exec() method, and the returned result is the number of affected rows. If you want to return an object, you can use the query() method. Next, let's look at another way to execute SQL statements: query() method.

<strong><span style="font-size: 20px;">query() </span></strong>Method

In the above example The exec() method can return these statement information that do not need to return a result set. When executing a SELECT query statement that needs to return a result set, we need to pass the query() statement. If this method is executed successfully, the repentant home country is 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.

The syntax format of the query() method is as follows:

PDO::query(string $sql)
PDO::query(string $sql, int $PDO::FETCH_COLUMN, int $colno)
PDO::query(string $sql, int $PDO::FETCH_CLASS, string $classname, array $ctorargs)
PDO::query(string $sql, int $PDO::FETCH_INTO, object $object)

What needs to be noted is:

$sql is required The executed SQL statement; the remaining parameters are used to set the default fetch mode for the statement, which is equivalent to calling the result object PDOStatement::setFetchMode() method.

Then we use the query() method to query the data we added earlier. The example is as follows:

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = "SELECT * FROM user WHERE name=&#39;壹壹&#39;";
        $res = $pdo -> query($sql,PDO::FETCH_ASSOC);
        print_r($res);
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

Output result:

The use of query() and exec() methods has the following points to note:

• Both query() and exec() can execute all SQL statements, but the return values ​​are different;

• query() can realize all the functions of exec();

• When the select statement is applied to exec(), 0 is always returned;

• If you want to see the specific results of the query, you can complete the loop output through the foreach statement .

<strong>##prepare()<span style="font-size: 20px;"></span></strong> and execute() methods

When it is necessary to pass in different parameters iteratively, that is, when the same query needs to be executed multiple times, using prepared statements will make the implementation more efficient. Use To prepare a statement, 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. Then let's take a look at the prepare() and execute() methods.

The syntax format of the prepare() method is as follows:

PDO::prepare(string $statement[, array $driver_options = array()])

It should be noted that:

• $statement represents It must be a SQL statement template that is valid for the target database;

• $driver_options represents an optional parameter, which is an optional parameter of array type and contains one or more Key-value pairs to set properties for the returned PDOStatement object.

The syntax format of the execute() method is as follows:

PDOStatement::execute([array $input_parameters])

What needs to be noted is:

• 参数 $input_parameters 为一个元素个数和将被执行的 SQL 语句中绑定的参数一样多的数组。

• SQL 语句模板中可以包含零个或多个参数占位标记，格式可以是命名（:name）或问号（?）的形式，当它执行时将用真实数据取代。

• 在同一个 SQL 语句里，命名和问号形式不能同时使用，只能选择其中一种参数形式。如果使用命名形式的占位标记，那么标记的命名必须是唯一的。

接下来我们看一下使用命名形式的参数占位符，查询指定的 SQL 语句，示例如下：

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = "SELECT name,age,sex FROM user WHERE age = :age";
        $sth = $pdo -> prepare($sql);
        $sth -> execute([&#39;:age&#39;=>11]);
        $res1 = $sth -> fetchAll();
        $sth -> execute(array(&#39;:age&#39;=>14));
        $res2 = $sth -> fetchAll();
        echo &#39;<pre class="brush:php;toolbar:false">&#39;;
        print_r($res1);
        print_r($res2);
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

输出结果：

上述示例是使用命名形式的参数占位符，查询指定的 SQL 语句，接下来我们看一下使用问号形式的参数占位符，查询指定的 SQL 语句。示例如下：

<?php
    $dsn  = &#39;mysql:host=127.0.0.1;dbname=test&#39;;
    $user = &#39;root&#39;;
    $pwd  = &#39;root&#39;;
    try{
        $pdo = new PDO($dsn,$user,$pwd);
        $sql = "SELECT name,age,sex FROM user WHERE age = ? AND sex = ?";
        $sth = $pdo -> prepare($sql);
        $sth -> execute([12,&#39;男&#39;]);
        $res1 = $sth -> fetchAll();
        $sth -> execute(array(11,&#39;女&#39;));
        $res2 = $sth -> fetchAll();
        echo &#39;<pre class="brush:php;toolbar:false">&#39;;
        print_r($res1);
        print_r($res2);
    }catch(PDOException $e){
        echo &#39;数据库连接失败：&#39;.$e -> getMessage();
    }
?>

输出结果：

由此我们便通过使用问号形式的参数占位符，查询指定的 SQL 语句。

大家如果感兴趣的话，可以点击《PHP视频教程》进行更多关于PHP知识的学习。

The above is the detailed content of PHP database learning: How to use PDO to execute SQL statements?. For more information, please follow other related articles on the PHP Chinese website!