在PHP / MySQL中使用标签进行搜索

I am struggling to find a solution to this. I have tags that are inserted into the database in this form

mytags=firsttag, secondtag, thirdtag

On the single page I need to show related items based on these tags aso I am using

SELECT * FROM posts WHERE tags LIKE \"%$mytags%\" ORDER BY views DESC LIMIT 5"

However this doesn't work much as it only searches for posts that have the exact same tagline (firsttag, secondtag, thirdtag) and not the posts that their tag contain one of the tags (firsttag, anothertag, andanothertag).

I thought of exploding mytags and then search using the first tag in the array, but this doesn't work very good.

Does anyone know an effective method of searching?

Why don't you use separate table for post tags keeping? I mean for example table 'posts', table 'tags' and relation table 'posts_tags'. Here is structure of 'posts':

id
title
content

Here is structure of 'tags':

id
tag_name

And here is structure if 'posts_tags':

id
post_id
tag_id

This is scheme of 'many to many' relationship model. And you can now simply find tags or/and posts through JOIN operator.

This is not a propper way to do it. Your database is not normalised. In short, it is impossible to do what you want, and even if it is, that'd be highly ineffective.

You have to create another table - Tags, and then another one to represent the connection of tags to Posts let's say TagsPosts, everything propperly indexed.

You should always keep your data normalised (one piece of data in each column). There's a solution to this problem using two tables; one for tags and one for posts and a MySQL LEFT JOIN. Below is a very minimal example.

Table posts

id  int(11) Auto Increment   
title   char(255)

Table tags

id  int(11)  
postid  int(11)  
tag char(64)

Query to search for posts with tags containing firstpost

(important)

SELECT p.title FROM posts p
LEFT JOIN tags t
ON t.postid=p.id
WHERE t.tag LIKE '%firstpost%'

Sample data

INSERT INTO `posts` (`id`, `title`) VALUES
(1, 'Post number 1'),
(2, 'Another post'),
(3, 'Third post');

INSERT INTO `tags` (`id`, `postid`, `tag`) VALUES
(0, 1,  'first_post'),
(0, 1,  'firstpost_secondtag'),
(0, 2,  'secondpost_firsttag');

As I said above, this is a very minimal example. The most important bit here is the use of LEFT JOIN and a separate tags table. It should be obvious to you that you simply replace firstpost in the LIKE statement with a PHP variable.

You might want to look into find_in_set() but also, I would highly recommend to normalize your database and use a proper structure.