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:
Authors has three fields:
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:
Now, if there is no authorid found, only those paragraphs with an author is returned.PHP Code:
$query ="SELECT id,header,summary,maintext,authorid
FROM content c, authors a
WHERE pageid =$pageid
AND c.authorid = a.authorid";
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).