Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
| From: | Nisse Engström | Date: | Mon, 03 Aug 2009 09:52:11 +0000 |
| Subject: | Re: newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column? | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-46386@lists.php.net to get a copy of this message | ||
On Sun, 2 Aug 2009 16:30:35 -0600, Govinda wrote:
> I'm translating some code from another server-side language into PHP,
> and I need something that 'summarizes' results found from a MySQL
> SELECT. I.e. -
>
> $foundTrackingRows=mysql_query("SELECT...
> while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {...
>
> ..such that the while loop only loops *ONCE per unique _date_ found
> (regardless of the hour/min./sec.)* in my column which is of type
> TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
> PRIMARY KEY
>
> For example, if I have column values like these:
> 2009-08-01 07:01:00
> 2009-07-30 18:16:37
> 2009-07-30 17:49:06
> 2009-07-27 17:35:52
> 2009-07-27 17:24:21
> 2009-07-27 17:23:03
> ..then my while { loop would only fire 3 times.
You could use the date as an index:
... SELECT DATE(
datetimecolumn) AS date ...
while ($TrackingRow = mysql_fetch_object (...)) {
$data[$TrackingRow['date']] = $TrackingRow;
/* Store the last row from each set of dates */
}
or
while ($TrackingRow = mysql_fetch_object (...)) {
if (!isset ($data[$TrackingRow['date']])) {
$data[$TrackingRow['date']] = $TrackingRow;
}
/* Store the first row from each set of dates */
}
/Nisse