Trying to total by month from timestamp

I am trying to get a sum by month of readings from and file with mysql the date is in a date time stamp field in the sql file.

<?php 
$db = new mysqli('localhost', 'php06', 'php06', 'php00');

if (mysqli_connect_errno()) {
  echo "Error: Could not connect to database.  Please try again later.";
  exit;}

$query = "select readingVolume, readingDate from Reading order by readingDate";
$result = $db->query($query);
while($row = $result->fetch_assoc()){
$mysqldate = $row['readingDate'];
$timestamp = strtotime($mysqldate);
$day = date("d", $timestamp);
$month = date("m", $timestamp);
$year = date("Y", $timestamp); 

if ($month = 01){
$JanRead = ($row['readingVolume'] + $JanRead);}
if ($month = 02){
$FebRead = ($row['readingVolume'] + $FebRead);}
if ($month = 03){
$MarRead = ($row['readingVolume'] + $MarRead);}
if ($month = 04){
$AprRead = ($row['readingVolume'] + $AprRead);}
if ($month = 05){
$MayRead = ($row['readingVolume'] + $MayRead);}
if ($month = 06){
$JunRead = ($row['readingVolume'] + $JunRead);}
if ($month = 07){
$JulRead = ($row['readingVolume'] + $JulRead);}
开发者_C百科if ($month = 08){
$AugRead = ($row['readingVolume'] + $AugRead);}
if ($month = 09){
$SepRead = ($row['readingVolume'] + $SepRead);}
if ($month = 10){
$OctRead = ($row['readingVolume'] + $OctRead);}
if ($month = 11){
$NovRead = ($row['readingVolume'] + $NovRead);}
if ($month = 12){
$DecRead = ($row['readingVolume'] + $DecRead);}
}

$readingarray = array($JanRead,$FebRead,$MarRead,$AprRead,$MayRead,
                        $JunRead,$JulRead,$AugRead,$SepRead,$OctRead,
                        $NovRead,$DecRead);
print_r($readingarray);                         
?>

Your main problem appears to be that you are using = for testing equality, when you should be using ==. And don't prefix your integers with 0 unless you are doing a string comparison and the numbers are prefixed (e.g., $foo == 1, or $foo == '01).

You could make it much shorter if you directly inserted into your arrays:

$readingarray[$month] += $row['readingVolume'];

Also note that you could do this query via MySQL if you group by the month and do a SUM(). If your field were date type, it would be trivial. As a UNIX timestamp, you would first need to convert it to a date. (One extra MySQL function call.)

Assuming that readingDate is a DATE or DATETIME in MySQL, you probably want something like:

select SUM(readingVolume) as vol, MONTH(readingDate) from Reading order by readingDate group by MONTH(readingDate)

Also, as konforce pointed out: you're testing for equality with the assignment operator.

Additionally, in PHP, numbers prefixed with 0 are interpreted as octal, so 09 is actually == 0.

S

Here is a SQL that should do the same as @konforce mentions in his answer:

SELECT SUM(readingVolume), FROM_UNIXTIME(readingDate, '%m') as m 
FROM Reading 
GROUP BY m

(Update: just verified that one can use aliases in GROUP BY)

Assuming the timestamp conversion of PHP will alway equal to timestamp of MySQL is wrong.

You need to do job in MySQL.

SELECT SUM(readingVolume) as vol FROM Reading GROUP BY FROM_UNIXTIME(readingDate, '%m')