Forming a php array from MySQL data with gaps in it?

I want to count the number of rows I have in a specific table bundled up in year-month buckets depending on the create date - I have SQL that will do that -
SELECT DATE_FORMAT( create_date, '%Y%m' ) AS date, count( entry_no ) AS counts FROM MyTable GROUP BY DATE_FORMAT( create_date, '%Y-%m') so far so good but there are gaps so for instance in Jan 2009 there may be now records so I don't have an entry for that data bucket but I want to report zero for that time period. I'm thinking that I need to set up an array initialized to all zeros for all time buckets and then fill in real values where they exist ...
i) How would I do that?
ii) Is there a better way?
me_patrickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theodorejsalvoCommented:
The easiest way to setup the array is to use keys that match your input.  For example, line 1 is to setup the array, lines 2 and 3 would be hardcoding the data, as a model.  Lines 5 and 6 would work under a loop.
$bucket = array();
$bucket['201004'] = 4;
$bucket['201003'] = 2;

$curDate = date("Ym");
$bucket[$curDate] = $value;

Open in new window

0
me_patrickAuthor Commented:
I'm sorry but I don't understand this response. Maybe you haven't understood the question or I'm missing something about the answer you've given me but there are no hard-coded values. Any values that exist with come from a database (see SELECT statement above). Have I missed something?
0
hieloCommented:

<?php
$sql = "SELECT DATE_FORMAT( create_date, '%Y%m' ) AS date, count( entry_no ) AS counts FROM MyTable GROUP BY DATE_FORMAT( create_date, '%Y-%m')";
$result=mysql_query($sql) or die( mysql_error() );
$cache=array();
while( $row=mysql_fetch_assoc($result)){
	$cache[ $row['date'] ] = $row['counts'];
}

$start=strtotime('01/01/2009');
$end=strtotime('04/01/2010');
while($start<$end){
	$key=date('Ym',$start);

	if( isset($cache[$key]) )
	{
		echo $cache[ $key ]['counts'];
	}
	else
	{
		echo 0;
	}
	$start=strtotime("+1 month",$start);
}

?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theodorejsalvoCommented:
The hardcoding was just showing how you could do it.  I'm not sure which way you're calling the data from MySQL, but I'm going to assume that you're using the mysql_result function and the result variable is called $result.  Now:
// Setup the array
$bucket = array();

// If we want to go back 1 year by month, for example, we'd setup the array as such...
for($i = 0; $i < 12; $i++)
{
  $curDate = date("Ym",mktime(0,0,0,idate("n")-$i,1,date("Y"))
  $bucket[$curDate] = 0;
}

// Now we can use "date" as the key, and "counts" as the value, assuming the query's already been called...
for($i = 0; $i < mysql_num_rows($result); $i++)
{
  $curDate = mysql_result($result, $i, 'date');
  $bucket[$curDate] = mysql_result($result, $i, 'counts');
}

Open in new window

0
me_patrickAuthor Commented:
Thanks very much guys. I have given slightly more to hielo (I hope you don't mind theodorejsalvo) as that solution appearers to be a little neater but I think you have both ended up saying much the same kind of thing.
You've been a great help.
Regards.
Patrick.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.