php/mysql search for multiple values

Question

I have a table homes with (country,city,area,published) fields. I have a search form where someone can enter country or city or area. I want to get all homes which are published = 1 and any of the search terms matches any of their fields.

This is what I have so far:

SELECT * FROM homes 
WHERE published = 1 
AND 
LOWER(country) LIKE '$search%' 
OR 
LOWER(city) LIKE '$search%'
OR
LOWER(area) LIKE '$search%'

The problem is that it return homes that have published = 0...

tamarintech · Accepted Answer · 2011-04-22 20:04:22Z

3

I would suggest parentheses.

SELECT * FROM homes 
WHERE published = 1 
AND
(
 LOWER(country) LIKE '$search%' 
 OR 
 LOWER(city) LIKE '$search%'
 OR
 LOWER(area) LIKE '$search%'
)

answered Apr 22, 2011 at 20:04

tamarintech

1,99013 silver badges18 bronze badges

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

Comments

Pentium10 · Accepted Answer · 2011-04-22 20:03:51Z

2

SELECT * 
FROM   homes 
WHERE  published = 1 
       AND ( country LIKE '$search%' 
              OR city LIKE '$search%' 
              OR area LIKE '$search%' )

Like already does the case-insensitive stuff so you don't need it.

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

answered Apr 22, 2011 at 20:03

Pentium10

209k124 gold badges435 silver badges513 bronze badges

1 Comment

chchrist Over a year ago

My default character set is UTF-8 and collation is utf_general_ci. I think I need the LOWER()

Collectives™ on Stack Overflow

php/mysql search for multiple values

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related