Difference between fetch and fetchall in PHP?

Question:

I would like to know what is the difference between fetch and fetchall in php, from what I saw there fetch returns a single field and fetchall all the fields, but using PDO it seems to me that it doesn't matter which one I use, I totally use (PDO :: FETCH_ASSOC) , but I would still like to know the difference, thank you 🙂

Answer:

They are two different things mixed in the question:

fetch

opens as a pointer ready to while through the data one by one (usually via a while ). It is the recommended method when a lot of data is expected, 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 a row each time the pointer advances within the while .

Also, the data can be printed on- site if it is not needed to transport it to another place:

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

fetchAll

It brings you all the data at once , without opening any pointer, storing it in an array. It is recommended when you are not expecting 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 query data. If you want to read the rows in it, you can implement a loop that goes through 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 traversal 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 it. In fetch if we close the statement we will not be able to read the data.


As for PDO::FETCH_ASSOC is just a form of representation of the data. One of the fetch_style that exist, 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 is the data belonging to that column. You can use it with both fetch and fetchAll .

It should be noted that for both fetch and fetchAll the default style is PDO::FETCH_BOTH . But you can set a default style to either the connection object through the setAttribute method, changing the value of the constant PDO::ATTR_DEFAULT_FETCH_MODE ; be to a specific setFechMode using the setFechMode method.

Scroll to Top