Search code examples
phpmysqldatabasewildcardsql-like

MySQL LIKE operators & wildcards

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

Solution

  • 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'