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);
Member Avatar for diafol

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

commented: :) +14

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():

thanks for diafol and cereal.

I am using MSSQL

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.