Query MySql database by month into array

xeonman13 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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

Derokorian · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

See my reply above.

xeonman13 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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 · Mar 1, 2012

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.

Query MySql database by month into array

Xxeonman13

Bbradgrafelman

DDerokorian

Xxeonman13

Bbradgrafelman

DDerokorian

Bbradgrafelman

DDerokorian

Xxeonman13

Bbradgrafelman

Xxeonman13

Bbradgrafelman

Xxeonman13

Bbradgrafelman

Xxeonman13

Bbradgrafelman