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

Question

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

`count()` only works on arrays or objects that implement `Countable`. You could use [`PDOStatement::rowCount()`](https://www.php.net/pdostatement.rowcount). — Sherif, Sep 12 '21 at 06:11
Does this answer your question? [Row count with PDO](https://stackoverflow.com/questions/883365/row-count-with-pdo) — Ivan Barayev, Sep 12 '21 at 20:56
@Sherif the docs advise against using it for SELECT statements: `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 behaviour is not guaranteed for all databases and should not be relied on for portable applications.` — pjk_ok, Sep 12 '21 at 21:07

score 1 · Accepted Answer · answered Sep 12 '21 at 03:44

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>

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

1 Answers1