[SOLVED] Okay, I can't seem to wrap my head around this...

[applejuicerules]

I'm not even going to bother posting the current source code for what I'm trying to do because it's managed to become a huge mess that just makes no sense to even look at after hacking away at it for so long.

 

Instead, here's what I'm going to do - I'm going to give a verbose explanation of what it is I'm attempting to do, and if someone could fill me in on what I'm obviously overlooking, that would be awfully awesome...

 

Alright...

 

I've got this array of results from an SQL Query. I'm iterating through each one and performing some on-the-fly filtering that works okay as it is.

 

Basically, the only thing being returned in the query is a bunch of DATETIME Timestamps.

 

I strip the Date and Time from each one into two variables ($tsdate and $tstime, respectively), and for the purpose of this particular function, we can ignore the $tstime.

 

SO

 

What we end up with is a bunch of dates in ##-##-#### format. What I'm trying to do is this;

 

Each one of these timestamps correlates with a clicked advertisement on our ad-engine. What I'm trying to do is group the result-set so it will appear as the following:

 

$clicks Clicks on $date

 

Ultimately, what I'm trying to construct is an array like this:

 

$dates[] = array('date' => $date, 'clicks' => $clicks)

 

That way I can iterate through each element of the array and print the date and number of clicks. (I need to make use of these variables in the dynamic chart we're generating on the page, as well.)

 

Everything I've tried to do to make this work has broken one thing or another and now I'm totally stumped. Does anyone know a simple way to do this? Count the number of elements in an array that match a variable condition and then assign those as variables within a multi-dimensional array?

 

Or am I making this far more complex than it needs to be? Thanks in advance for your help.

[KresentPhresh]

And, uh, couple of extra notes:

 

1) This is the same guy as posted above - I'm soft-deleting the applejuicerules account since I finally figured out my old password here.

 

2) Here's the source code... Might as well show it - It just generates an error right now about an invalid argument being supplied for foreach(), but hopefully you can get an idea of what it is I'm trying to do...

 

