Search query help

My search functionality is not great on my site and I could do with some advice on improving this.

Essentially my database holds the following information about each accommodation business.

address2 (optional)

Btw this is a UK based site.

So lets say we have the address

Crown Hill (address1)
West Buckland (address2)
Wellington (townCity)
Somerset (county)
TA21 9SZ (postcode)

My site has a search form allowing a user to type in a search location.

So for example, someone may type in “West Buckland, Somerset”

Currently in my code, I explode the location that the user types in, into an array then use code like the following which kind of works if their search location is only one element e.g “West Buckland”

I am not currently checking against address1 as have assumed this is just a street name - which I know will not always be the case:(

$loc_array = explode(",", urldecode($this->loc));

$sql = "SELECT ? FROM accommodation WHERE  (accommodation.address2 LIKE '%".$db->prepare($loc_array[0])."%' OR accommodation.townCity LIKE '%".$db->prepare 
($loc_array[0])."%' OR accommodation.county LIKE '%".$db->prepare($loc_array[0])."%' OR accommodation.postcode LIKE '%".$db->prepare($loc_array[0])."%')";

My code doesn’t work if someone enters something like
“West Buckland, Somerset”

Any advice how to put this query together much appreciated. I cannot think of any way of doing it efficiently - my only thoughts are using a very long set of “AND” and “OR” conditionals which I am sure would be really inefficient.

Thanks in advance


I did something like this recently. I replaced commas, spaces, quotes, etc with wildcards% and did a concatenation of all fields to test against.

select address1+address2+townCity+county+postcode as longaddress
from table
where address1+address2+townCity+county+postcode like ‘%West%Buckland%%Somerset%’

matches Crown HillWest BucklandWellingtonSomersetTA21 9SZ

Thanks esearing - I will give that a go.