Re: faster output from php and postgres

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)