SQL users searching query

Question

I would like to create query to search given information in table users. Table consists: of id, username, firstname, lastname, phone and email.

Sample search text: mat h 50 @l d

My query:

SELECT * FROM  `users` WHERE (
(`firstname` LIKE  '%mat%') || (`firstname` LIKE  '%h%') ||
(`firstname` LIKE  '%50%') || (`firstname` LIKE  '%@l%') ||
(`firstname` LIKE  '%d%')
) && (
(`lastname` LIKE  '%mat%') || (`lastname` LIKE  '%h%') ||
(`lastname` LIKE  '%50%') || (`lastname` LIKE  '%@l%') ||
(`lastname` LIKE  '%d%')
) && (
(`phone` LIKE  '%mat%') || (`phone` LIKE  '%h%' ) ||
(`phone` LIKE  '%50%') || (`phone` LIKE  '%@l%') ||
(`phone` LIKE  '%d%')
) && (
(`email` LIKE  '%mat%') || (`email` LIKE  '%h%') ||
(`email` LIKE  '%50%' ) || (`email` LIKE  '%@l%') ||
(`email` LIKE  '%d%')
) && (
(`username` LIKE  '%mat%') || (`username` LIKE  '%h%') ||
(`username` LIKE  '%50%') || (`username` LIKE  '%@l%') ||
(`username` LIKE  '%d%')
)

But this query is returning people who have got username containing d and their phone number contains 50.

EDIT:
this query returns 3 rows:

1 | admin | mat | hladeo | 123450789 | admin@localhost
8 | dillese | Adriana | Zolch | 44450232 | [email protected]
12 | dcolhut | Denise | Colhut | 502222222 | [email protected]

and should return only first row (because matches all the requirements).

==========

And the main question - how to optimize this query? Is it possible to make it simplier?

Regards

You could use a regex instead of a LIKE pattern: fieldname RLIKE 'mat|h|50|@l|d' It will still be inefficient, since this regexp has to scan all records, it can't use an index. — Barmar
– Barmar, Commented Jul 16, 2014 at 16:09
Why shouldn't it return people who have username containing d and phone containing 50? What is the matching logic supposed to be? — Barmar
– Barmar, Commented Jul 16, 2014 at 16:12
I do not know how to specify it correctly in SQL, but "this query" should return only perfectly matched record, where all the information are found - not only some part of it like username containing d and phone containing 50 — Hladeo
– Hladeo, Commented Jul 16, 2014 at 16:24
So each part of the sample text has to match a different field, but they aren't specifying which part has to match which field, and you want all fields to be matched? — Barmar
– Barmar, Commented Jul 16, 2014 at 16:33
Could you give an example of a record that it returns that shouldn't? I'm still not sure I get it. — Barmar
– Barmar, Commented Jul 16, 2014 at 16:44

Barmar · Accepted Answer · 2014-07-16 17:17:06Z

1

I think this should do it:

SELECT * FROM  `users` WHERE (
       (firstname LIKE '%mat%' OR lastname LIKE '%mat%' OR
        phone LIKE '%mat%' OR email LIKE '%mat%' OR username LIKE '%mat%')
       AND
       (firstname LIKE '%h%' OR lastname LIKE '%h%' OR
        phone LIKE '%h%' OR email LIKE '%h%' OR username LIKE '%h%')
       AND
       (firstname LIKE '%50%' OR lastname LIKE '%50%' OR
        phone LIKE '%50%' OR email LIKE '%50%' OR username LIKE '%50%')
       AND
       (firstname LIKE '%@l%' OR lastname LIKE '%@l%' OR
        phone LIKE '%@l%' OR email LIKE '%@l%' OR username LIKE '%@l%')
       AND
       (firstname LIKE '%d%' OR lastname LIKE '%d%' OR
        phone LIKE '%d%' OR email LIKE '%d%' OR username LIKE '%d%')
      )

You need to test each criteria separately, not each field.

edited Jul 16, 2014 at 17:17

answered Jul 16, 2014 at 16:36

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

2 Comments

Hladeo Over a year ago

It looks that it works. But I will do some more tests. If they pass, I will accept your answer :)

Hladeo Over a year ago

It looks that everything is correct. Thank you very much for help :)

Collectives™ on Stack Overflow

SQL users searching query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related