php & pdo & mysql 计数返回 0

Question

我现在已经花了几个小时却一无所获。我想显示查询中的行数，这是我的代码:

 <?php 

        $user_id = 1;


        $statement = $dbConn->query("select badoo_interest.*, badoo_category.icon_class from badoo_interest, badoo_user_interest, badoo_category where badoo_user_interest.interest_id = badoo_interest.id and badoo_user_interest.user_id = :id_user_profile and badoo_category.id = badoo_interest.category_id");
        $statement->execute(array(':id_user_profile'=> $id_user_profile));


        $rResult = $statement->fetchAll();
        echo count($rResult);
        echo "<h2>Interesi</h2>";
        while( $row = $statement->fetch(PDO::FETCH_ASSOC) ){

            ?>
                <div class="interest" id = "int<?php echo $row['id'];?>">
                    <span class = "intr-ico <?php echo $row['icon_class'];?>"></span>
                    <?php echo $row['name'];?>
                </div>
            <?php
        }


    ?>

我得到了第 10 名，但现在我没有显示任何兴趣。如果您现在使用此代码:

<?php 

    //$user_id = 1;


        $statement = $dbConn->query("select badoo_interest.*, badoo_category.icon_class from badoo_interest, badoo_user_interest, badoo_category where badoo_user_interest.interest_id = badoo_interest.id and badoo_user_interest.user_id = :id_user_profile and badoo_category.id = badoo_interest.category_id");
        $statement->execute(array(':id_user_profile'=> $id_user_profile));
        echo "<p>"  . $statement->rowCount(). " interests:</p>";


        while( $row = $statement->fetch(PDO::FETCH_ASSOC) ){

            ?>
                <div class="interest" id = "int<?php echo $row['id'];?>">
                    <span class = "intr-ico <?php echo $row['icon_class'];?>"></span>
                    <?php echo $row['name'];?>
                </div>
            <?php
        }


    ?>

现在我显示了所有兴趣，但它很重要:0 个兴趣。我可以计算兴趣的数量吗？

Answer 1

如果您阅读 PDOStatement::rowCount 的文档你会看到这一行

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.

解决方法是像您一样从 php 调用 count() 函数。如果这不起作用，则使用 COUNT(*) 作为 result_count。然后当您获取时读取该值。

<小时/>

And i getting number 10 but now im not getting any interests displayed

问题

结果未显示的原因是您已经提取了所有行，因此无法再次使用 fetch()。

解决方案

使用 fetchAll() 返回的数组，并使用 foreach

$rows = $statement->fetchAll();
echo "<p>"  . count($rows). " interests:</p>";

foreach ($rows as $row){
//echo $row['name']
}