finding the count() of months

[paddy_fields]

Hi, I'm trying to make a bar chart that shows how many views a job has per month. For the chart i need 12 seperate variables with the count for each stored... like $countJanuary, $countFebruary etc.

 

I've managed to work this out so far,

$query = "SELECT COUNT(*) AS `month_count`, DATE_FORMAT(viewDate, '%m') AS `month` FROM jobViews GROUP BY `month` ORDER BY `month` ASC";
$result = $db->query($query);

while($row = $result->fetch_assoc()) {
    
  echo $row['month']." - ".$row['month_count'];
  echo "</br>";

}

This produces the following (for testing purposes) but it obviously misses out months that have no matches...

01 - 3
03 - 1
05 - 1
12 - 7

I need the query to assign 0 to months with no matching results, so for February '02 = 0'

 

Does any have any advice on the logic of how I can get to the stage where I have (as in the example above) $countJanuary = 3, $countMarch = 1 etc..

 

Any help would be great

 

[Barand]

one way

$sql = "CREATE TEMPORARY TABLE months (month int)";
$db->query($sql);

$sql = "INSERT INTO months VALUES (1),(2),(3),(4),(5),(6),(7),(,(9),(10),(11),(12)";
$db->query($sql);

$query = "SELECT m.month , COUNT(*) AS `month_count` 
FROM months
    LEFT JOIN jobViews ON m.month = MONTH(viewDate)
GROUP BY month";

[paddy_fields]

Thank you very much, that's such a great idea of how to do it.

 

I've tried to implement your code I'm getting no output when I print_r the $row? Am I handling this wrong? I'll read up more and play about with the code tomorrow night when I've actually slept, but for now this is my attempt!

$sql = "CREATE TEMPORARY TABLE months (month int)";
$db->query($sql);

$sql = "INSERT INTO months VALUES (1),(2),(3),(4),(5),(6),(7),(,(9),(10),(11),(12)";
$db->query($sql);

$query = "SELECT m.month , COUNT(*) AS `month_count` 
FROM months
    LEFT JOIN jobViews ON m.month = MONTH(viewDate)
GROUP BY month";

if($result = $db->query($query)){
  $row = $result->fetch_assoc();
}

print_r($row);

I'm getting a notice saying $row is an undefined variable

[Barand]

try removing the "m" table alias

$query = "SELECT month , COUNT(*) AS `month_count`
FROM months
LEFT JOIN jobViews ON month = MONTH(viewDate)
GROUP BY month";

[Maq]

Looks like the 'm' alias was never even defined.

FROM months m

[Barand]

 

Looks like the 'm' alias was never even defined.

 

which is why I told him to remove the references to it

[Zane]

You get this notice because $row is not defined.. By this I mean, it is ONLY defined in your IF statement and no where else, put $row=null; above your if statement and your notice will be gone.

 

The 'm' would throw an SQL error, not a PHP Notice.

[Barand]

But $row was not defined because the query failed (the undefined alias that I forgot)

[Zane]

True.. I guess I'm used to the mysql errors rather than mysqli,  mysql would throw a non-boolean error.

[paddy_fields]

Yes, thank you it was the 'm' that was causing the problem - I should have noticed that.

 

The query is now running, but for some reason if there are 'no views' on a certain month then the count is 1 instead of 0?

 

In my test data I only have views for January - April. The counts on these are correct (they're not 1 too many), but the rest should say 0

$sql = "CREATE TEMPORARY TABLE months (month int)";
$db->query($sql);

$sql = "INSERT INTO months VALUES (1),(2),(3),(4),(5),(6),(7),(,(9),(10),(11),(12)";
$db->query($sql);

$query = "SELECT month , COUNT(*) AS `month_count` 
FROM months
    LEFT JOIN jobViews ON month = MONTH(viewDate)
GROUP BY month";

$result = $db->query($query);
  while($row = $result->fetch_assoc()){
    echo $row['month']." - ".$row['month_count']."<br>";
}
 

Output:

 

1 - 5
2 - 3
3 - 3
4 - 1
5 - 1
6 - 1
7 - 1
8 - 1
9 - 1
10 - 1
11 - 1
12 - 1

 

I understand how the code works but can't see why 5-12 would have a count of 1??

[Barand]

Sorry, change COUNT(*) to COUNT(viewDate).

 

COUNT(*) is counting the records and there is always at least 1

[paddy_fields]

Great, thank you. I really appreciate your help.

 

It's interesting to see how you've solved this so I can hopefully see more of the logic for myself in the future

[Barand]

Plan B if you need an array of the totals

$totals = array_fill_keys(range(1,12), 0);

$query = "SELECT MONTH(viewDate) as month , COUNT(*) AS `month_count` 
        FROM jobViews
        GROUP BY month";

$result = $db->query($query);
  while($row = $result->fetch_assoc()){
    $totals[$row['month']] = $row['month_count'];
}

echo '<pre>',print_r($totals, true),'</pre>';

[paddy_fields]

You sir, are a magician.

 

Works perfectly.

[paddy_fields]

Just to understand this... the array_fill_keys is creating an array of 12 keys (1-12) each with a value of 0.

 

Then the while loop updates keys in the array if the key is the same as $row['month'] value?

[Barand]

that's right