how to properly use while loop in PDO fetchAll

Question

please be easy on me, i just started learning PDO and still finding my way how to convert my mysqli to PDO.

so i have a function to get the contents from my database

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

    return $row;
}

normally when i return $row in mysqli, i would define fetch_assoc() in my while loop.

while ($row = $result->fetch_assoc())  {
    $id = $row['id'];
    $content = $row['content'];
}

Now, since (PDO::FETCH_ASSOC) is already declared in my function.

how would i properly create my while loop to print the values in PDO?

[edit] updated code

i will be declaring my while loop outside of the function. so i need something to return from my function but i dont know what that is..

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

    return $row;
}

this is my while loop outside the function.

$sql = getContent();

while ($row = $sql->fetchAll(PDO::FETCH_ASSOC))  {
    $id = $row['id'];
    $content = $row['content'];
}

Since this ranks high on Google, it is worth noting that it would be easier to comprehend if `$rows` refers to one or more rows, and `$row` refers to a single row. For instance: `foreach($rows as $row)`. — rybo111, Feb 06 '16 at 09:54

score 37 · Accepted Answer · edited Nov 15 '16 at 03:56

37

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'];
}

edited Nov 15 '16 at 03:56

Your Common Sense

156,878
40
214
345

answered Oct 21 '13 at 07:26

Hanky Panky

46,730
8
72
95

can i assume whenever i use `PDO::FETCH_ASSOC` i will be using `foreach` now instead of a `while`? – bobbyjones Oct 21 '13 at 07:29
Nope don't assume that. You can still use while loop easily. Its not a compulsion to use foreach, see the edit – Hanky Panky Oct 21 '13 at 07:30
so in my sample above, you can see the i `returned $row` in the function which includes the `PDO::FETCH_ASSOC`. do i have to declare it again on my `while($row= $sql->fetch(PDO::FETCH_ASSOC)) {}` ? sorry if didn't quite understood your explanation well . – bobbyjones Oct 21 '13 at 07:44
1

Ok let me explain it better. `fetch()` gets you one row by one, whereas `fetchAll()` grabs all the rows together and assigns them to your object. In your example you used `fetchAll()`, if you want to use this while from my code then you have to remove fhe fetchAll statement, one minute let me update the answer accordingly. – Hanky Panky Oct 21 '13 at 07:51
but what if my `while` loop is outside of the function, how do i return a variable and do the looping and printing outside my function . can you kindly refer to my updated post and see if im doing it correctly. – bobbyjones Oct 21 '13 at 08:17
although im not sure why `foreach` is being used but this solution worked. thank you for your patience :) – bobbyjones Oct 21 '13 at 08:30
In order to use a while loop here you will have to loop over the array indexes and use another variable, `foreach` suits more – Hanky Panky Oct 21 '13 at 08:41
8

I will suggest to use `while ($row = $sql->fetch(PDO::FETCH_ASSOC)) { //code }`, because `fetchAll()` returns all data as 2D array and stores in a variable, that consume much RAM space while working with large data. Please correct me if someone knows better on this. – Aniket Singh Dec 13 '16 at 12:35
Any query that returns 'large data' for display is itself a problem – Hanky Panky Dec 13 '16 at 12:59

how to properly use while loop in PDO fetchAll

1 Answers1

Linked

Related