PDO looping through and printing fetchAll [duplicate]

Question

I'm having trouble getting my data from fetchAll to print selectively.

In normal mysql I do it this way:

$rs = mysql_query($sql);
while ($row = mysql_fetch_array($rs)){
   $id = $row['id'];
   $n = $row['n'];
   $k = $row['k'];
}

In PDO, I'm having trouble. I bound the params, then I'm saving the fetched data into $rs like above, with the purpose of looping through it the same way..

$sth->execute();
$rs = $query->fetchAll();

Now comes the trouble part. What do I do PDO-wise to get something matching the while loop above?! I know I can use print_r() or dump_var, but that's not what I want. I need to do what I used to be able to do with regular mysql, like grabbing $id, $n, $k individually as needed. Is it possible?

Thanks in advance..

Grotius

Answer 1

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.

Balkan

thanks very much for your help. I went with 2, because I have to count it too before looping. So makes more sense to store it in $results before. – Grotius

$query->rowCount() might return the count. – Balkan

It won't for SELECT statements. See this question: #460510 – Nomo

You have an extra right parentheses in this line: $results = $query->fetchAll(PDO::FETCH_ASSOC)); – Balduin

The while loop does not show anything, at least it does not break the process either. The foreach loop shows the results (I use a dummy query SHOW TABLES which should not be the problem). I am fine with the foreach solution, just wondering why. – Merriman

Answer 2

If you intend to iterate over the result set in the same function/method or layer where the query was executed, then it is recommend to simply iterate the result object directly instead of calling fetchAll(). (This puts less demand on resources and enjoys a simpler syntax.) An exception to always avoiding passing fetchAll()'s data to a foreach is when you are using one of PDO' specialized payload mutating modes/flags (e.g. PDO::FETCH_GROUP).

Pass your result set object as the input of foreach(). The foreach will treat the payload as an indexed array of associative arrays and will not complain if there are zero results.

A few demonstrations assuming $sql = "SELECT id, n, k FROM my_table"; (PHPize Demo)

foreach ($pdo->query($sql) as $row) {
    vprintf("id: %d, n: %s, k: %s\n", $row);
}

---

foreach ($pdo->query($sql) as $row) {
    echo "id: {$row['id']}, n: {$row['n']}, k: {$row['k']}\n";
}

---

foreach ($pdo->query($sql) as ['id' => $id, 'n' => $n, 'k' => $k]) {
    echo "id: $id, n: $n, k: $k\n";
}

---

$result = [];
foreach ($pdo->query($sql) as ['id' => $id, 'n' => $n, 'k' => $result[$id][$n]]);
var_export($result);

Notice that none of the above techniques need to call PDO's fetch() method to access the equivalent data produced by ->fetch(PDO::FETCH_ASSOC).

The exact same technique can be used for the result object produced by executing a prepared statement with PDO. Just pass the object in and don't bother manually calling fetch().

Northwestward

I sincerely beg my pardon for the tone. After all the time l still cannot hold it. But honestly, here we go again: a fair desire to share the knowledge, but completely misplaced. The only PDO related example should be just a foreach over $stmt, probably to fill the resulting array while augmenting the data. Other examples should go somewhere else, where foreach being the main topic. The demand to "grab $id, $n, $k individually" makes this question too localized and unfit with the generalized title. How to loop over results is one question. How to get your variables is completely different. – Partition