php mysql query Highest, Lowest and Average value

[dk4210]

Hello all,

 

I need some help. I have a database structure like this

 

Date        |  time          |   Walk-in 1 Front  |  Walk-in 1 Front  |  Walk-in 2

7/3/2016  |  0:01:01    |      5.00                |           4.86         |       4.31

7/3/2016  |  0:02:01    |      4.375              |           4.22         |       2.34

7/3/2016  |  0:03:01    |      5.13                |           4.12         |       3.65

7/3/2016  |  0:04:01    |      4.89                |           3.99         |       3.32

 

I want to be able to query it for the Highest, Lowest and the average ( I know I may have to add a drop down for this ) but I can't get it to work correctly. I'm using Highcharts and it is creating 3 bar graphs based on the values for Walk-in 1 Front, Walk-in 1 Front & Walk-in 2

 

Here is my code and here's the link - to see the bar graph - http://www.kellywebserv.com/temperature/

 

<?php
$con = mysql_connect("localhost","****","####");

if (!$con) {
  die('Could not connect: ' . mysql_error());
}

mysql_select_db("mydb", $con);

// Format the date

// Today's date
$date = date("m-d-Y");
// Convert date
$date1 = str_replace('-', '/', $date);
// Set date of previous day
$from_date = date('n/j/Y',strtotime($date1 . "-1 days"));

 //echo "this is date" . $from_date;

$query = mysql_query("SELECT * FROM SensorData WHERE Date = '$from_date' LIMIT 1");

$category = array();
$category['name'] = 'Date';

$series1 = array();
$series1['name'] = 'Walk-in 1 Front';

$series2 = array();
$series2['name'] = 'Walk-in 1 Rear';

$series3 = array();
$series3['name'] = 'Walk-in 2';



while($r = mysql_fetch_array($query)) {
    $category['data'][] = $r['Date'];
    $series1['data'][] = $r['Walk-in 1 Front'];
    $series2['data'][] = $r['Walk-in 1 Rear'];
    $series3['data'][] = $r['Walk-in 2'];  
    
}

$result = array();
array_push($result,$category);
array_push($result,$series1);
array_push($result,$series2);
array_push($result,$series3);

print json_encode($result, JSON_NUMERIC_CHECK);

mysql_close($con);
?>

[Jacques1]

The code makes no sense to me. You want the lowest, highest and average tempatures of what? Of each sensor individually? Of all sensors together?

 

The problem is that your database layout is rather poor. Instead of storing the measurements as rows, you've added a separate column for each sensor. This makes it extremely difficult to do any calculations across the sensors, and adding a new sensor requires you to alter the entire table and append yet another column.

 

It also seems you've stored dates and times as strings with custom formats instead of using the proper MySQL data types. This will again make calculations very difficult.

 

So I suggest you fix your table first. The columns should be as follows:

• measurement_id (INT, AUTO_INCREMENT, PRIMARY KEY)
• sensor (VARCHAR)
• measurement_time (DATETIME)
• temperature (DECIMAL or FLOAT)

A single row would look like this:

(1, "Walk-in 1 Front", "2016-07-03 00:01:01", 5.00)

Edited July 5, 2016 by Jacques1