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

Question

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.

Strange, the query looks fine to me. Can you show how it is run in PHP? Are you 1000% sure that is the actual query being run? What happens if you run this statement in a database management tool like phpMyAdmin? — Pekka
– Pekka, Commented Jun 16, 2011 at 22:40

jeroen · Accepted Answer · 2011-06-16 22:41:49Z

2

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

answered Jun 16, 2011 at 22:41

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

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

Comments

Jacob Eggers · Accepted Answer · 2011-06-16 22:45:39Z

1

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

answered Jun 16, 2011 at 22:45

Jacob Eggers

9,3282 gold badges27 silver badges43 bronze badges

Comments

RRStoyanov · Accepted Answer · 2011-06-16 22:46:53Z

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

Curtis Patrick · Accepted Answer · 2011-06-16 22:46:16Z

0

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.

answered Jun 16, 2011 at 22:46

Curtis Patrick

978 bronze badges

2 Comments

RRStoyanov Over a year ago

How's that an answer to the question? You should add this as comment under the author's post.

Curtis Patrick Over a year ago

Perhaps a factor of my low reputation, I do not have a COMMENT option under the question.

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related