CODEWITHSUNDEEP
phpmysqlregexsearch
Azam
Azam

Reputation: 3

MySql REGEXP multiple words search by including specific words

I have a databse with keywords coloumn

Need to search the database on the basis of query done by user.

Every keyword has word "outlet" at the end but user will only search "gul ahmad" not "gul ahmad outlet". For this i used following query and things worked fine to get results and found complete result "Gul Ahmad Outlet"

$sql = "SELECT keywords FROM table WHERE keywords REGEXP '([[:blank:][:punct:]]|^)$keyword([[:blank:][:punct:]]|$)'";

Now i have 2 issues 1. If the word "outlet is in between the query words then it does not find the word. e.g if user search "kohistan lahore", database has an outlet named "kohistan outlet lahore" but it does not find the keyword in database and returns empty. How to tell database to include "outlet" in between, at the start or athe end to find and match the result.

  1. if some user search "nabeel's outlet" database has it but due to " ' " this query returns empty without any result.

Upvotes: 0

Views: 605

Answers (2)

Rick James
Rick James

Reputation: 142258

Much simpler: [[:<:]]$keyword[[:>:]] -- This checks for "word boundary" instead of space or punctuation or start/end of string. And $keyword = "nabeel's" should not be a problem.

Don't you want to always tack on "outlet"?

REGEXP "[[:<:]]$keyword[[:>:]] outlet"

And, yes, you must escape certain things, such as the quotes that will be used to quote the regex string. PHP's addslashes() is one way.

Upvotes: 0

Udit Solanki
Udit Solanki

Reputation: 531

What you can do is that you can match your column values with just the first word of your search expression(i.e nabeel's outlet). I believe this way you will be able to cover all your scenarios.

select 
      * 
from `outlets` 
    where REPLACE(`name`,'\'','') regexp SUBSTRING_INDEX('nabeels outlet', ' ', 1)

Look at this fiddle and test yourself : http://sqlfiddle.com/#!9/b3000/21

Hope it helps.

Upvotes: 1

Related Questions