PDO rowCount() works on MySQL but not in SQL Server 2008 R2

Question

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.

not all database return the rowcount in the query metadata. mysql happens to be one of them, mssql isn't. — Marc B, May 13 '13 at 21:33
I didn't know that.. and you know another way to get total rows of query? I try with $r = $result->fetchColumn(); $numrows = count($r); but in while($row = $r) or while($row = $result->fetch()) don't recognize indexes. — SoldierCorp, May 13 '13 at 21:39
probably the only practical way is to do a `select count(*)` separately. But if you're goin going to be fetching all the data anyways (and it's not an insanely huge data set), then fetch into an array and count the rows afterwards. — Marc B, May 13 '13 at 21:40
But with select count(*) needs execute two queries to database, 1 for compare and another to get data... well if doesn't exists another way, I need do that. — SoldierCorp, May 13 '13 at 21:43

score 7 · Answer 1 · answered Sep 14 '15 at 15:54

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

score 3 · Answer 2 · answered Sep 20 '21 at 16:14

3

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

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

answered Sep 20 '21 at 16:14

Matt

179
4
11

score 1 · Accepted Answer · answered May 13 '13 at 21:37

1

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.

answered May 13 '13 at 21:37

dev-null-dweller

29,274
3
65
85

I didn't know that.. and you know another way to get total rows of query? I try with $r = $result->fetchColumn(); $numrows = count($r); but in while($row = $r) or while($row = $result->fetch()) don't recognize indexes. – SoldierCorp May 13 '13 at 21:39
Other than making separate `select count(*)` would be getting all rows (with `fetchAll()` method) and counting result array or simply defining rowcount variable and incrementing it after every `fetch()`. To get indexed/associative array with `fetch`, use one of it's fetch style as first parameter. – dev-null-dweller May 13 '13 at 21:58
But to get rows with select count(*) I need repeat the same code with the difference is count(*) sentence right? – SoldierCorp May 13 '13 at 22:07
Yes, but it is quite normal to have separate query only to check number of expected rows – dev-null-dweller May 13 '13 at 23:15

score 0 · Answer 4 · answered Sep 14 '15 at 16:04

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.

PDO rowCount() works on MySQL but not in SQL Server 2008 R2

4 Answers4

Linked

Related