Select unexisting values

I have a database with images which can be tagged.

To tag them I put a value (which represents a color) in a database table called Tagged

Tagged

  ImgId  (example 5)
  TagId  (example 3)

I also have a table called Tags (the colors)

Tags

  TagId   (example 3)
  Tag     (example blue)

In the list with tags are almost 20 tags.

What I have is a page with images so 1 page is imageid 5

if I open the page I want a list of all Tag’s from Tags

If the TagId is in Tagged it must be like

TagId Selected

else just

TagId

How can I do this.

I have the database working to tag images but I can’t get it to ‘see’ if the tag is used.

What I have:

$sql = "SELECT *
        FROM Tags";
        $result = mysql_query("$sql");

    while($row = mysql_fetch_array($result))
      {
        
            echo "<input type="checkbox" name="color[]" value="".$row['TagId']."">";
        
        echo $row['TagId'];
        echo "<br>";
      }

I guess I need a join or what is the best method?

PS I know I should use mysqli but I use mysql for testing now I adjust it later to mysqli

Answer

You can use a LEFT JOIN with a GROUP BY to do one query against the database and get the complete result;

SELECT Tag, COUNT(ImgId) selected
FROM TAGS 
LEFT JOIN TAGGED
  ON tags.TagId = TAGGED.TagId
 AND TAGGED.ImgId = 5
GROUP BY Tag;

An SQLfiddle to test with.

A short explanation;

I use a LEFT JOIN to select all tags and all possibly matching entries in tagged with ImgId=5. If there is no match, the value of ImgId is NULL, if there is a match, it’s 5. This is a feature of LEFT JOIN.

I then count the number of ImgId‘s per Tag (the COUNT and GROUP BY do that). NULL does not count, so the count becomes 0, while 5 counts and gives the count 1 (remember, we restricted to hits only on a single ImgId, 5).