Query MySql database by month into array

Hello,
I have a table with a few hundred rows. each row contains a date field, for the date a student registered for a course.
I need to build an array which gives me a sum of signups for each month for the last 12 months.
Example, January, 12 signups
feb, 14 signups

The array should look like this after the query is complete.
In this format:

	$signupsbymonth=array(
		"Jan" => 110,
		"Feb" => 130,
		"Mar" => 215,
		"Apr" => 81,
		"May" => 175,
		"Jun" => 110,
		"Jul" => 190,
		"Aug" => 175,
		"Sep" => 390,
		"Oct" => 286,
		"Nov" => 150,
		"Dec" => 196
	);

can anyone assist? THANKS A MILLION!

SELECT MONTH(signupdate) AS mm
     , DATE_FORMAT(signupdate,'%b') AS mth
     , COUNT(*) AS signups
  FROM daTable
GROUP
    BY mm
ORDER
    BY mm

:cool:

thanks for this. did a query using the code provided and it did work,
However it joined all years together. would like array to say


$values=array(
"January 2012" => 25
"December 2011" => 30
"November 2011" => 22

And to go back 12 months. This query only gave me 2 months worth.

Also, how do I constuct the array? Would like to print it out in the format above.

doh!! i forgot the WHERE condition! :blush:

WHERE signupdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY
                             - INTERVAL 1 YEAR

and if you want to append the year to the month, change the DATE_FORMAT format string from ‘%b’ to ‘%b %Y’

:slight_smile:

ok perfect that worked. THANKS.now can anyone help with getting it into the array format above?