I have been learning PHP and SQL but have a question.
I want to count the data where the year is 2019 but then want to populate a JSON array for each month.
I have started by creating the following SQL and PHP code:
JavaScript
x
$sql = "SELECT COUNT (*) as count FROM table WHERE year(Date)=2019"
$sqlResult = mysqli_query($db,$sql)
$sqlCount = mysqli_fetch_assoc($sqlResult)
$array = array(
array(
"Month" => "January"
"Value" => $sqlCount["Count"]
), array(
"Month" => "Febuary"
"Value" => $sqlCount["Count"]
),
I do not however know how to run a further query on this result to retrieve month by month, if it’s possible at all?
I know that I can manually create an SQL statement and result for each month but want to find a more efficient way.
Advertisement
Answer
For this you need to learn about SQL GROUP BY
queries:
In your case:
JavaScript
SELECT
month(Date) as mon,
COUNT(*) as count
FROM
table
WHERE
year(Date) = 2019
GROUP BY
month(Date)
Then you can loop through the results to build your array.
You can check many tutorials and documentation pages for explanations of GROUP BY. Here’s just one such resource.