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
– Funk Forty Niner, Commented Jun 29, 2014 at 1:16
while ($arr = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $arr['name']; } as taken from this answer stackoverflow.com/a/19364078 you have two ways to do this. A while or a foreach, you can take your pick. — Funk Forty Niner
– Funk Forty Niner, Commented Jun 29, 2014 at 1: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
– Funk Forty Niner, Commented Jun 29, 2014 at 1:29

meda · Accepted Answer · 2014-06-29 05:14:57Z

5

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
}

answered Jun 29, 2014 at 5:14

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stan Over a year ago

Thank you. That's exactly the information I was looking for.

ChrisKsag Over a year ago

There seems to be a problem with fetch() vs fetchAll() not returning all the rows in the db. I found that if you use fetcha() with a while loop it returns missing the first row. While using fetchAll with foreach returns all the rows. Maybe there's something I'm missing. An explanation would be great! Thank you

meda Over a year ago

@ChrisKsag fetch() fetches the record one by one, until the cursor reaches the end of the recordset. To me it sounds like you called fetch() once before the while loop.

meda · Accepted Answer · 2014-06-29 05:15:15Z

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, 2014 at 5:15

meda

45.5k14 gold badges96 silver badges123 bronze badges

answered Jun 29, 2014 at 1:17

samayo

16.5k13 gold badges95 silver badges115 bronze badges

1 Comment

meda Over a year ago

for your answer to work properly you need to use fetchAll , I fixed it for you

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related