1

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!

Chandler Phillips
  • 19
  • 2
  • Possible duplicate of https://stackoverflow.com/questions/1346345/mysql-count-occurrences-of-distinct-values – misorude Nov 13 '18 at 09: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 Nov 13 '18 at 09:08

1 Answers1

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);
Joseph_J
  • 3,654
  • 2
  • 13
  • 22