在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?

我正在努力寻找解决方案。我有以这种形式插入数据库的标签 p>

mytags = firsttag，secondtag，thirdtag code> p>

单页我需要根据这些标签显示相关项目我正在使用 p>


  SELECT * FROM posts WHERE tags LIKE \“％$ mytags％\”ORDER BY views DESC LIMIT  5“
  code>  pre> 
 
 然而，这不起作用，因为它只搜索具有完全相同标语的帖子（firsttag，secondtag，thirdtag）而不是帖子 他们的标签包含一个标签（firsttag，anothertag和anothertag）。 p> 
 
 
我想爆炸mytags，然后使用数组中的第一个标签进行搜索，但这不是很好用 。 p> 
 
 
是否有人知道有效的搜索方法？ p> 
  div>

答

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.

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

Table posts

Table tags

Query to search for posts with tags containing firstpost

Sample data

相关推荐

Table `posts`

Table `tags`

Query to search for posts with tags containing `firstpost`