CODEWITHSUNDEEP
phpmysqlsearch
Unexpected Pair of Colons
Unexpected Pair of Colons

Reputation: 769

How can I search for something specific with other variables using PHP/MySQL?

I'm creating a search for a website for an organization that works with abused and neglected animals, and everything is working fine until the search contains a specific value. For example, if someone types in 'braxton black' in the search field, it will search for those terms properly, and display results properly. However, if they use the advanced search, and they select 'Male' the results come back with male and female animals, like that part doesn't matter.

Here is an example SQL statement produced from searching 'braxton black' and selecting 'Female' as the animals sex:

SELECT * FROM ans WHERE (ans_name LIKE '%braxton%' OR ans_name LIKE '%black%') OR (ans_color LIKE '%braxton%' OR ans_color LIKE '%black%') OR (ans_age LIKE '%braxton%' OR ans_age LIKE '%black%') OR (ans_desc LIKE '%braxton%' OR ans_desc LIKE '%black%') AND ans_sex = 'Female' GROUP BY ans_id

All fields are correct, and the term within the 'ans_sex' field is either Male or Female, so it searches the exact term. Can anyone see any issue with this SQL statement that is causing a problem? Any help is greatly appreciated.

Upvotes: 0

Views: 144

Answers (4)

RRStoyanov
RRStoyanov

Reputation: 1172

Right now, your search is executed like this (I will split it to small chunks for better overview).

SELECT * FROM ans WHERE (ans_name LIKE '%braxton%' OR ans_name LIKE '%black%')

SELECT * FROM ans WHERE (ans_color LIKE '%braxton%' OR ans_color LIKE '%black%')

SELECT * FROM ans WHERE (ans_age LIKE '%braxton%' OR ans_age LIKE '%black%')

SELECT * FROM ans WHERE ans_age LIKE '%black%'

SELECT * FROM ans WHERE (ans_desc LIKE '%braxton%' OR ans_desc LIKE '%black%') AND ans_sex = 'Female'

As you may see, your AND is taken into account only with last OR

So... make it like this:

`SELECT * FROM ans WHERE ((ans_name LIKE '%braxton%' OR ans_name LIKE '%black%') OR (ans_color LIKE '%braxton%' OR ans_color LIKE '%black%') OR (ans_age LIKE '%braxton%' OR ans_age LIKE '%black%') OR (ans_desc LIKE '%braxton%' OR ans_desc LIKE '%black%')) AND ans_sex = 'Female' GROUP BY ans_id

Just wrap in braces everything before your AND

Upvotes: 0

Curtis Patrick
Curtis Patrick

Reputation: 97

Does the database you use support some sort of Full Text engine? Performance will be better than the tablescans the LIKE operators will require.

Depending on your DB, the changes to your SQL may be minimal. Unfortunately, there may also be additional cost.

Upvotes: 0

Jacob Eggers
Jacob Eggers

Reputation: 9322

You'll need to understand operator precedence AND vs OR.

 a or b and c == a or (b and c)

Also, likes are expensive so you'll want to check the sex first.

SELECT * FROM ans
WHERE ans_sex = 'Female' AND (
        ans_name LIKE '%braxton%' OR ans_name LIKE '%black%'
        OR ans_color LIKE '%braxton%' OR ans_color LIKE '%black%'
        OR ans_age LIKE '%braxton%' OR ans_age LIKE '%black%'
        OR ans_desc LIKE '%braxton%' OR ans_desc LIKE '%black%'
      )
GROUP BY ans_id

Upvotes: 1

jeroen
jeroen

Reputation: 91734

I´m not sure of the order in which mysql processes the conditions, but I would try wrapping all OR statements in parenthesis:

SELECT * FROM ans WHERE ( (ans_name LIKE '%braxton%' OR ans_name LIKE '%black%') OR (ans_color LIKE '%braxton%' OR ans_color LIKE '%black%') OR (ans_age LIKE '%braxton%' OR ans_age LIKE '%black%') OR (ans_desc LIKE '%braxton%' OR ans_desc LIKE '%black%') ) AND ans_sex = 'Female' GROUP BY ans_id

Upvotes: 2

Related Questions