我如何使用标签搜索来查找网站? [英] how do I find websites using tag search?

查看:727
本文介绍了我如何使用标签搜索来查找网站?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下标记表:

  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;

DELIMITER 命令是更改语句分隔符从;改为$$并返回。您需要执行此操作以定义

本质上,这段代码在 web 列,那么它会在标签列中找到所有其他共享关键字的网站。使用此功能,如果您搜索google.com,它也会返回bing.com和yahoo.com,因为这三条记录都在中包含搜索和searchengine标签


I have the following 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

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 google.com then I want to list yahoo and bing from above sample table.

How can I achieve this with PHP and MySQL? (I have enabled FULL TEXT SEARCH for this table)

解决方案

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:

SELECT t1.* FROM tags AS t1 JOIN tags AS t2
  ON LIST_INTERSECT(t1.tags, t2.tags) != ''
  WHERE t1.web='google.com'

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):

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 ;

(The 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.)

Essentially this code looks for a site in the 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.

这篇关于我如何使用标签搜索来查找网站?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