CODEWITHSUNDEEP
phpsql
Fred Smith
Fred Smith

Reputation: 33

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.

Upvotes: 0

Views: 828

Answers (2)

Dharman
Dharman

Reputation: 33238

It's quite simple to do with PDO. All you have to do is use GROUP BY month(date) in SQL and then fetch the results indexed by month number. Next, you iterate each month from given start date until end date and for each month you check if there is an entry in the array.

<?php

$pdo = new \PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", 'user', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

// get all counts grouped by month number
$stmt = $pdo->query("SELECT MONTH(date), COUNT(*) as count 
    FROM tabledate
    WHERE year(Date)=2019 
    GROUP BY month(date)");
$counts = $stmt->fetchAll(\PDO::FETCH_KEY_PAIR);

// iterate through all months
$startDt = new DateTime('2019-01-01');
$endDt = new DateTime('2019-12-31');
$period = new DatePeriod($startDt, new DateInterval('P1M'), $endDt);
foreach ($period as $dt) {
    echo $dt->format('F') .': '.($counts[$dt->format('n')] ?? 0).PHP_EOL;
}

Upvotes: 1

ADyson
ADyson

Reputation: 61859

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.

Upvotes: 1

Related Questions