PDO and MySql loop outputs only one row when there are several

Question

$stmt = $conn->prepare($sql);
$stmt->execute($array);
$rows = $stmt->rowCount();

    if($rows >= 1) {
        $x = $stmt->fetch();

        echo '<div>'.$x['heading'].'</div>';

        while($row = $stmt->fetch()) {
            echo '<div>'.$row['article'].'</div>';
        }
    } else {
        echo 'Nothing found';
    }

When doing like above, can you see why the loop outputs only one row when there are several? It happens when I use fetch twice.

Also, how can I avoid having to use fetch twice in there? It's fetched once, can i use that same fetched data again?

php.net/manual/en/pdostatement.fetch.php fetch is for a single row. You should try fetchAll(). — Maximus2012
– Maximus2012, Commented Oct 14, 2013 at 16:22
But the OP has a loop to fetch one row at a time. Maybe only one row has been returned? — showdev
– showdev, Commented Oct 14, 2013 at 16:24

Luke · Accepted Answer · 2013-10-14 16:23:43Z

1

$stmt->fetchAll()

maybe this?

edited Oct 14, 2013 at 16:23

Luke

23.8k31 gold badges121 silver badges202 bronze badges

answered Oct 14, 2013 at 16:23

Millard

1,1671 gold badge9 silver badges20 bronze badges

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

4 Comments

showdev Over a year ago

I'm not sure how this answers the question. Using fetch() in a while loop is perfectly fine.

Norman Over a year ago

fetch works fine when used alone. Add another, and there's a problem.

showdev Over a year ago

@Norman What is the problem with fetching more than once? This page has many examples using a while loop to fetch: php.net/manual/en/pdostatement.fetch.php

showdev Over a year ago

@CommuSoft Yes, but the while loop will continue to run as long as there are results in $stmt. It fetches one row upon each iteration until there are no more rows to fetch. I'm not sure how fetchAll() solves the issue here.

Luke · Accepted Answer · 2013-10-14 16:28:00Z

0

You can use fetchAll() to get all of the data in the result set as an array.

You can reuse the data by assigning it to a variable:

// Fetch all returned rows
$my_rows = $stmt->fetchAll();

// Make use of these rows multiple times
foreach ($my_rows AS $single_row)
{
    echo $single_row['column_name'];
}

// Make use of the retult set a second time
foreach ($my_rows AS $single_row)
{
    echo $single_row['column_name'];
}

answered Oct 14, 2013 at 16:28

Luke

23.8k31 gold badges121 silver badges202 bronze badges

2 Comments

Norman Over a year ago

When using fetchAll, is it possible to directly access a value without the loop. Eg: $my_rows['heading']

showdev Over a year ago

@Norman All of your rows are fetched into the array. So you'd do $my_rows[0]['heading'] for the first row. Try echo"<pre>".print_r($my_rows,true)."</pre>" to see the array structure.

Collectives™ on Stack Overflow

PDO and MySql loop outputs only one row when there are several

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related