Loop and MySQL help!

Question

Okay, basically... i am trying to store the dates and month name from my database in this format. I am aiming store the month and then count the entries in that month and then store the count. This is my loop, but I have trouble formatting the array properly.

while ( $row = mysql_fetch_assoc($res) ) {
 if ($row['Date'] != $prev_date) {
  $values=array(
   $row['Date'] => $count,
  );

  $prev_date = $row['Date'];

 }

 $count++;

}
print_f($values);

You can see that I will always overwrite my previous array and I am not really adding entries into the array. I couldn't figure out how to do it. I'm basically trying to see the number of entries per month.

OLD Update: Currently learning the MYSQL thing that one commenter mentioned. I'll update when I get it.

Answer 1

Not sure I understand what you're trying to do. How about this?

$values[$row['Date']]++;

Answer 2

You can make this using group by in sql, some like this: select date, count(*) from table group by date

Edit: If you need only count by month use some like this: select MONTH(dateField) as NewDate, count(*) FROM table GROUP BY NewDate

Answer 3

I almost got it!

$values=array();
    while ( $row = mysql_fetch_assoc($res) ) {
     if ($row['Date'] != $prev_date) {
      $values[$row['Date']] =  $count;

      $prev_date = $row['Date'];
      $count = 0;
     }

     $count++;

    }

Update: Need a little help with this part.

My output:

Array ( [9] => [10] => 999 [11] => 986 )

999 was supposed to be stored in 9 and 986 was supposed to be stored in 10.

Update: Here's my new code, I would appreciate it if someone can show me a more efficient way.

 $sql = "SELECT MONTH(AddDate) AS Date, column_name FROM table ORDER BY AddDate ASC";
 $res = mysql_query($sql) or die(mysql_error());
 $prev_date = null;

$values=array();

    while ( $row = mysql_fetch_assoc($res) ) {
     if ( $row['Date'] != $prev_date) {
      $month = $row['Date'];
      $sql = "SELECT count(MONTH(AddDate)) AS EntryAmount FROM `table` WHERE MONTH(AddDate)=$month ";
      $countResults = mysql_query($sql) or die(mysql_error());
      if( $entryAmount = mysql_fetch_array($countResults) ) {
       $values[$row['Date']] =  $entryAmount['EntryAmount'];
      }
      $prev_date = $row['Date'];
     }
    }

Output:

Array ( [9] => 999 [10] => 986 [11] => 264 )

Answer 4

How about looping on the result of a query something like this:

SELECT COUNT(MONTH(AddDate)) AS EntryAmount, MONTH(AddDate) as MonthValue FROM table GROUP BY MONTH(AddDate)