0

Hi

I am trying to get count based on the select query using prepare. But i am unable to get the count.

Please help to fix the issue.

PDO function

public function getDistictCountRows($filname,$tablen,$condition){
            echo $sql = "select $filname FROM $this->db_name.$tablen where $condition" ;
            $query = $this->conn->prepare($sql);
            try {
                    $query->execute();
            }catch(PDOException $e) {
                    die($e->getMessage());
                }
             echo $row_count = $query->rowCount();

        }

code

$Users = new Users($db);                                                
 $query = $Users->getDistictRows('DISTINCT(SHUSER)','FQ64001','ORDER BY SHUSER ASC');
 $i=1;
 while($row_stream = $query->fetch(PDO::FETCH_ASSOC)) {
 extract($row_stream);
$SHUSER1 = trim($SHUSER);
$c1 = "SHUSER='$SHUSER1' and SHYQ64YN = 'Y'";
$active_count = $Users->getDistictCountRows('*','FQ64001',$c1);
3
Contributors
4
Replies
10
Views
8 Months
Discussion Span
Last Post by cereal
1

rowCount shouldn't be used for SELECT. It's more like the old rows_affected. From the manual:

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

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.

http://php.net/manual/en/pdostatement.rowcount.php

Edited by diafol

Votes + Comments
:)
0

Hi,

if using MySQL you can do a second query:

$num = $this->conn->query("SELECT FOUND_ROWS()")->fetchColumn();

About rowCount():

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

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.

See also this comment, it suggests how to add a custom method with FOUND_ROWS():

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.