如何计算列中相同结果的数量？

Question

I've developed a database, where i have three sets of columns: - Username - Feeling - Remark

The entry's for feelings are all the same, because they're being selected from a dropdown menu. I'm looking for a way to count the amount of same results and then echo it on a site with PHP, but I can't get any further than this:

<?php
$con=mysqli_connect("localhost","username","password", "database");
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$query = "SELECT COUNT(*) FROM thefeels WHERE feeling = 'Happy'";
$result = mysqli_query($con, $query);

var_dump($result)
?>

In this code I want to count the amount of times happy occurs in a column, but even that is not working. How can I count the amount of times the same results is in a column? So if Happy is there four times and Sad two times it should display:

Happy: 4
Sad: 2

Answer 1

This is a basic Counting Rows task. You will find some examples in the official documentation.

In your case it would be:

$query = "SELECT feeling, COUNT(*) as count FROM thefeels GROUP BY feeling";
$result = mysqli_query($con, $query);

Now you can use the "old school" way (which you will find in many totorials):

while ($row = mysqli_fetch_assoc($result)) {
    echo "{$row['feeling']}: {$row['count']}<br>";
}

or move forward and separate data fetching from data processing and data output with:

$feelingCounts = $con->fetch_all(MYSQLI_ASSOC);

And do what ever you need with the fetched data. For example:

var_dump($feelingCounts);