PHP/SQL - How can I display distinct values and the number of occurrences?

Question

So I am currently using PHP to pull the bodies from emails, parse the data, and insert the data into a user-created DB. I want to display an overview of the data (that is inside the DB) using PHP. Here is an example of what I want to achieve:

This is my table:

Table Name: bldgSensors

 sensor_code | temp_limit | current_temp
 ---------------------------------------
 0102260100A |    55      |     45
 0102260100B |    55      |     50
 0102260100A |    55      |     48

Desired output using PHP:

 Sensors         Count
 0102260100A       2
 0102260100B       1

So far, I can find distinct values but cannot output the total count:

$result1 = mysqli_query($DBconn,"SELECT DISTINCT sensor_code FROM bldgSensors");

              echo "<table border='1'>
              <tr>
              <th>Distinct sensor codes</th>
              <th>Count</th>
              </tr>";

              while($row1 = mysqli_fetch_array($result1))
              {
                echo "<tr>";
                echo "<td>" . $row1['sensor_code'] ."</td>";
              }
              echo "</table>";
              mysqli_close($DBconn);

Thanks so much in advance! This will help tremendously!

Possible duplicate of stackoverflow.com/questions/1346345/… — misorude
– misorude, Commented Nov 13, 2018 at 9:07
That duplicate could btw. easily be found by copy&pasting the question title you have chosen into Google verbatim … So please make a proper research effort before asking next time. — misorude
– misorude, Commented Nov 13, 2018 at 9:08

Joseph_J · Accepted Answer · 2018-11-13 03:44:19Z

3

You can do that by modifying your query. Use the COUNT and GROUP BY sql functions:

Try this:

$query = "SELECT 
sensor_code, 
COUNT(sensor_code) AS sensorCount
FROM bldgSensors 
GROUP BY sensor_code 
ORDER BY sensorCount DESC";

$result1 = mysqli_query($DBconn, $query);

echo 
'<table border="1">
  <tr>
    <th>Distinct sensor codes</th>
    <th>Count</th>
  </tr>';

  while($row1 = mysqli_fetch_array($result1)){

    echo 
    '<tr>' .

      '<td>' . $row1['sensor_code'] . '</td>' .
      '<td>' . $row1['sensorCount']  . '</td>' .

    '</tr>';

  }

  echo 
  '</table>';

  mysqli_close($DBconn);

edited Nov 13, 2018 at 3:44

answered Nov 13, 2018 at 3:26

Joseph_J

3,6692 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chandler Phillips Over a year ago

Wow this worked. Thank you so much!! If I could upvote this 1000 times I would.

Joseph_J Over a year ago

Hey no problem :) Hope the rest of your project goes well. ~Cheers!

Collectives™ on Stack Overflow

PHP/SQL - How can I display distinct values and the number of occurrences?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related