CODEWITHSUNDEEP
phpmysqldate
hkalan2007
hkalan2007

Reputation: 65

24 hours of values

I have a sql table : date (Y-m-d) / time (00:00:00) / power (INT)

When I select a date from an inline datepicker, I am trying to post 3 HighCharts graph (one-24 hours, two-31 days of month, three-12 months of year) and I need to get the values out of the table for the chart to be created.

For the day, I need the 24 values for each hour '100,200,300,200,300 etc..'

Here is the PHP for the "day" but it is not working...

<?php
$choice = (isset($_POST['choice'])) 
          ? date("Y-m-d",strtotime($_POST['choice'])) 
          : date("Y-m-d"); 
$con = mysql_connect("localhost","root","xxxxxx");  
if (!$con)  {  
  die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("inverters", $con);  
$sql = "SELECT HOUR(time), COUNT(power) 
FROM feed 
WHERE time = DATE_SUB('".$choice."', INTERVAL 24 HOUR) 
GROUP BY HOUR(time) 
ORDER BY HOUR(time)"; 
$res = mysql_query($sql) or die('sql='.$sql."\n".mysql_error()); 
$row = mysql_fetch_assoc($res); 
echo $row['choice'].'<br />'; 
?>

This has been confirmed by another individual that the code does not work, would anyone have a helpful solution to fix the error ?

Alan

Upvotes: 1

Views: 341

Answers (3)

hkalan2007
hkalan2007

Reputation: 65

Thank you everyone for all your help !

The problem was in the first string, I only had to change the date format in addition to your wonderful examles !

$choice = (isset($_POST['choice'])) ? date("m",strtotime($_POST['choice'])) : date("m");

Thank You Very Much !

Alan

Upvotes: 0

Toto
Toto

Reputation: 91375

You need a loop to walk over the rows:

$sql = "
SELECT HOUR(time) as h, power
FROM feed 
WHERE date = '".$choice."' 
ORDER BY HOUR(time)"; 

$res = mysql_query($sql) or die('sql='.$sql."\n".mysql_error()); 

while($row = mysql_fetch_assoc($res)) {
    echo $row['h'].":".$row['power']'<br />'; 
}

This will give you the power per day for a given month:

$sql = "
SELECT DAY(date) as d, SUM(power) as powerday
FROM feed 
WHERE MONTH(date) = '".$month."' 
GROUP BY DAY(date) 
ORDER BY DAY(date)"; 

$res = mysql_query($sql) or die('sql='.$sql."\n".mysql_error()); 

while($row = mysql_fetch_assoc($res)) {
    echo $row['d'].":".$row['powerday']'<br />'; 
}

Upvotes: 0

Piskvor left the building
Piskvor left the building

Reputation: 92752

At the moment, your SELECT gives you only the results which happened exactly 24 hours before the current moment. What you need is a range. Example for 1 hour (indentation added for clarity):

WHERE `time` BETWEEN 
   DATE_SUB('".$choice."', INTERVAL 24 HOUR) 
   AND DATE_SUB('".$choice."', INTERVAL 23 HOUR)

This way, you'll get results with time in the 1-hour range of "now - 24 hours" and "now - 23 hours". The BETWEEN operator is equivalent to this:

WHERE `time` >= DATE_SUB('".$choice."', INTERVAL 24 HOUR)
   AND `time` <= DATE_SUB('".$choice."', INTERVAL 23 HOUR)

Upvotes: 2

Related Questions