Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?

On Sun, Aug 2, 2009 at 11:08 PM, Govinda<govinda.webdnatalk@gmail.com> wrote: >> On Sun, Aug 2, 2009 at 8:02 PM, Govinda<govinda.webdnatalk@gmail.com> >> wrote: >>>> >>>> You need to do this on the mysql side, not in php - php can't summarize >>>> the data before processing it, so you need to use something like the >>>> date() >>>> function in mysql on your timestamp column. >>>> >>>> >>>> >>>> >>>> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date >>>> >>>> without knowing your original query it's hard to give an example, but: >>>> >>>> select distinct date(timestamp_column) from table; >>> >>> Thanks Chris, >>> >>> I am getting closer, but so far it is not iterating only once per unique >>> 'date part of the datetime expression', it is returning all the rows in >>> the >>> table, including those with the very same date but different time in the >>> value of the 'solarLandingDateTime' column.  There is not alot of >>> discussion >>> in the mysql docs that I saw about how to work with DISTINCT.  I need to >>> grab data out of the 3 columns: solarLandingIP, solarLandingDir, >>> solarLandingDateTime  (this part of my SELECT is working). >>> >>> This is what I have: >>> >>> $foundTrackingRows=mysql_query("SELECT DISTINCT >>> DATE(solarLandingDateTime) >>> solarLandingIP, solarLandingDir, solarLandingDateTime FROM >>> ".$whichTable." >>> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query >>> failed: " .mysql_error()); >>> >>> -Govinda >> >> try >> >> foundTrackingRows=mysql_query("SELECT DISTINCT >> DATE_FORMAT('%Y-%m,%d',solarLandingDateTime) >> solarLandingIP, solarLandingDir, solarLandingDateTime FROM ".$whichTable.." >> ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query >> failed: " .mysql_error()); > > > Bastien, > > I just did try that.  I also tried this, (thinking there was a typo in your > version, in the DATE_FORMAT formatting string), : > > $foundTrackingRows=mysql_query("SELECT DISTINCT > DATE_FORMAT('%Y-%m-%d',solarLandingDateTime) solarLandingIP, > solarLandingDir, solarLandingDateTime FROM ".$whichTable." ORDER BY > solarLandingDateTime DESC LIMIT $Maxrecs2Show") or die("query failed: " > .mysql_error()); > > still no luck. > > -Govinda > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > What does the resultset look like? -- Bastien Cat, the other other white meat