MySQL multiple tables access query

Question

Hi, I am new to web development... mostly i learn my own..

I got to design a database structure so to store items similar to tags and posts in a blog. The design would be:

_____________
Tag Table
TAGID | TAGNAME
_____________
_____________
Post Table
POSTID | POSTNAME
_____________
_____________
Tag Post Relation Table
TAGID | POSTID
_____________

Now what i have read online over the time, this is the best way to store TAGS and relate it to the posts.. (please correct me if i am wrong) Now my doubt is how to retrieve all TAGNAME(s) associated to a POSTID.

Sorry for such a newb question but even i was unable to figure out what query do i search in google.

And please recommend me some good notes/tutorial on MySQL.

Answer 1

how's this?

Select Tag.tagname
from tag, tagpost
where tagpost.postid = '$something'
    and tagpost.tagid = tag.tagid;

Alternate syntax with JOIN keyword:

 Select Tag.tagname
 from tag
    inner join tagpost on tagid = tagpost.tagid
 where tagpost.postid = `$something`

Answer 2

To get all tags for a specific post you'd execute :

select tags.tagname from tags
inner join tags_posts on tags_posts.tagid=tags.tagid
where tags_posts.postid=:postid

And bind the post ID to :postid.