Re: query, display questions - Mailing list pgsql-php

Michael,

----------------------------------------
$sql = "SELECT EXTRACT(DAY FROM posted) FROM healthnotes GROUP BY
EXTRACT(DAY FROM posted)";

Then loop through each of these results and do a further query

$sql = "SELECT * FROM healthnotes t1 WHERE EXTRACT(DAY FROM posted) = " .
$day . " ORDER BY posted ASC";

And print out the results for each of these.
----------------------------------------

Bascially the EXTRACT function allows you to pull out different parts of the
timestamp. See the following for a better description of the different bits
that can be pulled out.

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=functions-da
tetime.html#FUNCTIONS-DATETIME-EXTRACT

If you want to display the timestamp in PHP you could convert it to an
EPOCH, then use the php date() function to change the output format. i.e.
change the second sql query above to something like:

$sql = "SELECT *, EXTRACT(EPOCH FROM posted) AS note_epoch FROM healthnotes
t1 WHERE EXTRACT(DAY FROM posted) = " . $day . " ORDER BY posted ASC";

then in PHP feed the note_epoch data into date() like the following:

$timestamp_string = date("d/m/Y H:ia", $note_epoch);

Again there are loads of different output formats if you want, more details
can be found here:

http://www.php.net/manual/en/function.date.php

Hope that helps. If I havent quite understood you then please explain a bit
further.

Nick


----- Original Message -----
From: "Michael Hanna" <zen@hwcn.org>
To: <pgsql-php@postgresql.org>
Sent: Thursday, July 03, 2003 4:19 PM
Subject: [PHP] query, display questions


> I have a table:
>
> michael=# \d healthnotes
>                                     Table "public.healthnotes"
>   Column |           Type           |                          Modifiers
> --------+--------------------------
> +-------------------------------------------------------------
>   posted | timestamp with time zone | not null default
> ('now'::text)::timestamp(6) with time zone
>   notes  | text                     |
> Indexes: healthnotes_pkey primary key btree (posted)
>
> Often there are multiple entries per day. I want to display the day
> once, with all the entries on that day.
>
> So do I select * from notes and group by date, then write a nested
> for-loop in php that ignores the extra timestamp items? Seems a little
> inelegant. Or can I select distinct timestamps somehow after converting
> them to dates?
>
> Not sure how to go about this.
>
> Another question: I want to echo in an html page the timestamp and the
> note after it is entered. What php-pgsql commands do this?
>
> Michael
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>