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