查找带有常用标记的mysql条目

Question

I have articles stored in a MySQL table. Now I want to display related articles. So I integrated a field called "tags", which stores all tags separated by commas. Until now I search for common tags like this:

    foreach (explode(',', $article['tags']) as $key => $value) {
        if ($key != 0)
            $search.= ' OR';
        $search .= " `tags` LIKE '%" . mysql_real_escape_string($value) . "%'";
    }
    $query = "SELECT title, id  FROM `article` WHERE $search";

That created a query like this:

SELECT title, id FROM `article` WHERE `tags` LIKE '%tag1%' OR `tags` LIKE '%tag2%'

I am wondering if there is a more efficient way to solve this. The negative side of my current solution is the "%" which causes that extreme short Tags like "green" collide with "greenteam" .

Answer 1

You should store your tags in a different table "tag" and add relationships between each article and tag in an another table called "article_tag" for example.

TABLES

article
=======
id
title
...

tag
=======
id
label

article_tag
=======
article_id
tag_id

QUERY

SELECT article.id, article.title 
FROM article
JOIN article_tag ON article_tag.article_id = article.id
JOIN tag ON article_tag.tag_id = tag.id
WHERE tag.label IN ('tag1', 'tag2')