How to obtain the number of rows affected by additions, deletions and modifications through SQL aggregate functions?

How to obtain the number of affected rows by additions, deletions and modifications through SQL aggregate functions?

Being able to calculate the number of rows returned or affected by a query before taking further action is usually very useful. This function is especially convenient when you want to display results in pages or generate statistical information. So how is the function of getting the number of affected rows implemented?

1. Count the number of late result records through the SQL aggregation function COUNT

COUNT returns the number of rows of a query or part of a query, usually the same as DINSTINCT use together. SQL's aggregate function COUNT is widely supported by a variety of databases!

2. Return the number of affected rows through the PDOStatement->rowCount() method

Obtained by the PDOStatement->rowCount() method How many rows are affected by UPDATE, INSERT or DELECTE queries. The rowCount() method is not common in typical PHP applications, but it can count how many rows were affected after calling PDOStatement->execute.

3. Application Example

Use the SQL aggregate function COUNT to count the number of records returned by the query. The specific code is as follows:

<form action="5.php" name="form1" method="post">
    查询关键字：<input type="text" name="username" id="nr">
    <input type="submit" name="Submit" value="查询">
</form>
<?php
header("Content-Type:text/html; charset=utf-8");    //设置页面的编码格式
if(isset($_POST[&#39;Submit&#39;]) && $_POST[&#39;Submit&#39;]=="查询") {
    $dbms = "mysql";                                  // 数据库的类型
    $dbName ="php_cn";                                //使用的数据库名称
    $user = "root";                                   //使用的数据库用户名
    $pwd = "root";                                    //使用的数据库密码
    $host = "localhost";                              //使用的主机名称
    $dsn = "$dbms:host=$host;dbname=$dbName";
    try {
        $dbh = new PDO($dsn, $user, $pwd);//初始化一个PDO对象，就是创建了数据库连接对象$pdo
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = "SELECT COUNT(*) FROM user WHERE username = :id";//需要执行的sql语句
        $stmt = $dbh->prepare($sql);//准备查询语句
        $stmt->bindParam(&#39;:id&#39;, $_POST[&#39;nr&#39;], PDO::PARAM_STR);            //执行查询语句，并返回结果集
        $result = $stmt->execute();
        echo &#39;There are&#39;,$stmt->fetchColumn(),&#39;rows returned.&#39;;
    }catch (PDOException $e){
        echo &#39;PDO Exception Caught.&#39;;
        echo &#39;Error with the database:<br>&#39;;
        echo &#39;SQL Query:&#39;,$sql;
        echo &#39;Error:&#39;.$e->getMessage();
    }
}
?>

Run this example, enter the name of the database to be queried in the text box, click the "Query" button, and the number of rows of the queried results will be displayed, as shown below:

Note:

When using UPDATE and DELETE statements, be sure to add a WHERE clause at any time. If the WHERE clause is not added, all columns in the database may be updated or deleted from the table. All the data is not what the user wants to see in any case.

The above is the detailed content of How to obtain the number of rows affected by additions, deletions and modifications through SQL aggregate functions?. For more information, please follow other related articles on the PHP Chinese website!