$prevdate = 0;

	foreach ($click as $row)

		{

		// Here we separate the dates and times from the queried timestamp

		$tstime = substr($row->timestamp, -;
		$tsdate = substr($row->timestamp, 0, -9);

		// This if statement checks to see if the date matches the range we entered
		if ($tsdate >= $selectedstartdate && $tsdate <= $selectedenddate)

			{ 

				if ($tsdate !== $prevdate)
					{
						$dateSet = array('date' => $tsdate, 'clicks' => 1);
						continue;
					}
				else
					{
						$dateSet['date'] = $tsdate;
						$dateSet['clicks']++;
					}

				echo "" . $dateSet['clicks'] . " on " . $dateSet['date'] . "<br />";
				//$DataSet->AddPoint($dateSet['clicks'], "Serie1");
				$prevdate = $tsdate;	

			}

		else
			{ 
				echo ''; 
			}

[roopurt18]

If I understand correctly you have a table with a DATETIME column and you want to count the number of rows entered per day?

 

select date_format( `columnname`, 'Y-m-d' ) as `datecolumn`, count(*) as `n`
from `yourtable`
group by dayofyear( `columnname` )
order by `datecolumn`

 

And you can just add a WHERE-clause for your filtering probably.

[Mardoxx]

So what you want to do is group two arrays to one?

 

If so, say... and I'll put a script together to do it...

 

I've just been making one today!!!

[KresentPhresh]

I'm trying to avoid changing the SQL Query because it's part of a separate Controller file that is serving a few other functions.

 

I basically just need to manipulate the result I'm getting - I've queried the database to return all timestamps that match a specific ID (the Advertisement's ID) so we can generate a report of how many people have clicked on that ad.

 

The best I could do was get the page to go,

 

"1 Clicks on 07-29-2009

2 Clicks on 07-29-2009

3 Clicks on 07-29-2009

1 Clicks on 07-30-2009

2 Clicks on 07-30-2009

3 Clicks on 07-30-2009

4 Clicks on 07-30-2009"

 

The idea is, I can get this thing to count correctly, but I don't want it to spit out this line for every row we're iterating through. So obviously, I'm going to have to figure out some way to catch certain variables and re-output them after finishing going through the result-set.

 

In other words, I want to go from what's above to;

 

"3 Clicks on 07-29-2009

4 Clicks on 07-30-2009"

 

I can't even quite understand what this code is doing anymore, I've been tinkering with it all day and my head is starting to feel fuzzy.

 

 

[roopurt18]

<?php
$dates = array( '2007-07-29 00:00:12', '2007-06-29 00:00:12', '2007-08-29 00:00:12', '2007-08-29 00:00:12' );

$count = array();
foreach( $dates as $d ) {
  $d = date('Y-m-d', strtotime( $d ) );
  if( !isset( $count[$d] ) ) {
    $count[$d] = 1;
  }else{
    $count[$d]++;
  }
}
print_r( $count );
?>

[Mardoxx]

try a

<?php
while ($currentdate != $date)  {
//set counts here
}

 

 

then call the

 

echo "" . $dateSet['clicks'] . " on " . $dateSet['date'] . "<br />";

 

after the final click++ has been called

 

 

also pChart RULES!

[KresentPhresh]

This seems like it should be so much simpler than this.

 

Is there a way to iterate through a result set, make note of how many of the results match a variable condition (in this case, how many dates are the same), and output that date and its count?

 

For simplicity sake, how would I do this with a list of simple names?

 

If I had 10 rows in $result;

 

Peter

Peter

Peter

Joe

Joe

Joe

Joe

Joe

Quagmire

Quagmire

 

How would I output;

 

3 Peters

5 Joes

2 Quagmires

 

 

[MatthewJ]

Yes, there is an easier way. It is to use the database for what it is meant for like Roopurt originally showed in his first post.

[KresentPhresh]

Ugh, that just opens up a whole new can of worms - The site is all built in Kohana, so I'll have to alter the controller a bit to figure this out... This seems like a relatively simple operation that I figured there would be an easy way to accomplish with just PHP and the result-set the database already returns, but I'll just have to write some different queries and parse the report differently.

 

Thanks much.

[roopurt18]

Did you bother to try or look at the code below?

 

<?php
$dates = array( '2007-07-29 00:00:12', '2007-06-29 00:00:12', '2007-08-29 00:00:12', '2007-08-29 00:00:12' );

$count = array();
foreach( $dates as $d ) {
  $d = date('Y-m-d', strtotime( $d ) );
  if( !isset( $count[$d] ) ) {
    $count[$d] = 1;
  }else{
    $count[$d]++;
  }
}
print_r( $count );
?>

[KresentPhresh]

Did you bother to try or look at the code below?

 

I did, my good sir. It just prints one record - The total number of clicks, and it says they occurred on December 31st, 1969. I'll play around with this a bit and see what I can do.

[roopurt18]

The code I gave you, exactly as it is, displays:

Array (

  [2007-07-29] => 1

  [2007-06-29] => 1

  [2007-08-29] => 2

)

 

So if given an array strictly of dates it can count how often each date occurs in the array.  I see no reason why you can't modify it to your needs.  Or if necessary you can do a print_r() on your data so we can see how it's presented and help you on your way.

[KresentPhresh]

Okay, yeah, something isn't right - In the process of altering my SQL Query (or, rather, my Kohana ORM Query), something went out of whack in the Controller file. I don't doubt your code works, I'll just have to figure out what's going on. It's giving me this when doing a print_r of the query result:

 

Mysql_Result Object ( [fetch_type:protected] => mysql_fetch_object [return_type:protected] => stdClass [result:protected] => Resource id #55 [insert_id:protected] => [sql:protected] => SELECT `id`, `timestamp` FROM (`ad_clicks`) WHERE `id` = '13' [current_row:protected] => 0 [total_rows:protected] => 23 )

 

I do think your code may be the key I'm looking for, now I just have to backtrack and figure out what I screwed up while trying to get this to work last week. Thanks again, my good sah.

[KresentPhresh]

The code I gave you, exactly as it is, displays:

Array (

  [2007-07-29] => 1

  [2007-06-29] => 1

  [2007-08-29] => 2

)

 

So if given an array strictly of dates it can count how often each date occurs in the array.  I see no reason why you can't modify it to your needs.  Or if necessary you can do a print_r() on your data so we can see how it's presented and help you on your way.

 

...I love you...

 

Array ( [2009-07-24] => 3 [2009-07-27] => 6 [2009-07-28] => 7 [2009-07-29] => 7 )

 

...In ways that are only legal in about 6 or 7 states...