Php PDO rowCount() 返回错误结果

2021-12-26 00:00:00 php mysql pdo

我有一个在树结构数据中获取路径的函数.

I have a function for getting path in a tree structure data.

根据我在数据库中的表，当我查询 sub_id='root_id'

According to my table in DB, root should get no result when I query sub_id='root_id'

当我刚刚通过根时，rowCount 返回正确的结果，即 0.但是，当我从较低等级(第 3 位)传递一个节点时，在递归的末尾，即根，rowCount 返回 1?

When I just pass the root the rowCount return correct result which is 0. However when I pass a node from lower rank(3rd), in the end of the recursive which is root, rowCount return 1?

附注

我使用 Mysql 作为数据库

I use Mysql as DB

这是我的桌子

main_id | sub_id
----------------    
    1   |    2    
    1   |    3    
    2   |    4    
    3   |    5

代码:

$stmt = $conn->prepare("Select * from table where sub_id= ? ");  

function get_path($stmt,$node,&$map){

    $res=$stmt->execute(array($node));

    if(!$res){ throw new Exception( implode(' ',$stmt->errorInfo()),1); }

        echo $node.' found '.$stmt->rowCount().'<br>';

        if($stmt->rowCount()==0){ //root
            $map[]=$node;
        }else{

            foreach($stmt->fetchAll(PDO::FETCH_ASSOC) AS $row){
                $map[]=$node;
                $upper_node=$row['main_id'];
                get_path($stmt,$upper_node,$map);
        }
    }

}

如果我只是通过 get_path($stmt,1,$map);(根)

If I just pass get_path($stmt,1,$map); (the root)

输出:

1 found 0

但是当我例如将 4 传递给它时输出变成:

but when I for example pass 4 into it the output become:

4 found 1
2 found 1
1 found 1 <= it should found 0

为什么?

推荐答案

你不应该依赖 PDOStatement::rowCount() 来获取受 SELECT 语句影响的行数，参见 PHP 手册，PDOStatement::rowCount:

You shouldn't rely on PDOStatement::rowCount() to get the number of rows affected by a SELECT statement, see PHP manual, PDOStatement::rowCount:

DOStatement::rowCount() 返回受影响的行数相应的执行的最后一个 DELETE、INSERT 或 UPDATE 语句PDOStatement 对象.

DOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

如果关联的 PDOStatement 执行的最后一条 SQL 语句是一条 SELECT 语句，某些数据库可能会返回行数该语句返回.但是，不能保证此行为适用于所有数据库，不应依赖于可移植应用程序.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

[...]

示例 #2 对 SELECT 语句返回的行进行计数

Example #2 Counting rows returned by a SELECT statement

对于大多数数据库，PDOStatement::rowCount() 不返回受 SELECT 语句影响的行数.相反，使用PDO::query() 发出一个 SELECT COUNT(*) 语句谓词作为您想要的 SELECT 语句，然后使用PDOStatement::fetchColumn() 来检索将要执行的行数被退回.然后，您的应用程序可以执行正确的操作.

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

相关文章