SQL JOIN get name and all tags for item

Question

Hi all,

this is my first ever JOIN clause and I did run into a problem. I want to echo all the necessary information for my items but I don't understand how to echo all the tags for one item, right now I get a list with duplicates of items but with different tags if more than one tag is assigned for an item. Any ideas? Better ways to do this is greatly appreciated as well.

$query = "SELECT categories.id, categories.category, spots.spot_name, spots.category_id, spots.description, spots.slug, districts.id, districts.district, tags.spot_id, tags.tag ".
 "FROM categories, spots, districts, tags ".
    "WHERE categories.id = spots.category_id AND districts.id = spots.district_id AND tags.spot_id = spots.id";

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

while ($row = @mysql_fetch_array($result)){
echo '<tr><td style="background:#000; color:#ccc;" class="tooltip" title="'.$row["description"].'Tags: '.$row["tag"].'"><a style="color:#fff;" href="/'.$row["slug"].'">'.$row["spot_name"].'</a></td>
<td>'.$row["category"].'</td>
<td>'.$row["district"].'</td>
<td>****</td>
</tr>  
';
}

Thanks a million,

Anders

Answer 1

Change your Query and add left join like this: $query= "SELECT c.id, c.category, s.spot_name, s.category_id, s.description,". " s.slug, d.id, d.district, t.spot_id, t.tag". " FROM categories AS c, spots AS s, districts AS d". " JOIN tags AS t ON s.id = t.spot_id". " WHERE c.id = s.category_id AND d.id = s.district_id";

Answer 2

This is how your query looks much better to read:

SELECT c.id, c.category, s.spot_name, s.category_id, s.description, s.slug, d.id, d.district, t.spot_id, t.tag 
  FROM spots AS s
  LEFT JOIN categories AS c ON c.id = s.category_id
  LEFT JOIN districts AS d ON d.id = s.district_id
  LEFT JOIN tags AS t ON t.spot_id = s.id

To get all spots you like to issue this query:

SELECT c.id, c.category, s.spot_name, s.category_id, s.description, s.slug, d.id, d.district
  FROM spots AS s
  LEFT JOIN categories AS c ON c.id = s.category_id
  LEFT JOIN districts AS d ON d.id = s.district_id

Now you can loop over all the spots and get their tags:

'SELECT t.tag FROM tags WHERE t.spot_id = '. (int)$spot_id

Answer 3

Hi all,

with the help of JochenJung I now have the following code but still can't figure out how to get it to work:

<?php

$query = 'SELECT c.id, c.category, s.spot_name, s.category_id, s.description, s.slug, d.id, d.district, t.spot_id, t.tag 
  FROM spots AS s
  LEFT JOIN categories AS c ON c.id = s.category_id
  LEFT JOIN districts AS d ON d.id = s.district_id
  LEFT JOIN tags AS t ON t.spot_id = s.id'. 'SELECT t.tag FROM tags WHERE t.spot_id = '. (int)$spot_id;

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

while ($row = @mysql_fetch_array($result)){

echo '<tr><td style="background:#000; color:#ccc;" class="tooltip" title="'.$row["description"].'Tags: '.$row["tag"].'"><a style="color:#fff;" href="/'.$row["slug"].'">'.$row["spot_name"].'</a></td>
<td>'.$row["category"].'</td>
<td>'.$row["district"].'</td>
<td>****</td>
</tr>  
';
}
?>

Any ideas?