Query MySql database by month into array

[xeonman13]

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!

[bradgrafelman]

Sounds like you just need to use COUNT(*) and a GROUP BY to group the rows by month. The MySQL manual page Counting Rows shows an example of this, and note that MySQL has a MONTH() function to easily extract the month portion of a date (e.g. for use in the GROUP BY clause).

[Derokorian]

Without seeing the table structure I would think something like this would work:

SELECT MONTHNAME(`date`), COUNT(*) FROM `table` WHERE YEAR(`date`) = '2011' GROUP BY MONTH(`date`)

Edit: Damnit BG!

[xeonman13]

Thanks. The count part i got im just confused of how to make it calculate for the past 3 or even 12 months. So the array would start with the previous month instead of just a Jan, Feb, Mar. Should be Feb 2012, Jan 2012, Dec 2011, Nov 2011.

[bradgrafelman]

xeonman13;10998019 wrote:

Thanks. The count part i got im just confused of how to make it calculate for the past 3 or even 12 months. So the array would start with the previous month instead of just a Jan, Feb, Mar. Should be Feb 2012, Jan 2012, Dec 2011, Nov 2011.

You'd simply need to adjust the WHERE clause so that it matches the range you expect. As an example, this would match all dates greater than or equal to the first day of the month that was three months ago from the current date:

dateCol >= (CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY) - INTERVAL 3 MONTH

[Derokorian]

Super bored wrote this:

<?php

set_time_limit(0);

$db = new mysqli('localhost','derokorian','derokorian','test');

if( !$db->query('CREATE TABLE IF NOT EXISTS `count_test` (`id` int unsigned not null auto_increment, `date` datetime not null, primary key (`id`) )') ) {
	die('Create table failed '.$db->error);
}

$day = new DateInterval('P1D');

$date = DateTime::CreateFromFormat('Y-m-d','2011-01-01');

if( $ins = $db->prepare('INSERT INTO `count_test` (`date`) VALUES (?)') ) {
	$ins->bind_param('s',$str);

for( $o=0; $o<500; $o++ ) {
	$cnt = rand(3,10);
	for( $i=0; $i<=$cnt; $i++ ) {
		$str = $date->format('Y-m-d') .  ' 12:00:00';
		$ins->execute();
	}
	$date->add($day);
}
} else {
	die('Prepare failed saying '.$db->error);
}


// THIS IS THE PART THAT SHOULD INTEREST YOU
$start = (date('Y') -1 ) .'-'. (date('m')) .'-01';

if( $res = $db->query('SELECT CONCAT_WS(\' \',YEAR(`date`),MONTHNAME(`date`)) as month,COUNT(*) as cnt FROM `count_test` WHERE `date` > \''.$start.'\' GROUP BY MONTH(`date`) ORDER BY `date` ASC') ) {
	while( $row = $res->fetch_assoc() ) {
		echo $row['month'] .' - '. $row['cnt'] .'<br>';
	}
} else {
	die('Select failed');
}

Which produces the following output:

2011 March - 481
2011 April - 450
2011 May - 337
2011 June - 215
2011 July - 221
2011 August - 239
2011 September - 236
2011 October - 214
2011 November - 248
2011 December - 222
2012 January - 226
2012 February - 223

Course this could change depending on the result of the rand function heh but it seems to be what you're looking for no?

[bradgrafelman]

Seems like it would be a lot easier to do it all in the WHERE clause. :p

[Derokorian]

Probably, but that doesn't mean there isn't more than one way to skin a cat (or in this case construct a query)

[xeonman13]

Ok guys, dont kill me here. I could be way off.
But here is whats going on.

my graph code requires this:

    $data = array(
        'Jan' => 12,
        'Feb' => 25,
        'Mar' => 0,
        'Apr' => 7,
        'May' => 80,
        'Jun' => 67,
        'Jul' => 45,
        'Aug' => 66,
        'Sep' => 23,
        'Oct' => 23,
        'Nov' => 78,
        'Dec' => 23
    );

I am inserting this. And if i do it alone it works, in my graph script it does not.

$data = array(
while ($row = mysql_fetch_array($rsQuerySignups))
{
"'".$row['month']. "' => " .$row['COUNT']."<br>"
}
);   

Any ideas? Really about to lose it!! 🙂🙂

[bradgrafelman]

This:

$data = array( 
while ($row = mysql_fetch_array($rsQuerySignups)) 
{ 
"'".$row['month']. "' => " .$row['COUNT']."<br>" 
} 
); 

won't work no matter where you put it or how you use it, mostly because it doesn't make any sense.

You might want to visit the PHP manual page for the [man]array[/man] type and/or find some basic tutorials. As an example, your code above should probably look more like:

$data = array();

while ($row = mysql_fetch_array($rsQuerySignups)) 
{ 
$data[$row['month']] = $row['COUNT'];
} 

[xeonman13]

I have no issue fetching the data. The issue is getting it into the format I placed below.

Needs to be exactly like this.

    $data = array( 
        'Jan' => 12, 
        'Feb' => 25, 
        'Mar' => 0, 
        'Apr' => 7, 
        'May' => 80, 
        'Jun' => 67, 
        'Jul' => 45, 
        'Aug' => 66, 
        'Sep' => 23, 
        'Oct' => 23, 
        'Nov' => 78, 
        'Dec' => 23 
    ); 

THE $data = array( is part of the graph script.
I need my data to start under that at the 'Jan' => 12,

[bradgrafelman]

See my reply above.

[xeonman13]

Here is the result. NOt formatted correctly. Cant have [] aroudn month.
cant say array. Need it EXACTLY as posted above.

Thanks for your help! and sorry. Im getting started and read alot but cant sort this out.

Array ( [Dec] => 56 [Feb] => 24 [Jan] => 44 )

[bradgrafelman]

xeonman13;10998041 wrote:

Here is the result. NOt formatted correctly. Cant have [] aroudn month.
cant say array. Need it EXACTLY as posted above.

That is the same format you posted above.

What you posted previously is PHP source code. What you posted in your most recent reply looks to be the output of a [man]print_r/man. Neither of those matter - all that matters is the structure of the array itself.

In other words, this:

$data = array(
    'Foo' => 'bar'
);

is exactly the same as:

$data = array();
$data['Foo'] = 'bar';

or even:

$data = array();
$data["\x46\x6f\x6f"] = "\x62\x61\x72";

[xeonman13]

ok understood now. THANKS.
How can i troubleshoot this?
The graph script is not liking it. it says
"the value of the key %s is not numeric"

Like in MySQL explain tells you what the query is doing.
How can I do that for the array to see EXACTLY what the script is seeing.

[bradgrafelman]

xeonman13;10998056 wrote:

The graph script is not liking it. it says
"the value of the key %s is not numeric"

Sounds like a problem with the graph script's code, since a) that isn't a very helpful error message (looks like it didn't go through printf/sprintf() like it was supposed to), and b) it's wrong (assuming your output above is correct).

xeonman13;10998056 wrote:

Like in MySQL explain tells you what the query is doing.
How can I do that for the array to see EXACTLY what the script is seeing.

[man]print_r/man or [man]var_dump/man (for more verbosity) as you've already done above.