[RESOLVED] Best way to perfom searches on db

hadoob024

I have a db populated with real estate listings. On our search page, the user can enter in a City name in a text field that I then want to use as a search term on the db. My 2 thoughts on how to do this are the following:

$where[] = "ofrelistings.city = '".$city."'";

-or-

$where[] = "ofrelistings.city like '%".$city."%'";

I don't want to bog down my db by using "like", but if someone types in "Atlant" instead of "Atlanta", I would want the search to go through. Any thoughts, improvements, suggestions? Thanks!

scrupul0us

well my school of thought is this

#1 take their input and do a equal match
#2 if there is results, return them
#3 if theres no results, perform a like match and give them a list of options (did you mean "this")
#4 if theres still no results chances are they are typing jibberish or have no idea whats going on 😉

hadoob024

Hmmmm.... OK. I see what you're saying. That makes sense actually. Just incorporate both of them. By the way, would I have to create an index on this column in order to do this?

Additionally. I'm trying to think how annoying this is going to be because I have 3 text fields that I need to possibly do searches on (i.e., city, county, zip code). I'm trying to think of how to do all of this with 3 search fields.

FusionComputers

No, I don't believe there is any way for SQL to search through partial data in a field without the LIKE clause. At any rate, it would be easier on your database to use the LIKE clause. To do scrupul0us's suggestions, you'd have to hit your database at least two to four times. The LIKE clause would work much better.

There is a way to asynchronously collect data from a database. If you have a list of major searches in an area, you can use AJAX to autocomplete a search for a user. Check out Google Suggest for what I mean. AJAX is a blending of JavaScript and PHP, or ASP if you like, to retrieve data on the fly from a database without having to completely reload a page. Check out W3Schools.com for a beginner's tutorial on AJAX. It's a pretty interesting concept. I'm wanting to integrate it into my Web site, mainly for loading content, so the layout doesn't have to refresh every time someone clicks a link. The sky is the limit with AJAX, basically.

hadoob024

Cool. Thanks. Sorry you had to type that all out 🙂

Anyway, yeah, still debating whether I even want to go the LIKE route, or just make it so that people have to use a little common sense (and a dictionary) to perform a proper search.

It's also been suggested to me to use MySQL's SOUNDEX function or PHP's levenshtein function. Any thoughts on that?

hadoob024

Does anyone know how much overhead the SOUNDS LIKE function has?

Instead of doing both searches (first exact then LIKE), I did the following:

$where[] = "ofrelistings.city SOUNDS LIKE '".$city."'";

This seems to work like a charm. Just hope it's not straining the db.

FusionComputers

Alternatively, you could do SELECT * FROM table, then load the values into an array. If this is MySQL, you're set with mysql_fetch_array. Then search the keyed location value in the array. No overhead of functions, but you do load and work with unnecessary data, when it's easier to let the database sort out what you're looking for.

hadoob024

Cool. I tried it several ways and tried benchmarking them all. Couldn't really find any discernable difference in times. I think I'm more than satisfied with the SOUNDS LIKE query. Thanks for the help!