php mysql query Highest, Lowest and Average value

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
developer_dan
Forum Newbie
Posts: 2
Joined: Tue Jul 05, 2016 5:10 am

php mysql query Highest, Lowest and Average value

Post 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);
?>
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php mysql query Highest, Lowest and Average value

Post 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?
developer_dan
Forum Newbie
Posts: 2
Joined: Tue Jul 05, 2016 5:10 am

Re: php mysql query Highest, Lowest and Average value

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php mysql query Highest, Lowest and Average value

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: php mysql query Highest, Lowest and Average value

Post 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;
(#10850)
Post Reply