how to get count in prepare using PDO Functions

[rpv_sen]

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);

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

[cereal]

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.

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

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

• http://php.net/manual/en/pdostatement.rowcount.php#113133

[rpv_sen]

thanks for diafol and cereal.

I am using MSSQL

[cereal]

See this comment:

• http://php.net/manual/en/pdostatement.rowcount.php#113608

You can connect to MSSQL through different drivers, depending on which you choose then you can use rowCount().