SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    querying 1 to M table

    Ciao!

    I'm planning to upgrade my website with this database layout:

    authors
    ------
    id
    first_name
    etc

    articles
    --------
    id
    pub_date
    etc

    articles_en
    -----------
    id
    title
    content
    etc

    articles_it
    -----------
    id
    title
    content
    etc

    articles_authors
    ----------------
    article_id
    author_id

    I'm using mysql but I have installed into the server postgres too.

    To obtain an article I can do
    SELECT * FROM articles, articles_$lang WHERE articles.id = $id AND articles_$lang.id = $id

    To obtain the authors list I can do
    SELECT * FROM authors, articles_authors WHERE author_id = authors.id AND article_id = $id

    ehm, ehm I did not test them...

    I ask if it is possible to merge those two queries.
    Practically I want to obtain the article data and all the authors of that article with a single query.
    Is that possible ?
    Has a DB design sense what I'm asking for ?

    As I said previously I could use postregs too,
    but actually I would prefer mysql.
    I'm using php.

    Thank you in advance...
    Last edited by pippo; Jun 7, 2002 at 03:35.
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03

  2. #2
    Rehab is for quiters! spartan's Avatar
    Join Date
    Apr 2002
    Location
    Cape Town, South Africa
    Posts
    343
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you could... in a way, but you would get a result that duplicates all the article info.

    lets say you had 3 authors for article number 4

    you're result would look like this
    Code:
    Article_id  publish_date   Author_name 
    04          08/06/2001      J. Smith
    04          08/06/2001      C. Peterson
    04          08/06/2001      P. Roberts
    Spartan
    ---------------------
    It's like our sergeant told us before one trip into the jungle. Men! Fifty of you are leaving on a mission. Twenty-five of you ain't coming back.
    -Mr.Payne

  3. #3
    FreeBSD The Power to Serve silver trophy pippo's Avatar
    Join Date
    Jul 2001
    Location
    Italy
    Posts
    4,514
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx for the tips!

    If I keep the query separated when I will have to show the list of all articles and related authors the situation will became more complicated.

    So maybe changing the database layout as:

    authors
    ------
    id
    first_name
    etc

    articles
    --------
    id
    pub_date
    author1
    author2
    etc

    articles_en
    -----------
    id
    title
    content
    etc

    articles_it
    -----------
    id
    title
    content
    etc

    could help me a bit.
    Then with two LEFT JOIN I will do the trick.

    Typically I will always have only one authors for articles, but for books it could be not always true.
    Actually the worst case is two authors for the same book.

    When one day I will have three authors for a book I will add a column to the table and I will have to do three LEFT JOIN.

    Most of the time author2 will be an empty cell.

    I'm not too expert with dbs so every suggestion will be appreciated!
    Mr Andrea
    Former Hosting Team Advisor
    Former Advisor of '03


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •