RSS

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.

Advertisements
 
1 Comment

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

 

Tags: , , , , , , , , ,

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

  1. blog.svnlabs.com

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: