I have some data returned from a MySQL database that outputs the post details for a particular user.
I’d like to output a count for the number of images (represented below by the $db_image_filename
value).
How do I get a count for the number of field values in a column? I thought I could use PHP’s count()
function, but this didn’t work?
Is there a way to do this in PHP without running another query on the database (seeing as this data has already been fetched from the database, and I just need its count value)? This value will then be echoed out in the <p>
tag at the bottom of the example below.
<?php
$stmt = $connection->prepare("SELECT * FROM imageposts WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->execute();
while ($row = $stmt->fetch()) {
$db_image_id = htmlspecialchars($row['image_id']);
$db_image_title = htmlspecialchars($row['image_title']);
$db_image_tags = htmlspecialchars($row['image_tags']);
$db_image_filename = htmlspecialchars($row['filename']);
?>
<figure>
<!-- html is outputted here including values using the PHP variables above -->
</figure>
<p>Number of images: <?php // echo the count value of $db_image_filename ?></p>
<?php } ?>
Advertisement
Answer
Define a variable $count_images = 0
before your while
loop and inside the loop increase the variable by +1 every time there is a $row['filename']
which is not NULL
or an empty string.
After the while
loop you can echo $count_images
:
<?php
$stmt = $connection->prepare("SELECT * FROM imageposts WHERE username = :username");
$stmt->bindParam(':username', $username);
$stmt->execute();
$count_images = 0; // AT THE BEGINNING THERE ARE 0 IMAGES
while ($row = $stmt->fetch()) { // LOOP START
$db_image_id = htmlspecialchars($row['image_id']);
$db_image_title = htmlspecialchars($row['image_title']);
$db_image_tags = htmlspecialchars($row['image_tags']);
$db_image_filename = htmlspecialchars($row['filename']);
if ($db_image_filename != NULL && $db_image_filename != '') {
$count_images++; // IF IMAGE FOUND, INCREASE BY +1
}
?>
<figure>
<!-- html is outputted here including values using the PHP variables above -->
</figure>
<?php } // END OF WHILE LOOP ?>
<p>Number of images: <?php echo $count_images; ?></p>