Query does not return a result

Question

I converted a web-based application from MySQLi to PDO, because I want to use Microsoft SQL-Server as database too. PDO works like a charm with MySQL database. Now I tried it for the first time with MS-SQL and it does not. Almost every query has to be updated. It is very frustrating.

The simple code below drives me nuts:

$ComputerGUID = "5BEC3779-B002-46BA-97C4-19158C13001F";

$SqlSelectQuery = "SELECT computermapping.PrinterGUID, 
                case when computerdefaultprinter.PrinterGUID IS NOT NULL then 1 else 0 end AS isDefaultPrinter
                FROM computermapping 
                LEFT JOIN computerdefaultprinter ON computerdefaultprinter.ComputerGUID = computermapping.ComputerGUID 
                AND computerdefaultprinter.PrinterGUID = computermapping.PrinterGUID
                WHERE computermapping.ComputerGUID = :ComputerGUID";
$SqlStatement  = $pdo->prepare( $SqlSelectQuery );
$SqlStatement -> bindValue( ":ComputerGUID", $ComputerGUID, PDO::PARAM_STR );
$SqlStatement->execute();

$SelectQueryNumRows = $SqlStatement->rowCount();
IF ( $SelectQueryNumRows > 0 ) {

    $Data = $SqlStatement->fetchAll(PDO::FETCH_ASSOC);

} ELSE {
    echo "The query did not return a result ...";
}

It is working fine with MySQL and returns me a result.

With Microsoft SQL-Server it get NO RESULT. (The query did not return a result ...)

Running the same query in Microsoft SQL Server Management Studio is working fine too:

Monitoring the query when running the code with SQL Server Profiler shows the following:

would you please `var_dump($Data);` or `print_r($Data);` what do you see ? — , Jan 10 '21 at 12:32
Follow the [manual](https://www.php.net/manual/en/pdostatement.rowcount.php#refsect1-pdostatement.rowcount-description) which says "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" — u_mulder, Jan 10 '21 at 12:34
Yes, you are right. When I remove the IF-block with the row-count, I have access to the data. — Walhalla, Jan 10 '21 at 12:47

score 2 · Accepted Answer · answered Jan 10 '21 at 12:40

2

As the manual mentions:

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.

So, you should not use rowCount. But as you fetch all data - use it:

$SqlStatement->execute();

$Data = $SqlStatement->fetchAll(PDO::FETCH_ASSOC);
// in case of no data - empty array will be returned

if ($Data) {
    // process $Data;
} else {
    echo "The query did not return a result ...";
}

Also note that $Data can be false in case of errors and if required you should process this value of $Data.

answered Jan 10 '21 at 12:40

u_mulder

54,101
5
48
64

Thank you very much. I was not aware of this. I use the row-count clause extensively in my code and I will have to do a lot of changes :-( – Walhalla Jan 10 '21 at 12:49
1

@Walhalla but now if you think of it, it just makes no sense to use rowCount if you have the data itself anyway. Less work, more compatible code. – Your Common Sense Jan 10 '21 at 16:13
@YourCommonSense : As we found the root-cause here, I did some reading. (e.g. [Here](https://stackoverflow.com/questions/883365/row-count-with-pdo)) This IF-clause in my code is a relict from the past, as it was `mysql_num_rows` before. Using this seems to extra query the database a second time to get the row-count. Very bad. I will convert my code. – Walhalla Jan 10 '21 at 16:24

Zhorov · Answer 2 · 2021-01-10T12:55:01.333

0

For most drivers, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. But if you are using PHP Driver for SQL Server, as is mentioned in the documentation, if the last SQL statement executed by the associated PDOStatement was a SELECT statement, a PDO::CURSOR_FWDONLY cursor returns -1 and a PDO::CURSOR_SCROLLABLE cursor returns the number of rows in the result set.

You need to prepare the statement like this:

$SqlStatement  = $pdo->prepare(
   $SqlSelectQuery, 
   array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL)
);

edited Jan 10 '21 at 12:55

answered Jan 10 '21 at 12:39

Zhorov

28,486
6
27
52

I had a closer look on your answer now and tested it by adding the mentioned "PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL". Is it true, that "rowCount()" will work now? What will happen, if I would use it with MySQL too? Thx – Walhalla Jan 10 '21 at 12:59
1

@Walhalla If you use PHP Driver for SQL Server (pdo_sqlsrv), as is mentioned in the documentation, the answer is yes. I'm not able to test this against MySQL database, but this [question](https://stackoverflow.com/questions/883365/row-count-with-pdo) gives additional explanations about `PDO::rowCount()` capabilities.. – Zhorov Jan 10 '21 at 14:30

Query does not return a result

2 Answers2