Re: faster output from php and postgres

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(at)postgresql(dot)org
> [mailto:pgsql-php-owner(at)postgresql(dot)org] On Behalf Of scott.marlowe
> Sent: Friday, May 23, 2003 10:04 AM
> To: Mukta Telang
> Cc: pgsql-php(at)postgresql(dot)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(at)postgresql(dot)org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

-Chadwick