PDO::rowCount() returning -1

Question

i have this function below. that i used to run on a MySQL DB. I had to move to a SQL Server 2008, easy thing.

But, after that, the rowCount() keeps returning -1, I've never had this happen before. I'm sure that my SQL query is returning the results, because if I do a print_r() on my return (the $rows var), everything gets printed.

So, if anyone had this issue, please help me to figure this out.

Sorry for any grammatical mistake.

public function listar(){

    $retorno = array();

    $sql = "SELECT m.id, m.descricao, m.link, m.categoria, m.icone FROM menus AS m,    grupos AS g, permissoes AS p WHERE (g.id = p.idgrupo AND m.id = p.idmenu) AND (p.status = :pstatus AND g.status = :gstatus AND m.status = :mstatus) AND g.id = :gid ORDER BY m.ordem ;";

    $vars = array(":pstatus"=>1,":gstatus"=>1,":mstatus"=>1,":gid"=>$_SESSION['group']);

    $stmt = $this->pdo->prepare($sql);

    foreach($vars as $index => $value){
        $stmt->bindValue($index,$value);
    }

    if($stmt->execute()){
        $count = $stmt->rowCount();
        $rows = $stmt->fetchAll(PDO::FETCH_OBJ);
        $rows['msg'] = '1';
        $rows['length'] = $count;

        $i = 0;
        while($i < $count){
            foreach($rows[$i] as $index => $value){
                $rows[$i]->$index = utf8_encode($value);
            }
            $i++;
        }

        return $rows;
    } else {
        return array("msg" => '0');
    }

}

Answer 1

I think you may be running into the gotcha on the PDO rowCount() function. As stated in the PHP manual. The second sentence is the gotcha...:

"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 behavior is not guaranteed for all databases and should not be relied on for portable applications."

Instead, you should use a COUNT(*) sql statement.

Answer 2

Got it guys.

The prepare() statement should receive one more parameter. array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) .

$stmt = $this->pdo->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

After that the rowCount() should work fine.

Thanks for the answer @Rasclatt.