PDO :: rowCount () returning -1

4

I have this routine below, which I used without problems with a MySQL database. However, I had to migrate to a SQL Server 2008 database, which was simple.

The only problem is that, I do not know why my rowCount() is returning -1 , even with my query working without problem, because if I give print_r in SQL return, all the lines that should come in the query are there.

Follow my code:

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');
    }

}
    
asked by anonymous 08.12.2014 / 18:15

2 answers

2

Based on the link that @bfavaretto sent me, I managed to come up with a solution, go on.

In the line where prepare() is defined, the following parameter array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL) must be added. So the prepare line stays that way.

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

Then rowCount() will work without problems.

Thanks for the answers.

    
08.12.2014 / 18:41
3

This is the problem when a select is sent, it is not guaranteed that all the database returns the number of rows (it returns the number of rows affected by a DML: DELETE, INSERT, or UPDATE), the manual alerts you about it.

  

If the last SQL statement executed by the 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.

     

If the last SQL statement executed by the associated PDOStatement was   A SELECT statement, some databases may return the number of rows   Returned by this statement. However, this behavior is not guaranteed   For all databases and should not be invoked for laptops   Applications.

To counter this situation, make a select with count (). Another solution would be to use count () of php as long as the query has no aggregated results . count() returns the number of rows in the array.

    
08.12.2014 / 18:21