pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfaster output from php and postgres
15+ messages / 9 participants
[nested] [flat]
* faster output from php and postgres
@ 2003-05-23 15:39 Mukta Telang <mukta@darya.nio.org>
0 siblings, 2 replies; 15+ messages in thread
From: Mukta Telang @ 2003-05-23 15:39 UTC (permalink / raw)
To: pgsql-php@postgresql.org
Hi,
I am dealing with a database with many-to-many relation and have a php
script that joins these tables and displays the output.
But the problem is that after entering some 50 records the output is
very slow!
I can count till 50 before the script produces the output!
The complete description of the problem is written below..
Thanks in advance,
Mukta
description in detail:
database part:
Three tables: paper,author and author_paper
"A paper has many authors and an author has many papers."
In this way there is many to many relation.
The attributes of paper are: paper_id (primary key),title,journal,year
etc
The attributes of author are: author_id(primary key) and name
The attributes of author_paper are: author_id ,paper_id and level
( author with level=1 is the main author of the paper )
php part:
For an author:
1. get author_id from author table
2. select all paper_ids from author_paper table
3. For each of the above selected paper_ids:
a. print all the attributes of paper
b. select all authors of the paper and print their names in the
order
of their "level"
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-23 16:04 scott.marlowe <scott.marlowe@ihs.com>
parent: Mukta Telang <mukta@darya.nio.org>
1 sibling, 1 reply; 15+ messages in thread
From: scott.marlowe @ 2003-05-23 16:04 UTC (permalink / raw)
To: Mukta Telang <mukta@darya.nio.org>; +Cc: pgsql-php@postgresql.org
It's probably just a slow query.
Could you print out your query in PHP to the web browser, then cut and
paste it into the psql monitor to get an explain anaylze output?
i.e.
psql dbname
#> explain analyze select .... rest of query goes here;
And give us the output of that? Thanks.
On Fri, 23 May 2003, Mukta Telang wrote:
> Hi,
> I am dealing with a database with many-to-many relation and have a php
> script that joins these tables and displays the output.
> But the problem is that after entering some 50 records the output is
> very slow!
> I can count till 50 before the script produces the output!
> The complete description of the problem is written below..
> Thanks in advance,
> Mukta
>
>
>
>
> description in detail:
>
>
> database part:
>
> Three tables: paper,author and author_paper
>
> "A paper has many authors and an author has many papers."
> In this way there is many to many relation.
>
> The attributes of paper are: paper_id (primary key),title,journal,year
> etc
>
> The attributes of author are: author_id(primary key) and name
>
> The attributes of author_paper are: author_id ,paper_id and level
> ( author with level=1 is the main author of the paper )
>
>
>
> php part:
>
> For an author:
> 1. get author_id from author table
> 2. select all paper_ids from author_paper table
> 3. For each of the above selected paper_ids:
> a. print all the attributes of paper
> b. select all authors of the paper and print their names in the
> order
> of their "level"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-23 16:49 Frank Bax <fbax@sympatico.ca>
parent: Mukta Telang <mukta@darya.nio.org>
1 sibling, 0 replies; 15+ messages in thread
From: Frank Bax @ 2003-05-23 16:49 UTC (permalink / raw)
To: pgsql-php@postgresql.org
At 11:39 AM 5/23/03, Mukta Telang wrote:
>Hi,
>I am dealing with a database with many-to-many relation and have a php
>script that joins these tables and displays the output.
>But the problem is that after entering some 50 records the output is
>very slow!
>I can count till 50 before the script produces the output!
>The complete description of the problem is written below..
>Thanks in advance,
>Mukta
>
>
>
>
>description in detail:
>
>
>database part:
>
>Three tables: paper,author and author_paper
>
>"A paper has many authors and an author has many papers."
>In this way there is many to many relation.
>
>The attributes of paper are: paper_id (primary key),title,journal,year
>etc
>
>The attributes of author are: author_id(primary key) and name
>
>The attributes of author_paper are: author_id ,paper_id and level
>( author with level=1 is the main author of the paper )
>
>
>
>php part:
>
>For an author:
>1. get author_id from author table
>2. select all paper_ids from author_paper table
>3. For each of the above selected paper_ids:
> a. print all the attributes of paper
> b. select all authors of the paper and print their names in the
>order of their "level"
Are you doing this with one select, or four selects, or something in
between? IAC, this doesn't sond like a php issue, but rather an issue with
how you build your select statement. If your SELECT statment is generated
dynamically, then print it out when the script runs. Then copy/paste the
final SELECT statement, add "EXPLAIN" to the beginning of it and run it via
psql.
Now you have enough info to post a question to pgsql-sql@postgresql.org -
include the following:
- schema of various table involved (like you did above).
- copy of SELECT statement(s)
- output of EXPLAIN for the SELECT statement that is taking too long.
Frank
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-23 17:26 Lonnie VanZandt <lonniev@predictableresponse.com>
parent: scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Lonnie VanZandt @ 2003-05-23 17:26 UTC (permalink / raw)
To: 'scott.marlowe' <scott.marlowe@ihs.com>; +Cc: pgsql-php@postgresql.org
The entire report should/could be a single SQL query with multi-table
joins and order by specifications - but I believe Mukta is doing
individual table queries and looping over result sets in the PHP code.
Seeing actual code will help...
-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
Sent: Friday, May 23, 2003 10:04 AM
To: Mukta Telang
Cc: pgsql-php@postgresql.org
Subject: Re: [PHP] faster output from php and postgres
It's probably just a slow query.
Could you print out your query in PHP to the web browser, then cut and
paste it into the psql monitor to get an explain anaylze output?
i.e.
psql dbname
#> explain analyze select .... rest of query goes here;
And give us the output of that? Thanks.
On Fri, 23 May 2003, Mukta Telang wrote:
> Hi,
> I am dealing with a database with many-to-many relation and have a php
> script that joins these tables and displays the output. But the
> problem is that after entering some 50 records the output is very
> slow! I can count till 50 before the script produces the output!
> The complete description of the problem is written below..
> Thanks in advance,
> Mukta
>
>
>
>
> description in detail:
>
>
> database part:
>
> Three tables: paper,author and author_paper
>
> "A paper has many authors and an author has many papers."
> In this way there is many to many relation.
>
> The attributes of paper are: paper_id (primary key),title,journal,year
> etc
>
> The attributes of author are: author_id(primary key) and name
>
> The attributes of author_paper are: author_id ,paper_id and level (
> author with level=1 is the main author of the paper )
>
>
>
> php part:
>
> For an author:
> 1. get author_id from author table
> 2. select all paper_ids from author_paper table
> 3. For each of the above selected paper_ids:
> a. print all the attributes of paper
> b. select all authors of the paper and print their names in
> the order of their "level"
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-23 17:53 scott.marlowe <scott.marlowe@ihs.com>
parent: Lonnie VanZandt <lonniev@predictableresponse.com>
1 sibling, 2 replies; 15+ messages in thread
From: scott.marlowe @ 2003-05-23 17:53 UTC (permalink / raw)
To: Lonnie VanZandt <lonniev@predictableresponse.com>; +Cc: pgsql-php@postgresql.org
Dear god, I saw the word join in his explanation and didn't even read much
after that. I thought he meant join as in join.
Even so, I bet he's still doing something else wrong, I can loop across 50
queries in a blink of an eye. Maybe he's disconnecting / reconnecting on
every access, or worse, just connecting over and over.
I've seen some crazy code in PHP when folks are just beginning. I think I
wrote a bit of it, but that was back when kids got suspended for saying
"I'm using PHP." (<- true story, a kid had a run in with a teacher /
principle, and was nearly suspended for saying that.)
On Fri, 23 May 2003, Lonnie VanZandt wrote:
> The entire report should/could be a single SQL query with multi-table
> joins and order by specifications - but I believe Mukta is doing
> individual table queries and looping over result sets in the PHP code.
> Seeing actual code will help...
>
> -----Original Message-----
> From: pgsql-php-owner@postgresql.org
> [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
> Sent: Friday, May 23, 2003 10:04 AM
> To: Mukta Telang
> Cc: pgsql-php@postgresql.org
> Subject: Re: [PHP] faster output from php and postgres
>
>
> It's probably just a slow query.
>
> Could you print out your query in PHP to the web browser, then cut and
> paste it into the psql monitor to get an explain anaylze output?
>
> i.e.
> psql dbname
> #> explain analyze select .... rest of query goes here;
>
> And give us the output of that? Thanks.
>
> On Fri, 23 May 2003, Mukta Telang wrote:
>
> > Hi,
> > I am dealing with a database with many-to-many relation and have a php
>
> > script that joins these tables and displays the output. But the
> > problem is that after entering some 50 records the output is very
> > slow! I can count till 50 before the script produces the output!
> > The complete description of the problem is written below..
> > Thanks in advance,
> > Mukta
> >
> >
> >
> >
> > description in detail:
> >
> >
> > database part:
> >
> > Three tables: paper,author and author_paper
> >
> > "A paper has many authors and an author has many papers."
> > In this way there is many to many relation.
> >
> > The attributes of paper are: paper_id (primary key),title,journal,year
>
> > etc
> >
> > The attributes of author are: author_id(primary key) and name
> >
> > The attributes of author_paper are: author_id ,paper_id and level (
> > author with level=1 is the main author of the paper )
> >
> >
> >
> > php part:
> >
> > For an author:
> > 1. get author_id from author table
> > 2. select all paper_ids from author_paper table
> > 3. For each of the above selected paper_ids:
> > a. print all the attributes of paper
> > b. select all authors of the paper and print their names in
> > the order of their "level"
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-24 15:50 Chadwick Rolfs <cmr@shell.gis.net>
parent: scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 15+ messages in thread
From: Chadwick Rolfs @ 2003-05-24 15:50 UTC (permalink / raw)
To: pgsql-php@postgresql.org
I'm glad this came up, because I have the same type of problem. Except,
I don't see how a join can work... of course, I'm not really schooled in
this stuff.
I also have three tables: author, publication, and pub_auth.
There are multiple authors for some publications, so it is necessary to
check each publication selected for ALL authors. I'm doing this with a
foreach loop on the result of each publication key returned.
How would a join make this easier?
I'm browsing the pgsql-sql archives now, but that may take a week. I'm
not sure what to search for...
On Fri, 23 May 2003, scott.marlowe wrote:
> Dear god, I saw the word join in his explanation and didn't even read much
> after that. I thought he meant join as in join.
>
> Even so, I bet he's still doing something else wrong, I can loop across 50
> queries in a blink of an eye. Maybe he's disconnecting / reconnecting on
> every access, or worse, just connecting over and over.
>
> I've seen some crazy code in PHP when folks are just beginning. I think I
> wrote a bit of it, but that was back when kids got suspended for saying
> "I'm using PHP." (<- true story, a kid had a run in with a teacher /
> principle, and was nearly suspended for saying that.)
>
> On Fri, 23 May 2003, Lonnie VanZandt wrote:
>
> > The entire report should/could be a single SQL query with multi-table
> > joins and order by specifications - but I believe Mukta is doing
> > individual table queries and looping over result sets in the PHP code.
> > Seeing actual code will help...
> >
> > -----Original Message-----
> > From: pgsql-php-owner@postgresql.org
> > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
> > Sent: Friday, May 23, 2003 10:04 AM
> > To: Mukta Telang
> > Cc: pgsql-php@postgresql.org
> > Subject: Re: [PHP] faster output from php and postgres
> >
> >
> > It's probably just a slow query.
> >
> > Could you print out your query in PHP to the web browser, then cut and
> > paste it into the psql monitor to get an explain anaylze output?
> >
> > i.e.
> > psql dbname
> > #> explain analyze select .... rest of query goes here;
> >
> > And give us the output of that? Thanks.
> >
> > On Fri, 23 May 2003, Mukta Telang wrote:
> >
> > > Hi,
> > > I am dealing with a database with many-to-many relation and have a php
> >
> > > script that joins these tables and displays the output. But the
> > > problem is that after entering some 50 records the output is very
> > > slow! I can count till 50 before the script produces the output!
> > > The complete description of the problem is written below..
> > > Thanks in advance,
> > > Mukta
> > >
> > >
> > >
> > >
> > > description in detail:
> > >
> > >
> > > database part:
> > >
> > > Three tables: paper,author and author_paper
> > >
> > > "A paper has many authors and an author has many papers."
> > > In this way there is many to many relation.
> > >
> > > The attributes of paper are: paper_id (primary key),title,journal,year
> >
> > > etc
> > >
> > > The attributes of author are: author_id(primary key) and name
> > >
> > > The attributes of author_paper are: author_id ,paper_id and level (
> > > author with level=1 is the main author of the paper )
> > >
> > >
> > >
> > > php part:
> > >
> > > For an author:
> > > 1. get author_id from author table
> > > 2. select all paper_ids from author_paper table
> > > 3. For each of the above selected paper_ids:
> > > a. print all the attributes of paper
> > > b. select all authors of the paper and print their names in
> > > the order of their "level"
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
> > majordomo@postgresql.org
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-Chadwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-24 17:02 Chadwick Rolfs <cmr@shell.gis.net>
parent: Lonnie VanZandt <lonniev@predictableresponse.com>
1 sibling, 1 reply; 15+ messages in thread
From: Chadwick Rolfs @ 2003-05-24 17:02 UTC (permalink / raw)
To: Lonnie VanZandt <lonniev@predictableresponse.com>; +Cc: 'scott.marlowe' <scott.marlowe@ihs.com>; pgsql-php@postgresql.org; pgsql-sql
As a person with the same problem, I would love to know how to do this.
The concept escapes me, but maybe these lists could help with novice
sqlers.
If there are multiple authors per publication (my case), and each author
needs to be selected and displayed, how can that be done via a join?
One would need to query for all authors with each publication.
I'm cross-posting this, because it seems relevant on both ends.
Thanks!
On Fri, 23 May 2003, Lonnie VanZandt wrote:
> The entire report should/could be a single SQL query with multi-table
> joins and order by specifications - but I believe Mukta is doing
> individual table queries and looping over result sets in the PHP code.
> Seeing actual code will help...
>
> -----Original Message-----
> From: pgsql-php-owner@postgresql.org
> [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
> Sent: Friday, May 23, 2003 10:04 AM
> To: Mukta Telang
> Cc: pgsql-php@postgresql.org
> Subject: Re: [PHP] faster output from php and postgres
>
>
> It's probably just a slow query.
>
> Could you print out your query in PHP to the web browser, then cut and
> paste it into the psql monitor to get an explain anaylze output?
>
> i.e.
> psql dbname
> #> explain analyze select .... rest of query goes here;
>
> And give us the output of that? Thanks.
>
> On Fri, 23 May 2003, Mukta Telang wrote:
>
> > Hi,
> > I am dealing with a database with many-to-many relation and have a php
>
> > script that joins these tables and displays the output. But the
> > problem is that after entering some 50 records the output is very
> > slow! I can count till 50 before the script produces the output!
> > The complete description of the problem is written below..
> > Thanks in advance,
> > Mukta
> >
> >
> >
> >
> > description in detail:
> >
> >
> > database part:
> >
> > Three tables: paper,author and author_paper
> >
> > "A paper has many authors and an author has many papers."
> > In this way there is many to many relation.
> >
> > The attributes of paper are: paper_id (primary key),title,journal,year
>
> > etc
> >
> > The attributes of author are: author_id(primary key) and name
> >
> > The attributes of author_paper are: author_id ,paper_id and level (
> > author with level=1 is the main author of the paper )
> >
> >
> >
> > php part:
> >
> > For an author:
> > 1. get author_id from author table
> > 2. select all paper_ids from author_paper table
> > 3. For each of the above selected paper_ids:
> > a. print all the attributes of paper
> > b. select all authors of the paper and print their names in
> > the order of their "level"
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-Chadwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-24 22:00 Bruno Wolff III <bruno@wolff.to>
parent: Chadwick Rolfs <cmr@shell.gis.net>
0 siblings, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2003-05-24 22:00 UTC (permalink / raw)
To: Chadwick Rolfs <cmr@shell.gis.net>; +Cc: Lonnie VanZandt <lonniev@predictableresponse.com>; 'scott.marlowe' <scott.marlowe@ihs.com>; pgsql-php@postgresql.org; pgsql-sql
On Sat, May 24, 2003 at 13:02:29 -0400,
Chadwick Rolfs <cmr@shell.gis.net> wrote:
> As a person with the same problem, I would love to know how to do this.
> The concept escapes me, but maybe these lists could help with novice
> sqlers.
>
> If there are multiple authors per publication (my case), and each author
> needs to be selected and displayed, how can that be done via a join?
>
> One would need to query for all authors with each publication.
You would do something like:
select pub.pubname, auth.authname
from pub, auth, pub_auth
where
pub.pubid = pub_auth.pubid and
auth.authid = pub_auth.authid
order by pub.pubname, auth.authname;
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-25 02:01 Frank Bax <fbax@sympatico.ca>
parent: scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 15+ messages in thread
From: Frank Bax @ 2003-05-25 02:01 UTC (permalink / raw)
To: pgsql-php@postgresql.org
Finding previous examples of complex joins in archives is not likely an
easy thing to find.
pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
Change [database] to the name of your database - this command will dump out
schema relative to your request. Post the results to this list. Then ask
us the question "how do I write a SELECT that produces...[ you finish this
sentence]". Question probably more appropriate to the list you mentioned,
but I expect there are people here who are just as capable of answering the
question. I've even seen examples where the process goes through several
emails before SQL produces desired results exactly.
>How would a join make this easier?
I have always found that one properly constructed complex query is always
"cheaper" in runtime than numerous queries inside a foreach loop. Your
final query will likely include joining a table to itself (this can
sometimes be a difficult concept to grasp).
Frank
At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
>I'm glad this came up, because I have the same type of problem. Except,
>I don't see how a join can work... of course, I'm not really schooled in
>this stuff.
>
>I also have three tables: author, publication, and pub_auth.
>
>There are multiple authors for some publications, so it is necessary to
>check each publication selected for ALL authors. I'm doing this with a
>foreach loop on the result of each publication key returned.
>
>How would a join make this easier?
>
>I'm browsing the pgsql-sql archives now, but that may take a week. I'm
>not sure what to search for...
>
>On Fri, 23 May 2003, scott.marlowe wrote:
>
> > Dear god, I saw the word join in his explanation and didn't even read much
> > after that. I thought he meant join as in join.
> >
> > Even so, I bet he's still doing something else wrong, I can loop across 50
> > queries in a blink of an eye. Maybe he's disconnecting / reconnecting on
> > every access, or worse, just connecting over and over.
> >
> > I've seen some crazy code in PHP when folks are just beginning. I think I
> > wrote a bit of it, but that was back when kids got suspended for saying
> > "I'm using PHP." (<- true story, a kid had a run in with a teacher /
> > principle, and was nearly suspended for saying that.)
> >
> > On Fri, 23 May 2003, Lonnie VanZandt wrote:
> >
> > > The entire report should/could be a single SQL query with multi-table
> > > joins and order by specifications - but I believe Mukta is doing
> > > individual table queries and looping over result sets in the PHP code.
> > > Seeing actual code will help...
> > >
> > > -----Original Message-----
> > > From: pgsql-php-owner@postgresql.org
> > > [mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
> > > Sent: Friday, May 23, 2003 10:04 AM
> > > To: Mukta Telang
> > > Cc: pgsql-php@postgresql.org
> > > Subject: Re: [PHP] faster output from php and postgres
> > >
> > >
> > > It's probably just a slow query.
> > >
> > > Could you print out your query in PHP to the web browser, then cut and
> > > paste it into the psql monitor to get an explain anaylze output?
> > >
> > > i.e.
> > > psql dbname
> > > #> explain analyze select .... rest of query goes here;
> > >
> > > And give us the output of that? Thanks.
> > >
> > > On Fri, 23 May 2003, Mukta Telang wrote:
> > >
> > > > Hi,
> > > > I am dealing with a database with many-to-many relation and have a php
> > >
> > > > script that joins these tables and displays the output. But the
> > > > problem is that after entering some 50 records the output is very
> > > > slow! I can count till 50 before the script produces the output!
> > > > The complete description of the problem is written below..
> > > > Thanks in advance,
> > > > Mukta
> > > >
> > > >
> > > >
> > > >
> > > > description in detail:
> > > >
> > > >
> > > > database part:
> > > >
> > > > Three tables: paper,author and author_paper
> > > >
> > > > "A paper has many authors and an author has many papers."
> > > > In this way there is many to many relation.
> > > >
> > > > The attributes of paper are: paper_id (primary key),title,journal,year
> > >
> > > > etc
> > > >
> > > > The attributes of author are: author_id(primary key) and name
> > > >
> > > > The attributes of author_paper are: author_id ,paper_id and level (
> > > > author with level=1 is the main author of the paper )
> > > >
> > > >
> > > >
> > > > php part:
> > > >
> > > > For an author:
> > > > 1. get author_id from author table
> > > > 2. select all paper_ids from author_paper table
> > > > 3. For each of the above selected paper_ids:
> > > > a. print all the attributes of paper
> > > > b. select all authors of the paper and print their names in
> > > > the order of their "level"
> > > >
> > > > ---------------------------(end of
> > > > broadcast)---------------------------
> > > > TIP 1: subscribe and unsubscribe commands go to
> > > majordomo@postgresql.org
> > > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>-Chadwick
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-27 16:34 Chadwick Rolfs <cmr@shell.gis.net>
parent: Frank Bax <fbax@sympatico.ca>
0 siblings, 2 replies; 15+ messages in thread
From: Chadwick Rolfs @ 2003-05-27 16:34 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-php@postgresql.org; pgsql-sql
So, I have the same problem, but I need all authors for each publication
to show up in it's own column. I tried the full join query from a
suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
of ALL authors at once.
I'll do some RTFMing of the joins.. and post any results I get
BUT, right now, looping over each publication with php isn't taking that
long. I would like to know how to make this query, though!
Please let me know how to get a result like:
____________________________________________________
|All Authors|Title|Source|Year|Type|Length|Keywords|
----------------------------------------------------
If there is a way on the SQL side to do this, that is ;>
Here we go:
CREATE TABLE "author" (
"auth_id" integer DEFAULT
nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
"first" text,
"last" text,
"auth_last_updated" timestamp with time zone,
Constraint "author_temp_pkey" Primary Key ("auth_id")
);
CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
(auth_last_updated);
CREATE TABLE "publication" (
"copyis" text,
"pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
NOT NULL,
"title" text,
"source" text,
"year" text,
"month" text,
"length" text,
"type" text,
"keywords" text,
"copyright" text,
"abstract" text,
"pdformat" text,
"pub_last_updated" timestamp with time zone
);
CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
(pub_id);
CREATE INDEX keywords_publication_key ON publication USING btree
(keywords);
CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
btree (pub_last_updated);
CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
(pub_id);
CREATE TABLE "pub_auth" (
"pub_auth_id" integer DEFAULT
nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
"pub_id" integer,
"auth_id" integer,
Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
);
CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
On Sat, 24 May 2003, Frank Bax wrote:
> Finding previous examples of complex joins in archives is not likely an
> easy thing to find.
>
> pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
>
> Change [database] to the name of your database - this command will dump out
> schema relative to your request. Post the results to this list. Then ask
> us the question "how do I write a SELECT that produces...[ you finish this
> sentence]". Question probably more appropriate to the list you mentioned,
> but I expect there are people here who are just as capable of answering the
> question. I've even seen examples where the process goes through several
> emails before SQL produces desired results exactly.
>
> >How would a join make this easier?
>
> I have always found that one properly constructed complex query is always
> "cheaper" in runtime than numerous queries inside a foreach loop. Your
> final query will likely include joining a table to itself (this can
> sometimes be a difficult concept to grasp).
>
> Frank
>
>
> At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
>
>
> >I'm glad this came up, because I have the same type of problem. Except,
> >I don't see how a join can work... of course, I'm not really schooled in
> >this stuff.
> >
> >I also have three tables: author, publication, and pub_auth.
> >
> >There are multiple authors for some publications, so it is necessary to
> >check each publication selected for ALL authors. I'm doing this with a
> >foreach loop on the result of each publication key returned.
> >
> >How would a join make this easier?
> >
> >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> >not sure what to search for...
> >
-Chadwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] faster output from php and postgres
@ 2003-05-27 18:19 Richard Huxton <dev@archonet.com>
parent: Chadwick Rolfs <cmr@shell.gis.net>
1 sibling, 2 replies; 15+ messages in thread
From: Richard Huxton @ 2003-05-27 18:19 UTC (permalink / raw)
To: Chadwick Rolfs <cmr@shell.gis.net>; Frank Bax <fbax@sympatico.ca>; +Cc: pgsql-php@postgresql.org; pgsql-sql
On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
> So, I have the same problem, but I need all authors for each publication
> to show up in it's own column. I tried the full join query from a
> suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> of ALL authors at once.
>
> I'll do some RTFMing of the joins.. and post any results I get
>
> BUT, right now, looping over each publication with php isn't taking that
> long. I would like to know how to make this query, though!
>
> Please let me know how to get a result like:
> ____________________________________________________
>
> |All Authors|Title|Source|Year|Type|Length|Keywords|
Well, if you search the archives for terms "text", "concat", "aggregate" you
should come up with one solution. This involves writing your own aggregate
function, like SUM() but for text. Don't worry, it's not difficult. The only
issue is that you won't be able to guarantee the order of authors in the
field.
There might be something on this in my PostgreSQL Notes on
http://techdocs.postgresql.org/ too - can't remember for sure.
--
Richard Huxton
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [PHP] faster output from php and postgres
@ 2003-05-27 18:27 Chadwick Rolfs <cmr@shell.gis.net>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 15+ messages in thread
From: Chadwick Rolfs @ 2003-05-27 18:27 UTC (permalink / raw)
To: pgsql-sql
On Tue, 27 May 2003, Richard Huxton wrote:
> On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
> > So, I have the same problem, but I need all authors for each publication
> > to show up in it's own column. I tried the full join query from a
> > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> > of ALL authors at once.
> >
> > I'll do some RTFMing of the joins.. and post any results I get
> >
> > BUT, right now, looping over each publication with php isn't taking that
> > long. I would like to know how to make this query, though!
> >
> > Please let me know how to get a result like:
> > ____________________________________________________
> >
> > |All Authors|Title|Source|Year|Type|Length|Keywords|
>
> Well, if you search the archives for terms "text", "concat", "aggregate" you
> should come up with one solution. This involves writing your own aggregate
> function, like SUM() but for text. Don't worry, it's not difficult. The only
> issue is that you won't be able to guarantee the order of authors in the
> field.
>
> There might be something on this in my PostgreSQL Notes on
> http://techdocs.postgresql.org/ too - can't remember for sure.
>
> --
> Richard Huxton
A function is definitely in need. But I already have PHP doing something
of this sort. I realize that PostgreSQL would most likely do it faster.
What I meant by the result output above was that there would be _many_
results, but for each publication only ONE column for ALL authors in EACH
row. (*whew*) So perhaps a function, but not an aggregate function.
I'll look into functions next, as it seems that there is no single SQL
statement that can do this... I can't see how.
-Chadwick
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: faster output from php and postgres
@ 2003-05-27 18:54 Rod Taylor <rbt@rbt.ca>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 15+ messages in thread
From: Rod Taylor @ 2003-05-27 18:54 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Chadwick Rolfs <cmr@shell.gis.net>; Frank Bax <fbax@sympatico.ca>; pgsql-php@postgresql.org; pgsql-sql
On Tue, 2003-05-27 at 14:19, Richard Huxton wrote:
> On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote:
> > So, I have the same problem, but I need all authors for each publication
> > to show up in it's own column. I tried the full join query from a
> > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> > of ALL authors at once.
> >
> > I'll do some RTFMing of the joins.. and post any results I get
> >
> > BUT, right now, looping over each publication with php isn't taking that
> > long. I would like to know how to make this query, though!
> >
> > Please let me know how to get a result like:
> > ____________________________________________________
> >
> > |All Authors|Title|Source|Year|Type|Length|Keywords|
>
> Well, if you search the archives for terms "text", "concat", "aggregate" you
> should come up with one solution. This involves writing your own aggregate
> function, like SUM() but for text. Don't worry, it's not difficult. The only
> issue is that you won't be able to guarantee the order of authors in the
> field.
If order is required:
SELECT custom_aggregate(author) as authors
FROM (SELECT author FROM table ORDER BY author) AS tab;
The above should give you authors in alphabetical order if
custom_aggregate() was written to concatenate text.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] faster output from php and postgres
@ 2003-05-27 19:35 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Chadwick Rolfs <cmr@shell.gis.net>
1 sibling, 1 reply; 15+ messages in thread
From: Jean-Luc Lachance @ 2003-05-27 19:35 UTC (permalink / raw)
To: Chadwick Rolfs <cmr@shell.gis.net>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-php@postgresql.org; pgsql-sql
KISS
why not use PHP to concatenate the authors while pub_id is the same???
If you insist on having each author in its own column,
put them at the end and concatenate with <TD>.
jll
Chadwick Rolfs wrote:
>
> So, I have the same problem, but I need all authors for each publication
> to show up in it's own column. I tried the full join query from a
> suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> of ALL authors at once.
>
> I'll do some RTFMing of the joins.. and post any results I get
>
> BUT, right now, looping over each publication with php isn't taking that
> long. I would like to know how to make this query, though!
>
> Please let me know how to get a result like:
> ____________________________________________________
> |All Authors|Title|Source|Year|Type|Length|Keywords|
> ----------------------------------------------------
>
> If there is a way on the SQL side to do this, that is ;>
>
> Here we go:
>
> CREATE TABLE "author" (
> "auth_id" integer DEFAULT
> nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
> "first" text,
> "last" text,
> "auth_last_updated" timestamp with time zone,
> Constraint "author_temp_pkey" Primary Key ("auth_id")
> );
>
> CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
>
> CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
> (auth_last_updated);
>
> CREATE TABLE "publication" (
> "copyis" text,
> "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
> NOT NULL,
> "title" text,
> "source" text,
> "year" text,
> "month" text,
> "length" text,
> "type" text,
> "keywords" text,
> "copyright" text,
> "abstract" text,
> "pdformat" text,
> "pub_last_updated" timestamp with time zone
> );
>
> CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
> (pub_id);
>
> CREATE INDEX keywords_publication_key ON publication USING btree
> (keywords);
>
> CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
> btree (pub_last_updated);
>
> CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
> (pub_id);
>
> CREATE TABLE "pub_auth" (
> "pub_auth_id" integer DEFAULT
> nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
> "pub_id" integer,
> "auth_id" integer,
> Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
> );
>
> CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
>
> CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
>
> On Sat, 24 May 2003, Frank Bax wrote:
>
> > Finding previous examples of complex joins in archives is not likely an
> > easy thing to find.
> >
> > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
> >
> > Change [database] to the name of your database - this command will dump out
> > schema relative to your request. Post the results to this list. Then ask
> > us the question "how do I write a SELECT that produces...[ you finish this
> > sentence]". Question probably more appropriate to the list you mentioned,
> > but I expect there are people here who are just as capable of answering the
> > question. I've even seen examples where the process goes through several
> > emails before SQL produces desired results exactly.
> >
> > >How would a join make this easier?
> >
> > I have always found that one properly constructed complex query is always
> > "cheaper" in runtime than numerous queries inside a foreach loop. Your
> > final query will likely include joining a table to itself (this can
> > sometimes be a difficult concept to grasp).
> >
> > Frank
> >
> >
> > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
> >
> >
> > >I'm glad this came up, because I have the same type of problem. Except,
> > >I don't see how a join can work... of course, I'm not really schooled in
> > >this stuff.
> > >
> > >I also have three tables: author, publication, and pub_auth.
> > >
> > >There are multiple authors for some publications, so it is necessary to
> > >check each publication selected for ALL authors. I'm doing this with a
> > >foreach loop on the result of each publication key returned.
> > >
> > >How would a join make this easier?
> > >
> > >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> > >not sure what to search for...
> > >
>
> -Chadwick
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [SQL] faster output from php and postgres
@ 2003-05-27 20:29 Chadwick Rolfs <cmr@shell.gis.net>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 15+ messages in thread
From: Chadwick Rolfs @ 2003-05-27 20:29 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: Frank Bax <fbax@sympatico.ca>; pgsql-php@postgresql.org; pgsql-sql
Yes, this may be better than foreach()ing through each publication
returned.
But a plpgsql function may suit these needs much better.
On Tue, 27 May 2003, Jean-Luc Lachance wrote:
> KISS
>
> why not use PHP to concatenate the authors while pub_id is the same???
> If you insist on having each author in its own column,
> put them at the end and concatenate with <TD>.
>
> jll
>
>
> Chadwick Rolfs wrote:
> >
> > So, I have the same problem, but I need all authors for each publication
> > to show up in it's own column. I tried the full join query from a
> > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> > of ALL authors at once.
> >
> > I'll do some RTFMing of the joins.. and post any results I get
> >
> > BUT, right now, looping over each publication with php isn't taking that
> > long. I would like to know how to make this query, though!
> >
> > Please let me know how to get a result like:
> > ____________________________________________________
> > |All Authors|Title|Source|Year|Type|Length|Keywords|
> > ----------------------------------------------------
> >
> > If there is a way on the SQL side to do this, that is ;>
> >
> > Here we go:
> >
> > CREATE TABLE "author" (
> > "auth_id" integer DEFAULT
> > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
> > "first" text,
> > "last" text,
> > "auth_last_updated" timestamp with time zone,
> > Constraint "author_temp_pkey" Primary Key ("auth_id")
> > );
> >
> > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
> >
> > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
> > (auth_last_updated);
> >
> > CREATE TABLE "publication" (
> > "copyis" text,
> > "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
> > NOT NULL,
> > "title" text,
> > "source" text,
> > "year" text,
> > "month" text,
> > "length" text,
> > "type" text,
> > "keywords" text,
> > "copyright" text,
> > "abstract" text,
> > "pdformat" text,
> > "pub_last_updated" timestamp with time zone
> > );
> >
> > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
> > (pub_id);
> >
> > CREATE INDEX keywords_publication_key ON publication USING btree
> > (keywords);
> >
> > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
> > btree (pub_last_updated);
> >
> > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
> > (pub_id);
> >
> > CREATE TABLE "pub_auth" (
> > "pub_auth_id" integer DEFAULT
> > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
> > "pub_id" integer,
> > "auth_id" integer,
> > Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
> > );
> >
> > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
> >
> > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
> >
> > On Sat, 24 May 2003, Frank Bax wrote:
> >
> > > Finding previous examples of complex joins in archives is not likely an
> > > easy thing to find.
> > >
> > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
> > >
> > > Change [database] to the name of your database - this command will dump out
> > > schema relative to your request. Post the results to this list. Then ask
> > > us the question "how do I write a SELECT that produces...[ you finish this
> > > sentence]". Question probably more appropriate to the list you mentioned,
> > > but I expect there are people here who are just as capable of answering the
> > > question. I've even seen examples where the process goes through several
> > > emails before SQL produces desired results exactly.
> > >
> > > >How would a join make this easier?
> > >
> > > I have always found that one properly constructed complex query is always
> > > "cheaper" in runtime than numerous queries inside a foreach loop. Your
> > > final query will likely include joining a table to itself (this can
> > > sometimes be a difficult concept to grasp).
> > >
> > > Frank
> > >
> > >
> > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
> > >
> > >
> > > >I'm glad this came up, because I have the same type of problem. Except,
> > > >I don't see how a join can work... of course, I'm not really schooled in
> > > >this stuff.
> > > >
> > > >I also have three tables: author, publication, and pub_auth.
> > > >
> > > >There are multiple authors for some publications, so it is necessary to
> > > >check each publication selected for ALL authors. I'm doing this with a
> > > >foreach loop on the result of each publication key returned.
> > > >
> > > >How would a join make this easier?
> > > >
> > > >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> > > >not sure what to search for...
> > > >
> >
> > -Chadwick
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
-Chadwick
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2003-05-27 20:29 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-23 15:39 faster output from php and postgres Mukta Telang <mukta@darya.nio.org>
2003-05-23 16:04 ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-23 17:26 ` Lonnie VanZandt <lonniev@predictableresponse.com>
2003-05-23 17:53 ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-24 15:50 ` Chadwick Rolfs <cmr@shell.gis.net>
2003-05-25 02:01 ` Frank Bax <fbax@sympatico.ca>
2003-05-27 16:34 ` Chadwick Rolfs <cmr@shell.gis.net>
2003-05-27 18:19 ` Richard Huxton <dev@archonet.com>
2003-05-27 18:27 ` Chadwick Rolfs <cmr@shell.gis.net>
2003-05-27 18:54 ` Rod Taylor <rbt@rbt.ca>
2003-05-27 19:35 ` Jean-Luc Lachance <jllachan@nsd.ca>
2003-05-27 20:29 ` Chadwick Rolfs <cmr@shell.gis.net>
2003-05-24 17:02 ` Chadwick Rolfs <cmr@shell.gis.net>
2003-05-24 22:00 ` Bruno Wolff III <bruno@wolff.to>
2003-05-23 16:49 ` 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