php - Count by month returning a string with 13 values
Sql query designed to output a string of totals per month is outputting 13 values (month 0?)
What do I need to add to the code to start counting at month 1 (January) and not month 0?
<?php
$sql = "SELECT MONTH(SightingDate) AS month, COUNT(CASE WHEN SpeciesName = 'Choreutis pariana' and Notes LIKE '%Adult%' THEN 1 END) cnt
FROM GAINLP
GROUP BY MONTH(SightingDate);";
if ($result3=mysqli_query($connect,$sql)) {
while($row=$result3->fetch_assoc()) {
echo $row['cnt'] . ',';
}
}
?>
The database contains one record for August. I am expecting result of (0,0,0,0,0,0,0,1,0,0,0,0) < 12 comma separated values.
Instead, above code outputs (0,0,0,0,0,0,0,0,0,1,0,0,0) <13 CSV
Answer
Solution:
This is probably because you have some rows in your database that have either invalid or
NULL
SightingDate
values, soMONTH(SightingDate)
is returning0
orNULL
, thus giving you 13 rows in the output (1 to 12 for January to December plus 0/NULL). You can run this query to find the bad dates: