Counting and then updating array

Question

I have a database with a date field that I want to count the number of unique years and then how many entries for each year. Here is what I have:

while ($row = mysql_fetch_array($sql_result)) 
{
     $Date = $row["Date"];
     //DETERMINE YEARS
     $Year = substr("$Date",0,4);
     if (!in_array($Year, $allDates)){
          $allDates[] = $Year;;
     }

}

So this leaves me with an array containing all the unique years.

Array ( [0] => 2010 [1] => 2008 [2] => 2009 [3] => 2006 [4] => 2007 [5] => 2012 [6] => 2011 [7] => 2013 )

Now how do I loop through all the entries again and total up the number of records with that specific year?

Thanks in advance

Why not just do this with a COUNT and a GROUP BY in your SQL query? `select YEAR(myDateColumn) AS 'year', COUNT(1) AS 'count' FROM myTable GROUP BY YEAR(myDateColumn)` and the number of unique years is the number of rows returned — Mark Baker, Sep 18 '13 at 13:09

score 0 · Answer 1 · answered Sep 18 '13 at 13:09

0

Instead of looping again, you can maintain the count for each year in same iteration.

answered Sep 18 '13 at 13:09

Bharat Jain

654
4
6

score 0 · Answer 2 · answered Sep 18 '13 at 13:13

0

I agree with Bharat

Try something like that :

while ($row = mysql_fetch_array($sql_result)) 
{
     $Date = $row["Date"];
     //DETERMINE YEARS
     $Year = substr("$Date",0,4);
     if (!array_key_exists($Year, $allDates)){
          $allDates[$Year] = $allDates[$Year]+1;
     }else{
          $allDates[$Year] = 1;
     }
}

answered Sep 18 '13 at 13:13

Odward

113
1
9

Cool. This seems to work with the exception that it leaves me with this: Array ( [0] => 771 [1] => 1277 [2] => 1379 [3] => 372 [4] => 1703 [5] => 1473 [6] => 1006 [7] => 1671 ) And I need to know the dates that correspond to the totals – Jeff Yonker Sep 18 '13 at 13:24
The date (I mean the year) is present as a key of your array. array_keys($allDates) will return you the years' list. – Odward Sep 18 '13 at 13:43

score 0 · Answer 3 · answered Sep 18 '13 at 13:37

0

I would like to add to the answers of @Bharat and @Odward, there is a function in php called array_count_values(). You can read about it in PHP docs :
http://www.php.net/manual/en/function.array-count-values.php
You can use this function :

$result = array_count_values($Year);
print_r($result);

The output should be something like this :

Array
(
    [2012] => 2
    [2009] => 4
    [2010] => 3
)

I hope this helps and excuse my English.

answered Sep 18 '13 at 13:37

A.Essam

1,094
8
15

Thanks Guys. I laid in bed all last night trying to figure this out - shoulda just asked ;) – Jeff Yonker Sep 18 '13 at 13:43

Counting and then updating array

3 Answers3