Returning an array with PDO - using FetchAll doesn't work

Question

I use the following code to retrieve data from my database. The problem is that it only displays the first row. In this particular case, it means that only the first picture is shown on the webpage but I want to show all of them.

<?php 
    $sql = "SELECT `image-id`, `article-id`, `image-path`, `image-title` FROM `table-images` WHERE `article-id` = :id";

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":id", $id);
    $stmt->execute();

    if($result = $stmt->fetch(PDO::FETCH_ASSOC))
    {
?>

<a class="swipebox" href="<?php echo $result['image-path'];?>" title="<?php echo $result['image-title'];?>">
<img alt="image" src="<?php echo $result['image-path'];?>"></a>

<?php
    }// end if
    else {
    echo '0 results';
    }// end else
?>

I read this article so I tried to use the code:

if($result = $stmt->fetchAll(PDO::FETCH_ASSOC));?

... but that doesn't work. It doesn't even echo the first picture anymore. What am I missing here?

Use a `while` loop. Edit: I was just about to mention a `foreach` loop, just as the answer appeared below. — Funk Forty Niner, Jun 29 '14 at 01:16
`while ($arr = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $arr['name']; }` as taken from this answer http://stackoverflow.com/a/19364078/ you have two ways to do this. A `while` or a `foreach`, you can take your pick. — Funk Forty Niner, Jun 29 '14 at 01:23
To explain what your present code is doing is this: Your conditional statement is checking **if** a result exists; and if so, it will echo only a single result because you are not looping till it doesn't find any others in your table. — Funk Forty Niner, Jun 29 '14 at 01:29

score 3 · Accepted Answer · answered Jun 29 '14 at 05:14

Here is how it works:

$stmt = $pdo->prepare($sql);
$stmt->bindParam(":id", $id);
$success = $stmt->execute();

if($success){
    //fetch here
}

Now you have 2 options for fetching the data:

fetch()

fetch() will get rows one by one so you need a while loop.

while($rows = $stmt->fetch(PDO::FETCH_ASSOC)){
 // get data
}

fetchAll()

fetchAll() get all the rows at once so no need for loops to retrieve the data, but if you need to loop then you will need a for loop.

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row){
  //do something
}

Thank you. That's exactly the information I was looking for. — Stan, Jun 29 '14 at 11:50

score 1 · Answer 2 · edited Jun 29 '14 at 05:15

1

<?php 
    $sql = "Your SQL query";
    $id  = 1; 

    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":id", $id);
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC)

    if($stmt->rowCount()){

       foreach($result as $row){
         echo $row['row_name'].'<br/>'; 
       }

    }else{
       echo 'No results found'; 
    }

edited Jun 29 '14 at 05:15

meda

45,103
14
92
122

answered Jun 29 '14 at 01:17

samayo

16,163
12
91
106

for your answer to work properly you need to use `fetchAll` , I fixed it for you – meda Jun 29 '14 at 05:18

Returning an array with PDO - using FetchAll doesn't work

2 Answers2