php - Count by month returning a string with 13 values

739

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

121

Answer

Solution:

This is probably because you have some rows in your database that have either invalid orNULLSightingDate 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:

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

People are also looking for solutions to the problem: javascript - Real time client information synchronization, best practices and advice

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.