mysql - please advice with correct regexp syntax

Question

I am looking for correct query in mysql db. Let's consider I have tables:

users:

id | user_name | desc
--------------------------
1  | john      | tall
2  | john      | fat
3  | maria     | pretty

items:

id | item_name | color
--------------------------
1  | trousers  | red
2  | shoes     | blue
3  | shoes     | red

My search engine searches database to select result with query:

SELECT i.item_name, i.color, u.user_name, u.desc 
FROM users u, items i 
WHERE u.id = i.id 
   AND item_name REGEXP $keywords 
   AND user_name REGEXP $keywords

Variable $keywords is like this:

$keywords = explode(' ', $_POST['keywords']);
$keywords = implode('|', $keywords);

Now when $keywords = 'john trousers' it works all fine - i get user with id = 1. It's ok. But when I set $keywords = 'john' it returns empty string. I know even why - there is no 'john' in item_name so my AND condition returns false. The question is: What is correct regexp expression to return user = 1 with $keywords = "john trousers" and if $keyword = 'john' get two lines - with id = 1 and id = 2?

Thanks in advance.

Bohemian · Accepted Answer · 2012-05-02 00:10:18Z

1

You can reverse the comparison, using the column value as the search value (and use LIKE instead of REGEXP):

SELECT i.item_name, i.color, u.user_name, u.desc 
FROM users u, items i 
WHERE u.id = i.id 
AND $keywords like concat('%', item_name, '%')
AND $keywords like concat('%', user_name, '%')

Note that although this is a neat approach, it only works when keywords contains the whole value for the column. To make a more sophisticated comparison, you'd need to play with splitting up the values etc (you would have to do your own investigation)

answered May 2, 2012 at 0:10

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kalreg Over a year ago

Well it's surely interesting way to solve the problem. Unfortunatelly the condition that whole value of column must fit keywords messes up :( +1 for you my friend but still waiting for another, more appropriate solution:(

Collectives™ on Stack Overflow

mysql - please advice with correct regexp syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related