Querying an SQL result grouped by month

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:

$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.

Answer

For this you need to learn about SQL GROUP BY queries:

In your case:

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.