按月计算返回包含13个值的字符串

Question

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 1

This is probably because you have some rows in your database that have either invalid or NULL SightingDate values, so MONTH(SightingDate) is returning 0 or NULL, 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:

SELECT * FROM GAINLP WHERE MONTH(SightingDate) NOT BETWEEN 1 AND 12