Checking for string match with a MySQL query

Question

I am trying to all grab rows of data from a data table, where one word matches a word within the text in my column 'story'.

Table Structure:

post_id | user_id | story
----------------------------------
1       | 1       | Hello World What's up?
2       | 4       | Hello guys!
3       | 7       | Working on shareit.me! 

For Example:

I want to grab all of the posts containing the word hello (I am looking for case-insensitive).

How can I do this?

Here is what I have done so far:

// this will be the keyword! so use the variable $filter_tag in the query!
$filter_tag= $_GET['tag'];

//query for getting the users' posts containing the select tag 
$query_posts= "SELECT * FROM posts WHERE user_id= '$user_id' ORDER BY post_id 
DESC";

$result_posts= mysqli_query($connect, $query_posts); 

Thanks!

Answer 1

 $query_posts= "SELECT * FROM posts WHERE user_id= '$user_id' AND story LIKE '%$filter_tag%' ORDER BY post_id 
    DESC";

Answer 2

SELECT * FROM posts WHERE ... AND LOWER(story) LIKE '%hello%'

OR

SELECT * FROM posts WHERE ... 
AND story COLLATE latin1_general_ci_ai LIKE '%hello%'

Answer 3

It would be:

SELECT * FROM posts WHERE ... AND story LIKE '%hello%'.

Generally the "%" is a wildcard (like '*'). So you can use 'hello%' '%hello%' and so on.

Answer 4

You can use the LIKE operator:

$query_posts = "SELECT * FROM posts WHERE user_id = $user_id AND story LIKE '%yourword%' ORDER BY post_id";

The % characters are like wildcards. % means match any number of characters, where _ (underscore) matches just one.

Note: I've removed the single quotes from your user_id column check too - this, being an integer, doesn't want to be in quotes - they are for strings.

Answer 5

Goes without say don't forget to escape the input.

$query_posts= "SELECT * FROM posts WHERE user_id = '$user_id' AND story LIKE '%".mysql_real_escape_string($filter_tag)."%'";

Answer 6

My answer is like the same, i made a sqlfiddle:

#supose the tag is Hello

SELECT * FROM posts
where story like "%Hello%";

PS: http://sqlfiddle.com/#!2/6bfcc1/5

Answer 7

The best solution will be to use MATCH() AGAINST() with an FULLTEXT index.