Display MySQL column value count only

Question

How do I display this query result:

from MySQL onto a webpage, just like this:

?

I want to display the count only. I did run the same query through PHP but its returning '2'. The PHP code is below

 <?php
    //Connection for database                                          
    $conn = mysqli_connect("localhost", "root", "aaaaa", "db");

    $query = "SELECT `gender`,COUNT(`gender`)  FROM `reg` GROUP BY `gender`";

        $result = mysqli_query($conn, $query); 
        
        if ($result) 
        { 
            // it return number of rows in the table. 
            $row = mysqli_num_rows($result); 
            
            if ($row) 
                { 
                    printf("" . $row); 
                } 
            // close the result. 
            mysqli_free_result($result); 
        } 
    
        // Connection close  
        mysqli_close($conn); 
?>

Please note that I have gone through other answers like this one but I can't really find my way through, I want the value count only not the total number of all rows in the table, .e.g. count for 'Male'.

score 2 · Accepted Answer · answered Dec 13 '20 at 11:52

You're using mysqli_num_rows(), which returns the number of rows in the result, not the actual data in the result. For that you need to use mysqli_fetch_assoc().

Your could would become:

    $query = "SELECT `gender`,
                     COUNT(`gender`) AS `count` 
              FROM `reg` 
              GROUP BY `gender`";

    $result = mysqli_query($conn, $query); 
    
    if ($result) { 
        while ($row = mysqli_fetch_assoc($result)) {
          $gender = $row['gender'];
          $count  = $row['count'];
          echo "$gender = $count<br>";
        } 
        mysqli_free_result($result); 
    }

Note that I slightly changed your query to make the count accessible.

Display MySQL column value count only

1 Answers1