HELP: Question relating to Foreign Keys (mySQL)

[lveale]

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:

$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).

[DR_LaRRY_PEpPeR]

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:

$query ="SELECT id, header, summary, maintext, authorid
FROM content c
LEFT JOIN authors a ON c.authorid=a.authorid
WHERE pageid=$pageid"; 

[lveale]

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)

[shane]

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.

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>

[lveale]

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

[thespian]

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.

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)