select data where %column% like $variable-CodePudding

I have a table name deny_keywords in database with this structure:

id	word
1	thief
2	crook
3	killer

and I have an input that user can send a text and this text Placed in variable name $user_text.

Now I want to check if $user_text includes any of deny keywords or no.

Is there a way to do this just using SQL?

A way like this:

SELECT * FROM deny_keywords WHERE %word% like $user_text;

CodePudding user response：

You can create a fulltext index on the word column of the deny_keywords table to accomplish this.

To create the index you would run:

alter table deny_keywords add fulltext(word);

To use the index in the query you are trying to run:

select *
from   deny_keywords
where  match(word) against ('$user_text' in natural language mode);

A fiddle is here: http://sqlfiddle.com/#!9/443eb6/1/0

For security reasons, you should use mysqli or PDO, where you prepare the statement and bind the user_text variable as a parameter. That's a separate discussion.

CodePudding user response：

This is the solution I found and it works:

SELECT * FROM deny_keywords WHERE INSTR('{$user_text}' , `word`) > 0