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