SQL condition: (A=B AND C LIKE %D%) OR (A LIKE %B% AND C=D)

I've got a table of first names and last names.

I'm trying to make a jQuery instant search on an input, in order to find very quickly and precisely a person in a huge list of people.

When the first word is entered in the input, it might be the first name or the last name.

I do this : SELECT * FROM myTable WHERE firstName LIKE %content% OR lastName LIKE %content%

When two words are entered, it might be: * the full firstname and a bit of the lastname * the full lastname and a bit o开发者_如何学Pythonf the firstame

So I tried this query : SELECT * FROM myTable WHERE (firstName = content1 AND lastName LIKE %content2%) OR ( lastName = content1 AND firstName LIKE %content2%)

Unfortunately parenthesis seems to do nothing, and the query is not interpreted this way, I've got a lot of results, basically produced by the two LIKE %% condition

Anyone had deal with this before and could give me a hand?

Thanx

If one of the words is going to be the full first name of the full last name, while the other word is a partial of the other, why don't you split up the words first? Then you'd pass in two paramaters and have:

SELECT
    *
FROM
    myTable
WHERE
    (
        firstName = %content1%
        AND lastName LIKE %content2%
    ) OR (
        lastName = %content1%
        AND firstName LIKE %content2%
    )

I believe you are going to need to split the two words entered and use them indepenently in your query.

It would probably work this way (assuming that the two words are in the content variable):

SELECT ... WHERE
   CONCAT(firstName, " ", lastName) LIKE content%
   OR CONCAT(lastName, " ", firstName) LIKE content%

however this approach would not be very efficient (no index usage). I would split the two words into two variables (word1, word2) and make it something like:

SELECT ... WHERE
    (firstName = word1 AND lastName LIKE word2%)
    OR (lastName = word1 AND firstName LIKE word2%)

SELECT * FROM myTable WHERE 
    (firstName = 'content1' AND lastName LIKE content2%) 
OR 
    (lastName = 'content1' AND firstName LIKE content2%)

When two words are entered, it might be: * the full firstname and a bit of the lastname * the full lastname and a bit of the firstame

If it is ALL of (exact match) the first or last name, then the = test seems correct. Just in case it is an error in the PHP part, this is how it should look like.

$qry = '
SELECT * FROM myTable
WHERE (firstName = content1 AND lastName LIKE '%".mysql_real_escape_string(content2)."%')
   OR ( lastName = content1 AND firstName LIKE '%".mysql_real_escape_string(content2)."%')';

If it is a bit of both, then you need the wildcard twice

$qry = '
SELECT * FROM myTable
WHERE (firstName LIKE '%".mysql_real_escape_string(content1)."%' AND lastName LIKE '%".mysql_real_escape_string(content2)."%')
   OR ( lastName LIKE '%".mysql_real_escape_string(content1)."%' AND firstName LIKE '%".mysql_real_escape_string(content2)."%')';