data filtering using php and mysql

Question

This couple of days I've working on this project. i didnt know whats the error of this code and what method should i use to make my code better, my friend told me to apply array but im not familiar on that method i try to research and research in couple of days but it not working. What i need is to make a program that will count level 1,2,3,4 and 5 and display for example how many data(row) got level 5 in jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov and dec, in this sample code i only show filtering level 5 only because i dont want may reader of this to be confuse. thank you guys :-(

This is My sample table

Table: sample
+-------------+------------------+-----------+
|    date     |     level        |   id      |
+-------------+------------------+-----------+
| 2012-03-01  |      4           |     1     |
| 2012-02-02  |      1           |     2     |
| 2012-04-03  |      4           |     3     |
| 2012-05-14  |      1           |     4     |
| 2010-01-05  |      5           |     5     |
| 2009-01-05  |      2           |     6     |
| 2008-01-05  |      3           |     7     |
| 2012-02-02  |      1           |     8     |
| 2012-06-03  |      4           |     9     |
| 2012-07-14  |      1           |     10    |
| 2010-01-05  |      5           |     11    |
| 2009-01-05  |      2           |     12    |
| 2012-08-05  |      3           |     13    |
+-------------+------------------+-----------+

This query is selecting all data with a year of 2012 and order by date

$query = mysql_query("select * from table where year(date)='2012' order by date");

This while loop generate all the record that $query prefer. In the loop there was the HOPING condition that filter the records with all 5 LEVEL... when the data(produce by $query) is level 5 the $level5 will increment by 1, then that data will under go to another if statement to determined what month the data(produce by $query) belong, let say the data is 2012-05-14, so the data will go in the if statement if(Date_format($ii['date'],'%m'))==05) and increase the $level5_may by 1 and after that...

while($ii=mysql_fetch_array($query))
{
if($ii['level']==5)
{
 $level5++;
  if(Date_format($ii['date'],'%m'))==1)
  {
  $level5_jan++;
  }elseif(Date_format($ii['date'],'%m'))==02)
  {
  $level5_feb++;
  }elseif(Date_format($ii['date'],'%m'))==03)
  {
  $level5_mar++;
  }elseif(Date_format($ii['date'],'%m'))==04)
  {
  $level5_apr++;
  }elseif(Date_format($ii['date'],'%m'))==05)
  {
  $level5_may++;
  }elseif(Date_format($ii['date'],'%m'))==06)
  {
  $level5_jun++;
  }elseif(Date_format($ii['date'],'%m'))==07)
  {
  $level5_jul++;
  }elseif(Date_format($ii['date'],'%m'))==08)
  {
  $level5_aug++;
  }elseif(Date_format($ii['date'],'%m'))==09)
  {
  $level5_sep++;
  }elseif(Date_format($ii['date'],'%m'))==10)
  {
  $level5_oct++;
  }elseif(Date_format($ii['date'],'%m'))==11)
  {
  $level5_nov++;
  }elseif(Date_format($ii['date'],'%m'))==12)
  {
  $level5_dec++;
  }
}
}

this part of the code will now collect the result of the if statement

echo"
number of 5 stars: $level5  <br>
jan:$level5_jan     <br>
feb:$level5_feb     <br>
mar:$level5_mar     <br>
apr:$level5_apr     <br>
may:$level5_may     <br>
jun:$level5_jun     <br>
jul:$level5_jul     <br>
aug:$level5_aug     <br>
sep:$level5_sep     <br>
oct:$level5_oct     <br>
nov:$level5_nov     <br>
dec:$level5_dec     <br>
";

Answer 1

SQL:

"SELECT level, id, date AS UNIX_TIMESTAMP(date) FROM table WHERE year(date) = '2012' ORDER BY date"

PHP:

while($row = mysql_fetch_assoc($query))
{
    $levels = array(); // This is where the data for the monthly counting goes in
    $currentLevel = $row['level']; // The current level in the loop
    $currentDate = date('n', $row['date']); // The current month without leading zeroes

    if ($currentLevel === 5) // Check if the current level equals 5
    {
        $levels[$currentLevel][$currentDate] += 1;
    }
}

Should do what you want but it is not tested.

NOTE: It is not recommendet to use multiple query of course (bad performance)

EDIT

SELECT field1, field2, field3, date AS UNIX_TIMESTAMP(date) FROM table WHERE year(date) = '2012' ORDER BY date)

or try this (not sure if this works properly):

SELECT *, date AS UNIX_TIMESTAMP(date) FROM ...

UNIX_TIMESTAP

If your field is typeof timestamp or datetime UNIX_TIMESTAMP(date) will return the date in seconds since November 1971 (see: http://en.wikipedia.org/wiki/Unix_time) and here (see: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp)

With unix timestamp it is very easy to handle dates - and you can easily calculate with them and format to whatever you like.