MySQL JOIN not working

Question

I am trying to complete a MySQL join from one table to another but it is not working. Ideally, I want to be able to do with with prepared statements (as it is more secure) but for now I am just trying to get this to work normally.

Here is my database setup:

Posts:

id | title | text 

1  | Evening | Something here...
2  | Trip away | Bets place to go...

Tags:

post_id | tag
1       | weather 
1       | Autumn 
2       | Holidays

This is my attempt so far:

  $tag = mysqli_real_escape_string($_GET['tag']);
  $select = mysqli_query($mysqli, "SELECT posts.id, posts.title FROM posts INNER JOIN tags ON tags.tag = ".$tag);

However this does not work. What I am trying to do is select all the posts with the relevant tag that has been searched for and then output this to the user. Please help I am really stuck

Edit: While loop the outputted data

  $select = mysqli_query($mysqli, "SELECT posts.id, posts.title FROM posts INNER JOIN tags ON tags.post_id = posts.id WHERE tags.tag = ".$tag);

  while($row = mysqli_fetch_array($select)) {

    echo print_r($row);

  }

score 3 · Accepted Answer · answered Oct 06 '14 at 19:47

3

your inner join syntax is incorrect

SELECT posts.id, posts.title 
FROM posts 
INNER JOIN tags ON tags.post_id = posts.id 
WHERE tags.tag = "some_tag_here"

you need to join the two tables together and then use a WHERE to filter by tag

answered Oct 06 '14 at 19:47

John Ruddell

25,283
6
57
86

Thanks! This is getting the query to work. How can I output this using a while loop? I have added my attempt as an edit to this question. – Harry Oct 06 '14 at 19:52
@Harry I would use `mysql_fetch_assoc($select)` something like [**THIS POST**](http://stackoverflow.com/questions/14456529/mysqli-fetch-array-while-loop-columns) – John Ruddell Oct 06 '14 at 19:56
@Harry my pleasure! thanks for the quick response and for accepting the answer! +1 reciprocated :) – John Ruddell Oct 06 '14 at 20:03

MySQL JOIN not working

1 Answers1