CODEWITHSUNDEEP
phpmysql
user2320500
user2320500

Reputation: 169

Checking for string match with a MySQL query

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!

Upvotes: 0

Views: 4515

Answers (7)

nnikolay
nnikolay

Reputation: 1751

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

Upvotes: 0

Hackerman
Hackerman

Reputation: 12305

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

Upvotes: 0

Justin
Justin

Reputation: 9724

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

OR

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

Upvotes: 1

m1kfb
m1kfb

Reputation: 121

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)."%'";

Upvotes: 0

LeonardChallis
LeonardChallis

Reputation: 7783

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.

Upvotes: 0

Nagasaki
Nagasaki

Reputation: 58

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

Upvotes: 3

Michal Borek
Michal Borek

Reputation: 4624

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.

Upvotes: 0

Related Questions