'fetch' in PDO gets only one result

Question

I have this code:

$sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
$f = $sql->query('select * from user');
$sql->setFetchMode(PDO::FETCH_ASSOC);

while($row = $f->fetch()){
     print_r($row);
}

The output is

Array
(
    [id] => 1
    [name] => turki
    [mail] => ablaf
    [pass] => 144
)
Array
(
    [id] => 2
    [name] => wrfwer
    [mail] => fwerf
    [pass] => werf
)

And that's what I really want. But if I do this

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $f = $sql->query('select * from user');
    $f->setFetchMode(PDO::FETCH_ASSOC);
    print_r($f->fetch());
?>

The output is

Array
(
    [id] => 1
    [name] => turki
    [mail] => ablaf
    [pass] => 144
)

It has one result, but I have two rows in the table; why is that?

Solution 1

Fetch should be used to display the next row from the database result.

To get all rows, you should use fetchAll();

• PDOStatement::fetch — Fetches the next row from a result set
• PDOStatement::fetchAll() — Returns an array containing all of the result set rows

Change your example to:

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $f = $sql->query('select * from user');
    $f->setFetchMode(PDO::FETCH_ASSOC);
    print_r($f->fetchAll());
?>

or if you want use PDOStatement::fetch to

<?php
    $sql = new PDO('mysql:host=localhost;dbname=b', 'root', 'root');
    $f = $sql->query('select * from user');
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
      print_r($row);
    }
?>

Solution 2

In that case use fetchAll.

$result = $f->fetchAll();
print_r($result);

Comments

[mickmackusa]

Have we got some bad copy-pasta here? I don't see where you've defined $sth. If there are no variables to bind to the statement, then a prepared statement is unnecessary overhead.

[Robert]

not sure what you meant by prepared statements as this answer clearly doesn't mention it but you were right with $sth it was a typo.

[mickmackusa]

Now, IMO, sth is misleading. The return from the query isn't a "statement handle" -- it is a result set resource/object. sth would be more appropriate when using a prepared statement. Your hyperlinks are pointing to prepared statement documentation, but your snippet is not using a prepared statement.