如何从php pdo中的mssql查询中获取受影响的行数

Question

I am changing my php project from using mysql to mssql. when i use if ($query->rowCount() == 0) { with mysql it works well but with mssql i get a negative value which isnt correct. So i tried to use $count = count($query->fetchAll()); with mssql which gives me a positive value similar to when using mysql but i get an error I am using Php drivers for sql server

Fatal error: Uncaught PDOException: SQLSTATE[IMSSP]: There are no more rows in the active result set. Since this result set is not scrollable, no more data may be retrieved.

Need some help with this issue

Answer 1

The documentation is clear about PDOStatement::rowCount.

Returns the number of rows added, deleted, or changed. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, a PDO::CURSOR_FWDONLY cursor returns -1. A PDO::CURSOR_SCROLL ABLE cursor returns the number of rows in the result set.

If you want to use rowCount() for SELECT statements, you need to use PDO::CURSOR_SCROLLABLE:

<?php  
#
$server = "server\instance,port";  
$dbname = "database";
$uid = "uid";  
$pwd = "pwd";  

# Connection
$conn = new PDO("sqlsrv:server=$server ; Database = $dbname", $uid, $pwd);  

# SELECT statement
$query = "SELECT * FROM Table1";  
$stmt = $conn->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));  
$stmt->execute();  
echo $stmt->rowCount();  

# End
$stmt = null;
$conn = null;
?>   

When you use $count = count($query->fetchAll()); your result set is already fetched after $query->fetchAll() call. If you try to call PDOStatement::fetch or PDOStatement::fetchAll methods, you'll get this error.

Try the next approach:

<?php

...
$result = $query->fetchAll(); 
$count = count($result);
foreach ($result as $row) {

}
...

?>