Normalisation: Newbie question (yeah so shoot me)

[Kwitcherbitchen]

Ok, im a php/mysql newbie (but learning) and i'm trying to write a very simple blog script for my site. The problem I have at the moment (and i'm in the planning stages 😛) is that I have three tables in my database as follows:

tblPosts

ID
Time
Date
Music
Mood
Content

tblComments

ID
Author
Email
Address
Time
Date
Content

tblPostComments

ID
PostID
CommentID

Which I think is about right. Now, clearly what I want to do is have a page where the stuff from the posts table is called and printed (no problem, done that) along with a link to the comments. What I really need to know is how I can have it so I query the database to find only the comments that are related to the post... I have a link table in there so what do I do now?

It'd end up outputting like:

blah blah blah
[ 2 comments - post comment ]

blah bla blah?
[ 1 comment - post comment ]

Do you understand what i'm on about or am I waffling? 😛

[daphreeek]

It would be easier if you got rid of the link table and just put a post_id field in the comments table (in this put the ID relating to which post was commented on from the posts table).

then when you go to show comments just do this:

$result = mysql_query("SELECT * FROM tblComments WHERE post_id=$id",$db);
$myrow = mysql_fetch_array($result);

and list them as normal 🙂

It is possible to use a link table but it is a touch more complicated:

$PostID = 5;    // this is the id from tblPosts

$result = mysql_query("SELECT tblComments.*, tblPostComments.* FROM tblComments, tblPostComments WHERE tblComments.ID=tblPostComments.CommentID AND tblPostComments.PostID=$PostID");

Hope that helps
- Matt

[Kwitcherbitchen]

That helped a lot, thanks 🙂