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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.