How to search on PHP with MySQL by full words

Question

I created the simple search page with the code:

$keywords = str_replace(' ','%',$_POST['keywords']);
$stmt = $pdo->query("SELECT `text` FROM `records` WHERE `tags` LIKE '%$keywords%'");

It works but if the DB column "tags" has for example the word "freelance" and I entered the word "free" I get result but I should not. It must search only by full words. I had tryed the full text search with MATCH(text) AGAINST but it doesn't suit me because of stopwords

EDIT: Sorry I didn't clarify: if the DB column has for example the words "freelance web development", I entered "freelance" or "web development" or "freelance development" [by using str_replace(' ','%')] I get in results what I need. But if I entered "free" (i.e. the word is no in the DB) I should get no results but I get success result again.

Besides I echo the results through foreach. If I changed my code with IN ($array) I get no result even if I entered "freelance", probably because of foreach

I'm coder of HTML/CSS only and PHP beginner (and maybe even less) so I will be thankful for any additional comments including from security-standpoint

NO NO NO! You invite people to use sql injection on your site! Read the documentation that comes with PDO on how to use prepared statements. Just using PDO as a driver is _not_ enough! — arkascha, Jan 17 '14 at 20:59
SQL injection risk ahead! Please read example 6 [here](http://www.php.net/pdo.prepared-statements). — Barranka, Jan 17 '14 at 21:00

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

I believe you need

WHERE `tags` IN $keywords

With $keywords as the implode(',', $keyArray) , I.e. of the format ('free', 'busy', 'other')

Do heed the warnings about SQL injection and see Select from mysql table WHERE field='$array'? for general method

edited May 23 '17 at 11:51

Community

1
1

answered Jan 17 '14 at 21:04

Floris

45,857
6
70
122

echo_Me · Accepted Answer · 2014-01-17T21:22:14.987

0

to find exact word you need to let a space like that

 $stmt = $pdo->query("SELECT `text` FROM `records` 
                      WHERE `tags` LIKE '% $keywords %' OR `tags` LIKE '$keywords %' OR `tags` LIKE '% $keywords'");
                                          ^---------^---spaces here

you will get the exact word FREE.

edited Jan 17 '14 at 21:22

answered Jan 17 '14 at 21:09

echo_Me

37,078
5
58
78

This won't return the word if it is at the beginning or end of the field, or if it is next to any sort of punctuation, or if the field is specifically the search term (without whitespace). – Radley Sustaire Jan 17 '14 at 21:11
@RadGH this edit should get them all – echo_Me Jan 17 '14 at 21:14
Still need one more :) `OR \`tags\` = '$keywords'` – Radley Sustaire Jan 17 '14 at 21:15
no this last one is wrong. you will get words attached to other words like him – echo_Me Jan 17 '14 at 21:16

How to search on PHP with MySQL by full words

2 Answers2