Php search query exact word match with CONCAT

Question

I am trying to build a search query where suppose if we type a word stem, it should show words starting with stem and whatever follows after that. Right now, my code looks like this:

$sql = "SELECT * FROM `faculty`
    WHERE (CONCAT(`firstName`, ' ', `lastName`,`expertise`,`affiliation`)
    LIKE '%$textSearch%') ORDER BY lastname asc";

If I search stem in the search bar, it will show unneccessary words like 'System'.

Maybe do it with a regex and use word boundaries, you'll have to look up the posix version, it is not `\b`. — user3783243, Oct 05 '18 at 23:53

Luke Corbett · Accepted Answer · 2018-10-06T05:12:14.127

0

If you’re looking for a search across multiple columns, but each word must begin with the search term you may have to make it a multiple LIKE statement?

$sql = "SELECT * FROM `faculty` WHERE 
`firstName` LIKE ‘$textSearch%’ OR `lastName` LIKE ‘$textSearch%’ OR `expertise` LIKE ‘$textSearch%’ OR `affiliation` LIKE ‘$textSearch%’ 
 ORDER BY lastname asc";

edited Oct 06 '18 at 05:12

answered Oct 05 '18 at 23:44

Luke Corbett

16
4

Hi Luke, Thank you for your response. This is working to some extent. However, If I have a expertise column looks like "Hey, I am engineer". It will only work if I type "Hey". If I type something that is in the middle like engineer, it won't work. – Amun Kharel Oct 06 '18 at 00:22
Hi Amun, I've found another link on SO which shows a similar issue to what you are having. [https://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql] – Luke Corbett Oct 06 '18 at 05:13

Php search query exact word match with CONCAT

1 Answers1