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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Aug 2, 2009 at 11:08 PM, Govinda<govinda.webdnatalk@xxxxxxxxx> wrote:
>> On Sun, Aug 2, 2009 at 8:02 PM, Govinda<govinda.webdnatalk@xxxxxxxxx>
>> 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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux