Difference between fetch and fetchall in PHP?

There are two different things mixed in the question:

fetch

opens as a pointer ready to go through the data one by one (usually through a cycle while ). It is the recommended method when you expect a lot of data, because that way you will not have memory problems.

Code example:

$stmt = $pdo->prepare($sql);
$stmt->execute();
while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $datos []= $fila;
}

Here $datos will be an associative array that will be filled with one row each time the pointer advances within the while .

Also, you can print the data in situ if you do not need to transport it to another place:

while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $fila["nombre"]." ".$fila["apellido"].PHP_EOL;
}

fetchAll

brings you all the data at once , without opening any pointer, storing them in an array. It is recommended when you do not expect too many results that could cause memory problems when you want to save thousands or millions of rows in an array! coming from a SELECT .

Code example:

$resultado = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($resultado);

In this case, $resultado is an associative array with all the data in the query. If you want to read the rows in it, you can implement a loop that traverses the array:

foreach ($resultado as $row){
    echo $row["nombre"]." ".$row["apellido"].PHP_EOL;
}

It is almost the same as in fetch , only that here we are not acting on a route of the data through a pointer, but on the data already stored in a variable. If for example before this we close the state, we already have the data in $resultado and we can read them. In fetch if we close the statement we can not read the data.

As for PDO::FETCH_ASSOC it's just a way of representing the data. One of the fetch_style that exists, which indicates that you want the results in the form of an associative array where the key is the name of each column and the value the data belonging to that column. You can use it with fetch as well as fetchAll .

Note that for fetch as for fetchAll the default style is PDO::FETCH_BOTH . But you can set a default style to either the connection object using the setAttribute method, changing the value of constant PDO::ATTR_DEFAULT_FETCH_MODE ; be to a specific statement using the method setFechMode .