Help with Join

I need help with a join I think. Can't figure it out.

I have these tables:

Posts

`id` int(10) unsigned NOT NULL,
`title` varchar(140) DEFAULT NULL,
`text` text COLLATE utf8_unicode_ci

Posts tags

`post_id` int(10) unsigned NOT NULL,
`tag_id` int(10) unsigned NOT NULL

Tags

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(40) DEFAULT NULL

If I want to loop out all the posts with the tag team-fortress-2 how would one do that? I can't figure it out.

$q = mysql_query("SELECT * FROM ... WHERE $get['tag']");
while($r = mysql_fetch_array($q)) {

From stackoverflow

• SELECT p.*
  FROM posts p
  JOIN posttags pt ON pt.post_id = p.id
  JOIN tags t ON pt.tag_id = t.id
  WHERE t.name = 'team-fortress-2'
  

  is the specific example. In PHP you'd do this:

  $tag = mysql_real_escape_string($get['tag']);
  $sql = <<<END
  SELECT p.*
  FROM posts p
  JOIN posttags pt ON pt.post_id = p.id
  JOIN tags t ON pt.tag_id = t.id
  WHERE t.name = '$tag'
  END;
  $query = mysql_query($sql);
  if (!$query) {
    $error = mysql_error();
    die("Error $error for query $sql");
  }
  ...
  

  Escaping the tag is really important, particularly if you are accepting it from user input in any way.

  Guffa : p.post_id should be pt.post_id...

  Lucero : +1 for the warning about escaping the user input (SQL injection prevention)

• The SQL should look something like this:

  select
     p.id, p.title, p.text
  from
     Posts p
     inner join PostTags pt on pt.post_id = p.id
     inner join Tags t on t.id = pt.tag_id
  where
     t.name = 'team-fortress-2'