0

Having issues returning number of results in PHP SQLSRV PDO connection, when I try $stmt->rowCount(); get -1 result, really don't get it. 

...
...
...
if(empty($region)){
    $query2 = "SELECT [QuotID], [QuotNumber], CreationDate, QuotDate
    FROM [dbo].[vQuotaion]
    GROUP BY [QuotID]
        ,[QuotNumber]
        ,[CreationDate]
        ,[QuotDate]
    HAVING CreationDate >='".$fdate."' AND CreationDate <='".$edate."' AND  ProType = 'OPSFi' ORDER BY CreationDate DESC";
    $stmt2 = $conn->query( $query2 );           
} else {
    ...
    ...
    ...
}
...
...
...
<?php
if(empty($stmt2)){
    echo '';
    }else{
        while ($result = $stmt2->fetch(PDO::FETCH_ASSOC)){
        bla bla bla;
    }
}
?>
Kelvin Morel
  • 63
  • 2
  • 10
  • 2
    [*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) – Script47 Aug 24 '17 at 13:10
  • [*Great, while using MySQL5, the only way to get the number of rows after doing a PDO SELECT query is to either execute a separate SELECT COUNT(*) query (or to do count($stmt->fetchAll()), which seems like a ridiculous waste of overhead and programming time. *](http://php.net/manual/en/pdostatement.rowcount.php#78338) – Script47 Aug 24 '17 at 13:12
  • 1
    Make use of prepared statements seeing as you are using PDO. – Script47 Aug 24 '17 at 13:13

2 Answers2

0

If you want to count the rows without a separate query you can do this with PDO:

$rows = $stmt2->fetchAll();
$num_rows = count($rows);

There is no way to directly count rows when using a SELECT statement with PDO for all database drivers. You could create a function using the code above if you need to retrieve counts regularly.

Warning!

Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe! Learn about prepared statements for PDO.

Community
  • 1
  • 1
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
0

You can get the row count of a select query with the PDO versions of the sqlsrv drivers however, like the standard version of the drivers (non-PDO), you have to specify a scrollable cursor. Like so:

$query = "SELECT * FROM myTable";
$stmt = $conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$rows = $stmt->rowCount();

The default cursor used is PDO::CURSOR_FWDONLY which, when rowCount() is used, returns -1.

Microsoft documentation.

ImClarky
  • 1,933
  • 1
  • 25
  • 29