Using PDO to echo display all rows from a table

Question

I'm trying to echo out all the rows of a table using PDO but am running into trouble.

With the old way of doing I'd have done it like

$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)){
   $title= $row['title'];
   $body= $row['body'];
}

But with PDO I'm trying;

$result = $db->prepare("SELECT title, body FROM post");
$result->execute();

while ($row = $db->fetchAll(PDO::FETCH_ASSOC))
{
$title = $row['title'];
$body = $row['body'];
}

echo $title;
echo $body;

Which keeps giving me Call to undefined method PDO::fetchAll()

Doing the example given in the manual

<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>

Works, but I don't think I have control over the individual colums like I would with a $row=['blah']; do I? It also prints out like this; rather ugly:

Array ( [0] => Array ( [title] => This is the test title entered in the database[0]

What needs to be done to properly use PDO to do this?

Green Black · Accepted Answer · 2013-01-23T23:53:46.903

9

change:

while ($row = $db->fetchAll(PDO::FETCH_ASSOC))
{
$title = $row['title'];
$body = $row['body'];
}

to:

while ($row = $result->fetch(PDO::FETCH_ASSOC))
{
$title = $row['title'];
$body = $row['body'];
}

edited Jan 23 '13 at 23:53

answered Jan 23 '13 at 23:47

Green Black

5,037
1
17
29

This seems to be giving me a similar error Fatal error: Call to undefined method PDO::fetch() – Jan 23 '13 at 23:53
I changed my answer, try again... ($db should be $result) – Green Black Jan 23 '13 at 23:54
Thanks John. Please excuse the ignorance though, but where does the $result come from? It's giving me an undefined variable error. – Jan 23 '13 at 23:57
look in your post and find the variable $result? Could be $sth, I dont know what version of you post you are using... – Green Black Jan 23 '13 at 23:58
Hahaha oh boy, don't I look a fool. Changed that in my code due to fabs answer and forgot about it. Got it working now, thanks! – Jan 23 '13 at 23:59

score 2 · Answer 2 · answered Jan 23 '13 at 23:48

Which keeps giving me Call to undefined method PDO::fetchAll()

This should have given you the hint, that you are using the wrong object. It's PDOStatement::fetchAll as you can see in your second example, or if you want to use it in a while loop PDOStatement::fetch:

while ($row = $result->fetch(PDO::FETCH_ASSOC))
{
    $title = $row['title'];
    $body = $row['body'];
}

Additional notes:

$result is a misleading variable name as you might see from the $result->execute() line. You don't execute a result, you execute a statement. This is why in the manual $stmt or $sth (statement handle i guess) are used.
The echo lines should be inside the while loop, otherwise you overwrite again and again, then output only the last row.

Thanks fab, I've made the necessary changes and it's helped me solved the problem. — , Jan 24 '13 at 00:01

Using PDO to echo display all rows from a table

2 Answers2

Linked

Related