Enhance Query Fu

    Date: 02/07/05 (MySQL Communtiy)    Keywords: mysql, database, sql, web

    For a website, I have to write a suite of scripts interacting with a database of bibliographic references. Some pages will have an embedded request for a set of the latest titles by an author, and some pages will have an embedded list of titles from which to generate full references. Additionally, I have to build a simple search engine that will query by author, title, publication or abstract.

    The search engine is a later problem, and I've already got searching for an embedded list of titles working. So right now I have to solve the author problem. I'm a MySQL newbie and am having problems with the following dilemma:

    The problem with citations is that there's no fixed number of authors for a book. As a consequence, the database has a table for the papers, a table for the authors, and a link table with columns for a paper's foreign key and author's foreign key. A paper with three authors populates three rows of the link table.

    My challenge is that to pull a complete citation when I search by author, I currently have to make two database queries:
    SELECT name, id_people, papers_fk FROM papers pa, link li, people pe WHERE name LIKE 'Smith' AND pa.id_paper=li.papers_fk AND pe.id_people=li.people_fk
    pulls the IDs of papers that Smith has contributed to.

    I take that list of IDs and loop through
    SELECT name, id_people, title, id_paper FROM papers pa, link li, people pe WHERE li.papers_fk='$titleid' AND pa.id_paper='$titleid' AND pe.id_people=li.people_fk
    entering a different paper ID into $titleid on each loop to generate arrays of citations. A script will distill the arrays into comma-separated lists of authors and format the results.

    My question is: Is there a way to distill this into a single query? Which is to say: Can I use a single query to get the complete list of authors for each title that Smith has contributed to?

    The approach I have now will work, even if it ends up sending a lot of queries for each web page hit, but being able to streamline this query will both make this page more efficient and give me a leg up on writing the full text (citation + abstract) search section.

    Source: http://www.livejournal.com/community/mysql/47657.html

« My brain hurts... || MySQL support for AMD64 »

antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home