SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question HELP: Question relating to Foreign Keys (mySQL)

    I know that mySQL doesn't support foreign keys (in the traditional sense of the word) but I've a question on something I'm stuck on:

    I have two tables, let's say content and authors
    Content has six fields:

    id,
    pageid,
    header,
    summary,
    maintext,
    authorid

    Authors has three fields:
    authorid,
    authorname,
    authoremail

    I want content.authorid to reference author.authorid BUT I also want a paragraph to have the option of having no author.

    So, when doing a query, I want to return all the content for a page (pageid) and the corresponding author (if present).

    The query I'm using (through PHP) is:

    PHP Code:
    $query ="SELECT id,header,summary,maintext,authorid
    FROM content c, authors a
    WHERE pageid =
    $pageid
    AND c.authorid = a.authorid"

    Now, if there is no authorid found, only those paragraphs with an author is returned.

    How do I get around this?
    One solution I've come up with is to put a dummy/default entry in the authors table with an authorid of 0, so that every paragraph that doesn't have a 'real' author still has a matching row in the author table (the dummy row).

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it sounds like you need a LEFT JOIN which will force a row (with NULL values) to appear from the authors table for each in the content table:

    PHP Code:
    $query ="SELECT id, header, summary, maintext, authorid
    FROM content c
    LEFT JOIN authors a ON c.authorid=a.authorid
    WHERE pageid=
    $pageid"
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that Matt,

    Do you know of a way I can get around not having this 'NULL' row at all?

    The relationship I'm looking for is that each content row can have, though doesn't have to have, an author.

    The results I want back are each row from content (with author if it exists - otherwise just the content)

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DR_LaRRY_PEpPeR means that the LEFT JOIN will result in the Authorid column value being NULL where there's no matching Authourid in the Authors table.

    Try the following. It should return all Content rows and each row will have either NULL's for both the Authorid and AuthorName columns, for Content without an Author, or the relevant data, for Content with an Author.

    Code:
    SELECT	c.id,
    	c.header,
    	c.summary,
    	c.maintext,
    	c.authorid,
    	a.authorname
    FROM 	content c LEFT OUTER JOIN authors a ON c.authorid = a.authorid
    WHERE	pageid = <page number>

  5. #5
    SitePoint Addict lveale's Avatar
    Join Date
    Jun 2001
    Location
    Dublin
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Much appreciated

    Shane,
    go raibh mile maith agat go lear.
    (as Bearla: a thousand thanks to you)

  6. #6
    SitePoint Zealot thespian's Avatar
    Join Date
    Sep 2000
    Location
    South Africa
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: HELP: Question relating to Foreign Keys (mySQL)

    Iveale,

    I have no experience with either mySQL or with PHP, but if my understanding is correct, the selection is done by SQL commands anyway and with them I may be able to help.

    This is just a stab in the dark and I have no idea what it will result in.
    Code:
    SELECT id,header,summary,maintext,authorid
    FROM content c, authors a
         WHERE (pageid =$pageid  AND
                c.authorid = a.authorid")
            OR (pageid =$pageid  AND 
                c.authorid = "");
    Please let me know what happens (if anything)
    Bill Conté [Protected by Psalm 91]
    Web Mechanix
    Growing OLD is Mandatory - Growing UP is Optional!


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
  •