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

> > > > > > 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 > > > There is no comma between DATE(solarLandingDateTime) and solarLandingIP > which means the DATE column will use the alias 'solarLandingIP'. Is this > your intention? Or is the solarLandingIP another column from the table. > If the latter, you may want to do something like this: > > $foundTrackingRows=mysql_query("SELECT DISTINCT > DATE(solarLandingDateTime) AS solarLandingDate, > solarLandingIP, solarLandingDir, solarLandingDateTime > FROM ".$whichTable. > " ORDER BY solarLandingDateTime DESC > LIMIT $Maxrecs2Show") or die("query failed: " . mysql_error());> Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. > If you are aliasing a column it is better to use the optional AS keyword > to avoid confusion. > MySQL's DATE function returns dates formatted as 'YYYY-MM-DD' so DATE_FORMAT > is not needed here. > -- > Niel Archer > niel.archer (at) blueyonder.co.uk > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- Niel Archer niel.archer (at) blueyonder.co.uk