Get Count For A Set Of Fetched Field Values – MySQL / PHP

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

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>