Values per hour for 24 hours

[hkalan2007]

Hello,

 

I have a very simple table... DAY (Y-m-d) / TIME (00:00:00) / POWER (INT)

 

I am using a Jquery datepicker to select the date on the page, and that POST to the PHP file.

 

I am trying to select values from Mysql to make 3 HighCharts Graphs using the selected day of the datepicker. I have started with the DAY graph PHP to get the values for each hour of a 24 hour day. I need to get the values for each hour... 23,12,15,35 etc... , and then I need for the 31 days of a month for the month graph, and 12 months of the year for the month graph all in the same way so the HighCharts can use the values to make the chart (3 php files, one for each graph)

 

Here is the PHP I have for the 1 day that should get the 24 individual hour data, but it does not seem to work...

 

<?php
$choice = (isset($_POST['choice'])) ? date("Y-m-d",strtotime($_POST['choice'])) : date("Y-m-d"); 
$con = mysql_connect("localhost","root","mackie1604");  
if (!$con)  {  
die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("inverters", $con);  
$sql = "SELECT HOUR(time), COUNT(power) FROM feed WHERE time = '".$choice."' 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 />'; 
?>

 

What have a written wrong in the sql query ???

 

Alan

[joel24]

you're ordering by HOUR(time), which will order 6pm on the 24th of June before 10pm on the 22nd of June as it is only ordering by hour...

you need to order by the date and then the time, i.e. make this ORDER BY dateColumn, HOUR(time)

 

  Quote

Here is the PHP I have for the 1 day that should get the 24 individual hour data, but it does not seem to work...

what does it return? any mysql errors, or incorrect information? if incorrect information, what?

[hkalan2007]

Hello,

 

Yes I was getting errors...

 

My goal is to have 24 values - 1 for each hour of a day (sure, some hours will have a 0 value)... I want to pulling values for each of the 24 hours in ONE SINGLE DAY... (not 1 hour of a day - 24 hours).

 

My TABLE has 3 columns = | date (Y-m-d) | Time 00:00:00) | Power (int) |

 

What code I have now that does not have any error, but only gives me a singel value...

 

<?php
$choice = (isset($_POST['choice'])) ? date("Y-m-d",strtotime($_POST['choice'])) : date("Y-m-d"); 
$con = mysql_connect("localhost","root","xxxxxxxxxx");  
if (!$con)  {  
die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("inverters", $con);  
$sql = "SELECT HOUR(time), COUNT(power) AS choice 
FROM feed 
WHERE date = '".$choice."'
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 />'; 
?>

 

I will also have to do this for a MONTH, and for a YEAR...

 

 

Mysql DATES and TIMES are the hardest for me to understand

[BlueSkyIS]

the code only asks for a single row. here is how to see all of them:

 

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

[hkalan2007]

Hello,

 

I had learned this a short time ago...

 

The code that workd for the day is below, but I am not able to get the 30+ values for a month, or the 12 values for the year.... The codes are below, could you see where I am wrong in the month and year... I can't put my finger on what I am missing.

 

24 Hour results...

 

$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['power']'<br />';  
} 

 

Daily results...

 

$sql = " 
SELECT DAY(date) as d, SUM(power) as powerday 
FROM feed  
WHERE date = '".$choice."'  
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['powerday']'<br />';  
} 

 

 

[joel24]

change HOUR to MONTH / YEAR for grouping by month or year.

 

$sql = " 
SELECT MONTH(date) as month, power 
FROM feed  
WHERE date = '".$choice."'  
GROUP BY MONTH(date)
ORDER BY MONTH(date)";  
$res = mysql_query($sql) or die('sql='.$sql."\n".mysql_error());  
while($row = mysql_fetch_assoc($res)) { 
    echo $row['power']'<br />';  
} 

 

 

** EDIT **

sorry, misread what you were after.

if you only want to return values from a select month then use a where clause like this. Set the month number to whatever month you seek.

WHERE MONTH(date) = 8
AND YEAR(date) = 2010

[hkalan2007]

Hello,

 

The WHERE statement gets the date from the datepicker ($choice) that is sent to the php file we are in, so if I select 2010-10-28 that goes to the php file, and the WHERE statement takes that date to use the DAY ot MONTH for me to select, group,order the data.

 

I can only get a single value, and not 31 for the days, or 12 for the month... here is where I am at, and it only shows 1 day value.

 

<?php
$choice = (isset($_POST['choice'])) ? date("Y-m-d",strtotime($_POST['choice'])) : date("Y-m-d"); 
$con = mysql_connect("localhost","root","xxxxxxxxxx");  
if (!$con)  {  
die('Could not connect: ' . mysql_error());  
}  
mysql_select_db("inverters", $con);  
$sql = " SELECT MONTH(date) AS m, SUM(power) AS powermonth 
FROM feed  
WHERE date = '".$choice."' 
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['m'].":".$row['powermonth'].'<br />';  

}
?>

[hkalan2007]

Hello,

 

I fixed it, I needed to change the date in the post string for the month and year...

 

Thanks for your assistance !

 

Alan