php - how do I find websites using tag search?

Question

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)

Answer 1

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.