Pdo::Fetchall Vs. Pdo::Fetch in a Loop

PDO::fetchAll vs. PDO::fetch in a loop

Little benchmark with 200k random records. As expected, the fetchAll method is faster but require more memory.

Result :
fetchAll : 0.35965991020203s, 100249408b
fetch : 0.39197015762329s, 440b

The benchmark code used :

<?php
// First benchmark : speed
$dbh = new PDO('mysql:dbname=testage;dbhost=localhost', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM test_table WHERE 1';
$stmt = $dbh->query($sql);
$data = array();
$start_all = microtime(true);
$data = $stmt->fetchAll();
$end_all = microtime(true);

$stmt = $dbh->query($sql);
$data = array();
$start_one = microtime(true);
while($data = $stmt->fetch()){}
$end_one = microtime(true);

// Second benchmark : memory usage
$stmt = $dbh->query($sql);
$data = array();
$memory_start_all = memory_get_usage();
$data = $stmt->fetchAll();
$memory_end_all = memory_get_usage();

$stmt = $dbh->query($sql);
$data = array();
$memory_end_one = 0;
$memory_start_one = memory_get_usage();
while($data = $stmt->fetch()){
  $memory_end_one = max($memory_end_one, memory_get_usage());
}

echo 'Result : <br/>
fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/>
fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>';

Illegal string PDO fetch() vs fetchAll()

This error is not due to the use of fetch() OR fetchAll() as neither of these functions are using an offset, there must be something you are doing in your code later that is causing this issue.

I believe your issue has to do with the way you are looping through your values as you say that it works when you use fetchAll(). Your loop is throwing an error because it is trying to access an offset that does not exist because fetch() will only return a single result while fetchAll() will return multiple from your query.

---

As for your question "What's the difference between the two (fetch() & fetchAll()), and how would I use either one?", here is your answer.

fetchAll() is used to return all relevant rows, according to the query.

This is beneficial for queries where you need many rows of data.

PDOStatement::fetchAll() returns an array containing all of the
remaining rows in the result set. The array represents each row as
either an array of column values or an object with properties
corresponding to each column name. An empty array is returned if there
are zero results to fetch, or FALSE on failure.

---

fetch() is used to return a single row of results from your query.

This is beneficial for queries where you only need a single row result. This is useful for things like when a user logs in so you don't have to loop through data to assign it to variables.

Fetches a row from a result set associated with a PDOStatement object.
The fetch_style parameter determines how PDO returns the row.

PHP PDO fetchAll() vs direct foreach loop

The only difference is that the former doesn't consume extra memory for the returned records like the latter does.

However, given you generally shouldn't fetch more records than could be shown on a single HTML page anyway, the difference considered to be a negligible one.

PHP PDO FetchAll vs Fetch

If you're going to build a new array, there's not much point in having fetchAll() build an array. Write your own fetch() loop:

function userName($db){
    $q = $db->prepare("SELECT id, name FROM users WHERE id = :user");
    $q->bindParam(":user", $user);
    $q->execute();
    $names = array();
    while ($row = $q->fetch(PDO::FETCH_ASSOC)) {
        $names[$row['id']] = $row;
    }
    return $names;
}

There's also no need for buildArray(), since $row is already the associative array you want.

PDO looping through and printing fetchAll

It should be

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  $id = $row['id'];
  $n = $row['n'];
  $k = $row['k'];
}

If you insist on fetchAll, then

$results = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   $id = $row['id'];
   $n = $row['n'];
   $k = $row['k'];
}

PDO::FETCH_ASSOC fetches only column names and omits the numeric index.

PDO Fetchall, fetch, while and foreach loop only selects 1 row

You need to put the dropdown choices in the loop.

<?php
$acces = 'Electronics';
                
$conn = $pdo->open();
try{
    $stmt = $conn->prepare("SELECT `id`, `cat_slug`FROM mall_category WHERE name = :acces");
    $stmt->execute(['acces' => $acces]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e){
    echo "There is some problem in connection: " . $e->getMessage();
}
$pdo->close();

?>
<li class="dropdown">
    <a href="#" class="dropdown-toggle" data-toggle="dropdown">Electronics</a>
    <ul class="dropdown">
    <?php foreach ($result as $row) { ?>
      <li><a href='shopping.php?category=<?php echo $row['cat_slug']?>'><?php echo $categ; ?></a></li>
    <?php } ?>
    </ul>

</li>

Understand PDO fetch and fetchAll iteration with foreach

Can be as simple as having yet another foreach loop to iterate the array and then ("inside" that loop) do the same thing as before.

foreach($objects as $obj) {
  foreach($obj as $property) {
    ...
  }
}

how to properly use while loop in PDO fetchAll

With fetchAll() you don't have to use while at all. As this function returns an array, you have to use foreach() instead:

function getContent() {
    $db = PDOconn();
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 0,3";
    $sql = $db->prepare($query);
    $sql->execute();
    return $sql->fetchAll();
}

$data = getContent();
foreach($data as $row) {
    $id = $row['id'];
    $content = $row['content'];
}

fetchAll() - Is really necessary?

It kinda depends on what you want to do with the result.

The actual data structures you get from either are very different, but you are probably asking the question because with both you can loop through the result with foreach.

But fetchAll() returns a pure PHP array, where as query returns a PHP object with hidden internals. One of the two you can (for example) call json_encode on.

Generally directly looping over the PDOStatement might be a little faster, because you're not creating a (potentially large) intermediate array.

Loop through fetchall without knowing the fields php

First, fix your return fetchAll to specify the return style like so:

return $query->fetchAll(PDO::FETCH_ASSOC);

Then you can use an inner loop with your outter loop like so:

//get the results
$results = $User->getUser();
//loop over the results, setting $result to an array representing each row
foreach($results as $result){
    //loop over each $result (row), setting $key to the column name and $value to the value in the column.
    foreach($result as $key=>$value){
        //echo the key and value.
        echo "{$key} = {$value}<br>";
    }
}

This will output all columns and their value regardless of what columns there are in the array. Following the comments, you can see what I do is, using your code, loop over the outer array that is an array of each row from the query. Then loop over the array from each row getting the column name and the value in that column. For now I am just echo'ing out the column and value. You would likely want to do more like echo this out to a table or whatever your end goal is.

---

Related Topics