PHP PDO while loop isn't returning anything

Question

I really can't figure out what I'm doing wrong here. I'm doing a query to check whether there are records in a DB table called 'newCards'.

With $count I check how many results it's returning: it shows me '1'. But the while loop isn't returning ANY thing. The only things I'm seeing are the <th>'s at the top of the table, but no table records are present, while $count is giving '1' as a result. Which is true, cause there is actually 1 record present in DB.

How can I fix this?

<?php
    $query = $db->prepare("SELECT * FROM `newCards` WHERE `company` = :companyID");
    $query->bindParam(":companyID", $enterprise['id'], PDO::PARAM_INT);
    $query->execute();
    $count = $query->rowCount();
    echo $count;

    if(empty($query->fetch())){
        echo "Geen gevonden";
    } else {
?>
                    <table>
                        <tr>
                            <th>Ontvanger</th>
                            <th>Saldo</th>
                            <th></th>
                        </tr>
<?php
        while($result = $query->fetch()){
?>
                        <tr>
                            <td><?php echo $result['id']; ?></td>
                            <td>2</td>
                            <td>3</td>
                        </tr>
<?php
        }
?>
                    </table>
<?php
    }
?>

Note that fetch() returns FALSE not only when there are no records found, but also when the querying of the db fails, from different reasons. And you can not differentiate between the two situations. This is a bug in PDO. So, I'd recommend you to use fetchAll() instead. It returns an empty array when no records are found, or FALSE on failure. Both situations can be handled correspondingly. Also, don't mix data access functions within HTML. Fetch all data in arrays, in the upper php code. Work with that arrays when you're building your HTML parts. — user7941334
– user7941334, Commented Sep 20, 2017 at 21:47
@aendeerei its returning null on an empty resultset (if I remember correctly), not false. And it's not really a bug, I don't believe it is. It's so you can loop it, and so the loop breaks when there are no more results to fetch. It doesn't adhere to the SQL-92 SQLSTATE standard, but it does work — Qirel
– Qirel, Commented Sep 20, 2017 at 21:48
@Qirel Thanks for ponting me out. I rechecked my docus: it definitely returns a bool FALSE. I made - I would say a lot of - research and tests one month ago. And I noted me the results. The first phrase in them is my prior comment. The second phrase is: "Maybe on failure an exception will be thrown" :-) That means, that I couldn't test this at that moment. And I'm referring to it as beeing a bug, because in this context it is not the natural, desirable behavior, like in the case of fetchAll(). — user7941334
– user7941334, Commented Sep 20, 2017 at 21:56
I definitely agree that using fetchAll() is a good approach - but it's seemingly not a bug that it returns false with an empty resultset. Someone reported it as a bug a good while back and got the issue closed with "working as intended"... — Qirel
– Qirel, Commented Sep 20, 2017 at 22:00
@Qirel I didn't checked any official "bug" post, as I recall, but I checked the behavior as described not only by me, but also by many internet users. No, no, it is wrong to return false when no records found AND on failure. Why? Because, no exception is thrown when using fetch(). So, how can you handle the matter, if there is no way to differentiate betw. no records and fetching failure? Just for fun, test it and give me a feedback, please, if you wish. — user7941334
– user7941334, Commented Sep 20, 2017 at 22:10

u_mulder · Accepted Answer · 2017-09-20 21:05:18Z

1

$query->fetch() already fetches a record. So next call to fetch() fetches next record or nothing if there're no records. In your case with one record second fetch() fetches nothing, so while never starts.

You can change your code to:

if($count){?>
<table>
    <tr>
        <th>Ontvanger</th>
        <th>Saldo</th>
        <th></th>
    </tr>
<?php
    while($result = $query->fetch()){
        // show row
    }?>
</table>
} else {
    // echo that no rows found
}

answered Sep 20, 2017 at 21:05

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

Comments

Markownikow · Accepted Answer · 2017-09-20 21:05:09Z

0

I think fetch in first if is executed so that is why second returns empty, try to assign it to var before conditions or check wit $cont var

answered Sep 20, 2017 at 21:05

Markownikow

4671 gold badge4 silver badges14 bronze badges

Comments

Darwin · Accepted Answer · 2017-09-20 21:09:37Z

0

I believe you want to return an array indexed by column names with

->fetch(PDO::FETCH_ASSOC)

More information can be found here http://php.net/manual/en/pdostatement.fetch.php

answered Sep 20, 2017 at 21:09

Darwin

3771 gold badge2 silver badges9 bronze badges

Comments

Qirel · Accepted Answer · 2017-09-20 21:21:18Z

Because fetch() fetches the first row, even when checking in empty(), it will try to fetch the next row when you use while($result = $query->fetch()){. You can either check the value from $count (like shown by u_mulder), but you should beware of the note in the manual for rowCount() (emphasis mine)

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behavior is not guaranteed for all databases and should not be relied on for portable applications.

You can use a do..while structure and check if the fetch was successful or not instead. If you change out if(empty($query->fetch())){ with if (!$row = $query->fetch()) {, you check if there was a row fetched or not (as fetch() returns null on an empty result). Then $row is ready to use, and you can use it before the first loop takes place.

<?php 
$query = $db->prepare("SELECT * FROM `newCards` WHERE `company` = :companyID");
$query->bindParam(":companyID", $enterprise['id'], PDO::PARAM_INT);
$query->execute();
$count = $query->rowCount();
echo $count;

if (!$row = $query->fetch()) {
    echo "Geen gevonden";
} else {
    ?>
    <table>
        <tr>
           <th>Ontvanger</th>
           <th>Saldo</th>
           <th></th>
       </tr>
    <?php
    do {
        ?>
        <tr>
           <td><?php echo $result['id']; ?></td>
           <td>2</td>
           <td>3</td>
       </tr>
       <?php
    } while ($result = $query->fetch());
    ?>
    </table>
    <?php
}

Collectives™ on Stack Overflow

PHP PDO while loop isn't returning anything

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related