我如何使用标签搜索来查找网站? [英] how do I find websites using tag search?
问题描述
我有以下标记
表:
web |标签
--------------------------------------------- ---------------
google.com |搜索,谷歌,searchengine,引擎,网站
facebook.com | Facebook,社交,网络,朋友
youtube.com |视频,YouTube,视频,娱乐
yahoo.com |雅虎,搜索,电子邮件,新闻,searchengine
bing.com | search,searchengine,microsoft,bing,tools
我试图通过标签搜索来获得这个表格中的网站列表。
例如,如果通过 google.com
进行搜索,那么我想要从上面的示例表中列出 yahoo
和 bing
。
我如何用PHP和MySQL实现这一目标? (我为此表启用了 FULL TEXT SEARCH
)
像这样可能会给你想要的结果,但它会很慢。也许别人可以提供更高效的解决方案:
SELECT t1。* FROM tags AS t1 JOIN标记AS t2
ON LIST_INTERSECT(t1.tags,t2.tags)!=''
WHERE t1.web ='google.com'
你还需要这个存储函数(只要你连接到服务器并选择你的数据库,就把这段代码复制并粘贴到mysql客户端中):
DELIMITER $$
CREATE FUNCTION LIST_INTERSECT(
list1 VARCHAR(255),list2 VARCHAR(255)
)RETURNS VARCHAR 255)
BEGIN
SET @delim =',';
SET @list = list1;
SET @overlap ='';
LOOPING:LOOP
IF(LOCATE(@delim,@list)> 0)THEN
SET @word = SUBSTRING_INDEX(@list,@delim,1);
SET @list = SUBSTR(@list,LOCATE(@delim,@list)+ 1);
ELSE
SET @word = @list;
SET @list = NULL;
END IF;
IF(CONCAT(',',list2,',')LIKE CONCAT('%,',@ word,',%'))THEN
SET @newword = @word;
IF(@overlap!='')THEN
SET @newword = CONCAT(',',@word);
END IF;
SET @overlap = CONCAT(@overlap,@newword);
END IF;
IF(@list IS NULL)THEN
LEAVE LOOPING;
END IF;
END LOOP LOOPING;
RETURN @overlap;
END $$
DELIMITER;
( 本质上,这段代码在 I have the following What I am trying to achieve is searching by tags to get a list of websites from this table. If for example some one search by How can I achieve this with PHP and MySQL? (I have enabled Something like this might give you the results you want, but it would be quite slow. Maybe someone else can provide a more efficient solution: And you'll also need this stored function (just copy and paste this code into the mysql client once you've connected to the server and selected your database): (The Essentially this code looks for a site in the 这篇关于我如何使用标签搜索来查找网站?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! DELIMITER
命令是更改语句分隔符从;改为$$并返回。您需要执行此操作以定义
web
列,那么它会在标签
列中找到所有其他共享关键字的网站。使用此功能,如果您搜索google.com,它也会返回bing.com和yahoo.com,因为这三条记录都在中包含搜索和searchengine标签
。tags
table:web | tags
------------------------------------------------------------
google.com | search,google,searchengine,engine,web
facebook.com | facebook,social,networking,friends
youtube.com | video,youtube,videos,entertainment
yahoo.com | yahoo,search,email,news,searchengine
bing.com | search,searchengine,microsoft,bing,tools
google.com
then I want to list yahoo
and bing
from above sample table.FULL TEXT SEARCH
for this table)SELECT t1.* FROM tags AS t1 JOIN tags AS t2
ON LIST_INTERSECT(t1.tags, t2.tags) != ''
WHERE t1.web='google.com'
DELIMITER $$
CREATE FUNCTION LIST_INTERSECT(
list1 VARCHAR(255), list2 VARCHAR(255)
) RETURNS VARCHAR(255)
BEGIN
SET @delim = ',';
SET @list = list1;
SET @overlap = '';
LOOPING: LOOP
IF (LOCATE(@delim, @list) > 0) THEN
SET @word = SUBSTRING_INDEX(@list, @delim, 1);
SET @list = SUBSTR(@list, LOCATE(@delim, @list) + 1);
ELSE
SET @word = @list;
SET @list = NULL;
END IF;
IF (CONCAT(',',list2,',') LIKE CONCAT('%,',@word,',%')) THEN
SET @newword = @word;
IF (@overlap != '') THEN
SET @newword = CONCAT(',', @word);
END IF;
SET @overlap = CONCAT(@overlap, @newword);
END IF;
IF (@list IS NULL) THEN
LEAVE LOOPING;
END IF;
END LOOP LOOPING;
RETURN @overlap;
END$$
DELIMITER ;
DELIMITER
command is to change the statement delimiter from ";" to "$$" and back. You need to do this in order to define custom functions or procedures.)web
column, then it finds all other sites that share its keywords in the tags
column. Using this, if you search for "google.com", it will also return "bing.com", and "yahoo.com" because all three of those records have "search" and "searchengine" in tags
.