Thread: query, display questions

query, display questions

From

Michael Hanna

Date:

03 July 2003, 15:19:11

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

Re: query, display questions

From

Bruno Wolff III

Date:

03 July 2003, 15:32:44

On Thu, Jul 03, 2003 at 11:19:14 -0400,
  Michael Hanna <zen@hwcn.org> wrote:
>
> 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?

I think the normal thing to do here is have the application check that
the date for the current row is the same as for the previous row
and suppress the date in that case. If the repeated information was
large enough that just transferring the rows from the backend to the
frontend was the bottleneck, then you might be better off getting
the list of per information in a cursor and then for each day
get the records for that day. I can't imagine this being the case
if the only redundant information was the date.

Re: query, display questions

From

"Nick Barr"

Date:

03 July 2003, 15:42:28

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
>

Re: query, display questions

From

"scott.marlowe"

Date:

03 July 2003, 15:42:37

On Thu, 3 Jul 2003, Michael Hanna wrote:

> 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?

I posted some untested code and posted it to phpbuilder to do this.  What
I do is just grab the whole data set and use a while loop to go through
each row.  I store the first column and then compare it on each iteration,
if it changes then print it out, otherwise print a blank kinda thing

http://www.phpbuilder.com/board/showthread.php?s=&postid=10373627#post10373627

Re: query, display questions

From

Frank Bax

Date:

03 July 2003, 15:55:54

At 11:19 AM 7/3/03, Michael Hanna wrote:

>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?


Retrieve 'posted' as a date *and* a timestamp in the same select:
         select posted::date as posted_date,* from notes order by date
Then do the grouping by date in PHP.


>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?


I think you want to use pg_last_oid to determine the oid of record just
inserted, then retrieve it for display?

Frank