Reputation: 87
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
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
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
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
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