Looping through array and comparing to mySql results

[rvdb86]

Hi Guys,

 

I am trying to build a statistics system that shows the amount of interactions per month.

Each interaction is saved in a databas with the date.

 

I have pulled the data from the database and counted it using sql:

 

Code:

$timelineData = mysql_query("SELECT COUNT(date), EXTRACT(YEAR_MONTH FROM date) AS xpdate 
FROM table    
GROUP BY MONTH(date)");

 

Output:

Array
(
    [0] => 2
    [COUNT(date)] => 2
    [1] => 201206
    [xpdate] => 201206
)
Array
(
    [0] => 85
    [COUNT(date)] => 85
    [1] => 201207
    [xpdate] => 201207
)

 

As you can see from the output, I have 2 interactions in June and 85 in July.

 

So far so good...

 

I have a timeline that shows the last 7 months. Per month I want to show the amount of interactions.

 

Code:

$months = array();
for ($i = 0; $i < 7; $i++) {
	$timestamp = mktime(0, 0, 0, date('n') - $i, 1);
	$months[date('n', $timestamp)] = date('M', $timestamp);
}

$result = array_reverse($months);

foreach ($result as $name) {
           $numDate = date('Ym', strtotime($name));
	   //$monthlyXPs = '0';

	 while($line = mysql_fetch_array($timelineData)){

			echo "<pre>";
			print_r($line);
			echo "</pre>";

			if ($line['xpdate'] == $numDate){
				//echo "<li>".$line[0]."</li>";
				$monthlyXPs[$numDate] = $line[0];
	   		} else {
				//echo "<li>0</li>";
				$monthlyXPs[$numDate] = '0';
			}
		}
	   
        }

 

What I am attempting in the code above is, firstly to loop through the months, then to compare the month against the results of the database. If the month appears in the array, assign $monthlyXPs with the value of the number of Interactions. If the month does not appear, assign the value 0.

 

It doesn't seem to be working  , my output looks like this:

Array
(
    [201201] => 0
)

 

Any suggestions, help, corrections will be really appreciated! TIA!

[Barand]

I'd start out with a $results array like this

Array

(

    [201201] => 0

    [201202] => 0

    [201203] => 0

    [201204] => 0

    [201205] => 0

    [201206] => 0

    [201207] => 0

)

 

Then you can just assign the counts using your date as the key ($results[$date] = $count;). Where there is no data you are left with the 0 value.

 

[rvdb86]

Hey Barand,

 

Thanks again for helping me!

 

Using your suggestions I managed to come up with a solution, I dont think it is the most elegant coding around but I thought I would post it to help future readings of this post:

 

$result = array_reverse($months);
        foreach ($result as $name) {
           	$numDate = date('Ym', strtotime($name));
	 	$monthlyXPs[$numDate] = 0;
	}

	while($line = mysql_fetch_array($timelineData)){

foreach ($monthlyXPs as $key => $value) {


			if($key == $element){

				$monthlyXPs[$key] = $element;
			  }

		}

	}

 

Thanks Again!