Page 1 of 1

php mysql query Highest, Lowest and Average value

Posted: Tue Jul 05, 2016 5:13 am
by developer_dan
Hello all,



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


[text]
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[/text]



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.kellywebs...om/temperature/


Code: Select all

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

Re: php mysql query Highest, Lowest and Average value

Posted: Tue Jul 05, 2016 9:21 am
by Celauran
but I can't get it to work correctly
is not terribly descriptive. Also, your link is broken.

What is the expected behaviour? What is happening instead? What errors are being reported? Why are you using mysql_ functions that have been outdated for fifteen years?

Re: php mysql query Highest, Lowest and Average value

Posted: Tue Jul 05, 2016 9:40 am
by developer_dan
Hey Thanks for the reply!

I'm basically trying to modify my query to get the highest, Lowest and Average. What would you use instead of mysql_functions?

Thanks,
Dan

Re: php mysql query Highest, Lowest and Average value

Posted: Tue Jul 05, 2016 9:42 am
by Celauran
developer_dan wrote:What would you use instead of mysql_functions?
PDO

That's a best practice question, though, and may or may not be the cause of your problems. It's still not clear what is happening.

Re: php mysql query Highest, Lowest and Average value

Posted: Tue Jul 05, 2016 7:15 pm
by Christopher
You want to use the GROUP BY clause on the column you are interested in. Then use the MIN(), MAX() and AVG() functions to get the values you are interested in. For example:

Code: Select all

SELECT MIN(walkin2) AS minimum, MAX(walkin2) AS maximum, AVG(walkin2) AS average FROM mytable GROUP BY walkin2;