Pdo Rowcount() Works On Mysql But Not In Sql Server 2008 R2

I have a problem when I get number of rows in SQL Server 2008 because my code works fine using MySQL but not in SQL Server. $sql = 'SELECT TOP 1 U.Id , U.Name, U.Profile, P.Name N

Solution 1:

I know it's a bit of an old thread, but I had the similar question this morning and there's actually a way for the rowcount() function to work with SQL server.

I'm using a connection string like this (to connect to a SQL server database):

$connection = new PDO("sqlsrv:Server=" . $this->sourceServer . ";Database=" . $this->sourceDB, $this->sourceUser, $this->sourcePW);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

And when I want to use a query for which I need to know the number of row to return (with SQL server), I use PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL as second parameter of PDO prepare function just like this:

$rs = $connection->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

Here's the example from Microsoft website: https://msdn.microsoft.com/en-us/library/ff628154(v=sql.105).aspx

Well, it's never too late to share a good solution,

Jonathan Parent-Lévesque from Montreal

Solution 2:

Just quoting the manual:

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.

Solution 3:

You don't actually need this function. As well as most of the other code

$result = $this->dbConnect->prepare($sql);
$result->bindParam(':user',$this->data['username']);
$result->bindParam(':pass',$this->data['password']);
$result->execute();
$jsonLogin = $result->fetch(PDO::FETCH_ASSOC));
if ($jsonLogin) {
    $jsonLogin['area'] = 'another';
   return json_encode($jsonLogin);
}

is all the code you need.

Solution 4:

Just add $result = $stmt->fetchAll(); after $stmt->execute();

$stmt = $db->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll();
$numrows = $stmt->rowCount();