applejuicerules Posted July 31, 2009 Share Posted July 31, 2009 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. Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted July 31, 2009 Share Posted July 31, 2009 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 ''; } Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 31, 2009 Share Posted July 31, 2009 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. Quote Link to comment Share on other sites More sharing options...
Mardoxx Posted July 31, 2009 Share Posted July 31, 2009 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!!! Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted July 31, 2009 Share Posted July 31, 2009 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted July 31, 2009 Share Posted July 31, 2009 <?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 ); ?> Quote Link to comment Share on other sites More sharing options...
Mardoxx Posted July 31, 2009 Share Posted July 31, 2009 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! Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted July 31, 2009 Share Posted July 31, 2009 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 Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted July 31, 2009 Share Posted July 31, 2009 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. Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted July 31, 2009 Share Posted July 31, 2009 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted August 1, 2009 Share Posted August 1, 2009 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 ); ?> Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted August 3, 2009 Share Posted August 3, 2009 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted August 3, 2009 Share Posted August 3, 2009 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. Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted August 3, 2009 Share Posted August 3, 2009 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. Quote Link to comment Share on other sites More sharing options...
KresentPhresh Posted August 3, 2009 Share Posted August 3, 2009 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... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.