MySQL Related Search (title+tags+description)

06 Apr

What you think? What will be the best option to search related videos in mysql videos table with matching title+tags+description?

Very easy Idea!

# SELECT * FROM `videos` WHERE title LIKE ‘%$title%’ OR tags LIKE ‘%$tags%’ OR description LIKE ‘%$description’;

Full-text Search is a feature introduced to MySQL in version 3.23.23.

You have to add key in videos table for title+tags+description….

# ALTER TABLE `videos` ADD FULLTEXT(`title`,`tags`,`description`);

# SELECT *, MATCH(`title`,`tags`,`description`) AGAINST (“‘.$search_string.'”) as Relevance FROM `videos` WHERE MATCH (`title`,`tags`,`description`) AGAINST(“‘.$search_string.'” IN BOOLEAN MODE) ORDER BY `Relevance` DESC;

# SELECT * FROM `videos` WHERE MATCH (`title`,`tags`,`description`)
AGAINST (‘+myspace -youtube’ IN BOOLEAN MODE);

These SQL will return result automatically sorted by relevancy.

1 Comment

Posted by on April 6, 2010 in Open Source, php, Tips, Tricks


Tags: , , , , , , , , ,

One response to “MySQL Related Search (title+tags+description)


    October 12, 2010 at 5:29 am

    Stumbled into this site by chance but I’m sure glad I clicked on that link. You definitely answered all the questions. Greetings from the Speedy DNS


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: