pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
query, display questions
5+ messages / 5 participants
[nested] [flat]

* query, display questions
@ 2003-07-03 15:19  Michael Hanna <zen@hwcn.org>
  0 siblings, 4 replies; 5+ messages in thread

From: Michael Hanna @ 2003-07-03 15:19 UTC (permalink / raw)
  To: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query, display questions
@ 2003-07-03 15:31  scott.marlowe <scott.marlowe@ihs.com>
  parent: Michael Hanna <zen@hwcn.org>
  3 siblings, 0 replies; 5+ messages in thread

From: scott.marlowe @ 2003-07-03 15:31 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query, display questions
@ 2003-07-03 15:36  Bruno Wolff III <bruno@wolff.to>
  parent: Michael Hanna <zen@hwcn.org>
  3 siblings, 0 replies; 5+ messages in thread

From: Bruno Wolff III @ 2003-07-03 15:36 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; +Cc: pgsql-php

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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query, display questions
@ 2003-07-03 15:39  Nick Barr <nicky@chuckie.co.uk>
  parent: Michael Hanna <zen@hwcn.org>
  3 siblings, 0 replies; 5+ messages in thread

From: Nick Barr @ 2003-07-03 15:39 UTC (permalink / raw)
  To: Michael Hanna <zen@hwcn.org>; 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
>





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: query, display questions
@ 2003-07-03 15:41  Frank Bax <fbax@sympatico.ca>
  parent: Michael Hanna <zen@hwcn.org>
  3 siblings, 0 replies; 5+ messages in thread

From: Frank Bax @ 2003-07-03 15:41 UTC (permalink / raw)
  To: pgsql-php

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 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-07-03 15:41 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-03 15:19 query, display questions Michael Hanna <zen@hwcn.org>
2003-07-03 15:31 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-03 15:36 ` Bruno Wolff III <bruno@wolff.to>
2003-07-03 15:39 ` Nick Barr <nicky@chuckie.co.uk>
2003-07-03 15:41 ` Frank Bax <fbax@sympatico.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox