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 NameProfile
        FROM sa_users U
        INNER JOIN sa_profiles P ON P.Id = U.Profile
        WHERE User = :user  AND Pass = :pass";

$result = $this->dbConnect->prepare($sql) or die ($sql);
$result->bindParam(':user',$this->data['username'],PDO::PARAM_STR);
$result->bindParam(':pass',$this->data['password'],PDO::PARAM_STR);

if (!$result->execute()) {
    return false;
}

$numrows = $result->rowCount();
$jsonLogin = array();

var_dump($numrows);

if($numrows > 0) {
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        $jsonLogin = array( 
            'name' => $row['Name'],
            'id' => $row['Id'],
            'profile' => $row['Profile'],
            'n_profile' => $row['NameProfile']
        );
    }

    $jsonLogin['area'] = 'another';
    return $jsonLogin;
} else {
    return false;
}

var_dump($result->fetch()) in MySQL and SQL Server

array(8) {
["Id"]=>
string(1) "1"
[0]=>
string(1) "1"
["Nombre"]=>
string(13) "Administrador"
[1]=>
string(13) "Administrador"
["Perfil"]=>
string(1) "1"
[2]=>
string(1) "1"
["NomPerfil"]=>
string(13) "Administrador"
[3]=>
string(13) "Administrador"
}

var_dump($numrows) in SQL Server

int(-1)

var_dump($numrows) in MySQL

int(1)

Regards.

Answers


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.


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


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.


Need Your Help

How to deploy an ASP.NET web site

asp.net deployment

I have an ASP.Net website that I built in a computer science class. I built it in Visual Studio and the solution is on my local drive. How would I deploy this to a real website?

Less syntax with @@ meaning

css compiler-construction less

I have downloaded a theme for bootstrap and In their less sources I have a mixin like:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.