Myujth

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!

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);