MySQL LIKE operators & wildcards

Question

I've been reading tutorials and have learned nothing new. I have a table of customers. The customer's first and last names are stored in separate columns. I want to write a query that can search for customers by name, either first, last or BOTH.

Here's what I've got:

$queryyy = "
    SELECT *
    FROM `customers`
    WHERE
        `first_name1` LIKE '".mysql_real_escape_string($_GET['custname'])."%'
        OR `last_name1` LIKE '%".mysql_real_escape_string($_GET['custname'])."'
        AND `status` = 'active'
    LIMIT 6
";

If I want to find "Lindsay Thompson", I can query for "lindsay", or for "Thompson" and get the results I want, but if I query for "lindsay thompson" I get nothing.

I feel like I'm missing the point of the wildcards, or not using them properly. Can someone please explain this to me and correct my query..

Thanks

i don't necessarily need an exact match, the problem is i'm getting no results at all — I wrestled a bear once., Feb 23 '13 at 08:11
The way you have it setup, it won't work. You should explode the string on the spaces and individually match the first and last name with wildcards (ie `firstname LIKE 'lind%' or lastname LIKE 'thomp%'`) — Supericy, Feb 23 '13 at 08:12
thanks @Supericy i didn't think of that. the concat() answer below looks promising as well. — I wrestled a bear once., Feb 23 '13 at 08:14
@Supericy: so for the request "Linda Thompson" this will return all Lindas, regardless of her last name. — zerkms, Feb 23 '13 at 08:17
I would consider splitting your search query into its separate words. So 'Lindsay Thompson' would search for first names and/or last names like 'Lindsay' and/or like 'Thompson'. — Timo, Dec 11 '14 at 16:03

score 4 · Accepted Answer · answered Feb 23 '13 at 08:09

4

Wildcards are introduced to express "any number of any characters" (in case of %).

So

col LIKE '%foo'

will match for foo value and barfoo value.

What you want is actually the opposite - you need to concatenate two columns and check if it's equal to the request, like:

CONCAT(first_name, ' ', last_name) = 'foo bar'

answered Feb 23 '13 at 08:09

zerkms

249,484
69
436
539

Why exactly does he need to do that in a search query? And how will this help him to search customers by only first name or by only last name? – Igor Jerosimić Feb 23 '13 at 08:25
@Igor Jerosimić: "Why exactly does he need to do that in a search query?" --- why not? This question is about logic, not optimization. "And how will this help him to search customers by only first name or by only last name?" --- I have no idea, I just answered a particular question (see "If I want to find "Lindsay Thompson", I can query for "lindsay", or for "Thompson" and get the results I want, but if I query for "lindsay thompson" I get nothing.") – zerkms Feb 23 '13 at 08:27
His request is "I want to write a query that can search for customers by name, either first, last or BOTH.". – Igor Jerosimić Feb 23 '13 at 08:27
1

@Igor Jerosimić: yep. So? Now he knows about `CONCAT` and may implement exactly what he needs. My intention wasn't to do the other guy's job completely, but to give a hint about *possible* solutions. – zerkms Feb 23 '13 at 08:30

score 2 · Answer 2 · answered Feb 23 '13 at 08:13

a % wildcard will match with any number of characters. To use the example that is shown in the page http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html D%i% would match David.

The problem that you are having is that you are searching either Lindsay or Thompson for %Lindsay Thompson, i.e. search either name for any number of characters followed by the full name. Therefore this will never match.

One option is to run the query on a catenated string of the two names.

SELECT * from customers WHERE CONCAT(first_name1, ' ', last_name1) LIKE '%" .mysql_real_escape_string($_GET['custname']). "%' AND status = 'active' LIMIT 6";

score -1 · Answer 3 · edited Feb 23 '13 at 08:28

-1

Try this, hope it'll help you

 $queryyy = "SELECT * FROM `customers`
             WHERE (`first_name1` LIKE '".mysql_real_escape_string($_GET['custname'])."%' 
             OR `last_name1` LIKE '%".mysql_real_escape_string($_GET['custname'])."') 
             or concat(`first_name1`,' ',last_name1`) 
             LIKE'".mysql_real_escape_string($_GET['custname'])."%' 
             AND `status` = 'active' LIMIT 6";

edited Feb 23 '13 at 08:28

Mayukh Roy

1,815
3
19
31

answered Feb 23 '13 at 08:20

Tapas Pal

7,073
8
39
86

1

there are some typos, but this definitely helped me get perspective. thanks, – I wrestled a bear once. Feb 23 '13 at 09:58

MySQL LIKE operators & wildcards

3 Answers3