使用PHP&创建相关或相似的帖子的MySQL [英] Creating a related or similar posts using PHP & MySQL

问题描述

我一直在四处寻找SO，并且想知道如何使用PHP& amp; amp;创建一个相关或相似的帖子以显示在我的网站上. MySQL的?怎么做的一个基本例子是什么?

I was looking around SO and was wondering how would I go about in creating a related or similar posts to display on my web site using PHP & MySQL? What is a basic example of how to do this?

推荐答案

使用MySQL全文搜索

Using the MySQL Full Text search MATCH (col1,col2,...) AGAINST (expr [search_modifier]) thing.

假设您的表格为articles，您需要查找有关当前帖子标题的相关帖子.这样做:

Let's say your table is articles and you need to find related posts about a title of current post. Do it like this:

SELECT *, MATCH(title, body) AGAINST('$CurrentPostTitle') AS score
FROM articles 
WHERE MATCH(title, body) AGAINST('$CurrentPostTitle') 
ORDER BY score DESC LIMIT 5

这将为您提供前5条相关的帖子.

This will give you top 5 related posts.

但是首先请记住通过运行以下查询为该表的列启用全文搜索:

But first remember to enabled Full Text search for that table's columns, by running this query:

ALTER TABLE articles ADD FULLTEXT (title, body);

:为什么不使用 LIKE :澄清OP:

因为它不会给出正确的结果.假设您当前的标题是"1980年的音乐"，并且您想要与此相关的文章.现在，如果您使用LIKE，则仅出现包含词"1980音乐"的序列的帖子.但是，如果使用MATCH ... AGAINST，则将出现包含Music OR 1980的帖子.此外，同时包含音乐"和"1980"的帖子将出现在顶部"，因为它给每个结果赋予SCORE，我们正在按照该分数进行排序.我希望这很清楚.

Because it will not give correct results. Let's say you current title is "Music of 1980" and you want related posts on that. Now, if you use LIKE then only the posts containing EXACTLY the sequence of words "Music of 1980" will appear. However, if you use MATCH ... AGAINST, then posts that contain Music OR 1980 will appear. Also, the posts that contain both Music and 1980 will appear on Top because it gives a SCORE to each results and we are sorting by that score.I hope that's clear.

:2 :

如果您有类别，则可以在SQL查询where clause中添加AND Category = '$CurrentCategory'以获得更具体的结果.

If you have categories, you can add AND Category = '$CurrentCategory' in the SQL query where clause to get more specific results.

:3:OP无法使用全文:

如果由于某种原因不能使用全文，则可以只显示5个来自同一类别的随机帖子.由于它们属于同一类别，因此它们至少在某种程度上相关:

If you can not use Full Text (for some reason), you can just show 5 random posts from the same category. As they are in same category, they are somehow related at least:

SELECT *
FROM articles 
WHERE Category = '$CurrentCategory'
LIMIT 5

编辑的语法:在MySQL代码中将LIMTI更改为LIMIT

Edited Syntax: Changed LIMTI to LIMIT in MySQL Code

这篇关于使用PHP&创建相关或相似的帖子的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！