CODEWITHSUNDEEP
phpmysqlloopspdo
Dennis Prins
Dennis Prins

Reputation: 87

PHP PDO while loop isn't returning anything

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
    }
?>

Upvotes: 2

Views: 1124

Answers (4)

Qirel
Qirel

Reputation: 26490

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
}

Upvotes: 0

Darwin
Darwin

Reputation: 377

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

Upvotes: 0

u_mulder
u_mulder

Reputation: 54796

$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
}

Upvotes: 1

Markownikow
Markownikow

Reputation: 457

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

Upvotes: 0

Related Questions