使用Mysql进行文本搜索

Question

I am trying to create a search blog system where blogs are displayed based on user suggestions. Suggestions are basic hashtags which is stored in user_information table. While blogs will also have some hashtags input by blogger which will be stored in blog table.

Now as I have to match hashtags from user_information table to blog table, I am not finding a way to do this.

I have tried mysql LIKE CLAUSE but I am not able to get even single result.

My Query was [just representation]

SELECT * FROM blog WHERE hashtags LIKE $hashtags_from_user

Answer 1

You are not correctly escaping the value, you require the hash tags to be enclosed in quotations

"LIKE '". $hashtags_from_user ."'";

Additionally you will pay the price in the SELECT statement when you have denormalized database (hash-tags should have their own row in a hash_tags table and the blog would references these via a many-to-many or many-to-one association)

Currently the output of the query is:

SELECT * FROM blog WHERE hashtags LIKE '#hashtag1,#hashtag2,#hashtag'

This isn't going to give you the results you want if you need to match on the individual tags.

The only soultion would be to read the users has tag string, break it up into each tag (using $tags = explode(',', $hash_tags_from_user); and then build up a query to include all of them

$where  = array();
$select = 'SELECT * FROM foo';
foreach($tags as $tag) {
  $where[] = sprintf('tag LIKE \'%s\'', $tag);
}
if (! empty($where)) $select .= 'WHERE ' . implode(' OR ', $where);

Please don't use the above as it's just and example (and open to simple SQL injection) consider using prepared statements, with PDO/MySQLi.