[SOLVED] 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!

[Wildbug]

You could compromise.  Only use a LIKE 'cityname%'.  Create an index on that column.  IIRC, indicies will only work on the beginning of the string, that is, "Atlan%" will use the index, but "%tlanta" won't.  If your site is busy/big and this search will affect performance, benchmark the options to see which will work best.  I think LIKE 'cityname%' with an index is the best mix of usability and performance.

[hadoob024]

That is true.  I think I might be able to incorporate both of them.  First do a search on the exact term entered.  If that doesn't pull any results, try again using the wildcarding that you suggested.  That seems like it would be even better, right?

[Wildbug]

Yep.  Actually, I think I did that on one of my first search functions.  If the "exact" search failed, an "approximate" search was executed.  I used a "sounds like" function, too.  MySQL has a SOUNDEX function.  PHP has a levenshtein metric ("distance" between two words).  Either can be used to help approximate searches.

[hadoob024]

Really?  I had never heard of either or those functions.  Do they come with the core PHP library, or do I need to have additional libraries installed?

[Wildbug]

levenshtein() is a string function -- standard.  SOUNDEX() is a standard MySQL function.  See also SOUNDS LIKE (for MySQL).

[hadoob024]

Cool!  Thanks!  I'll definitely try those out.  Also, I'm trying to figure out how annoying this is going to be because I actually have 3 text fields that I need to do searches on simultaneously (i.e., city, county, zip code).

[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.

[gtk]

I use probably the Coolest way to search from DB

<?php
$conn = mysql_connect("localhost", "root", "");
$test = new browse("php", "Profiles", $conn);//db_name, table_name, $conn
$test->set("usr", "neel");//Set The GIVEN Fields, and its Value
//Set The Fields You have To Find
$test->find("id");
$result = $test->output();
print_r($result);
?>

$test->adv_output();//Advanced Output is also provided

And you can also use $test->set("usr", "!=", "neel"); or $test->set("usr", "LIKE", "neel");//Operator

More Information is provided on http://zigmoyd.sourceforge.net/man/db.php#browseThis code is copy pasted from that site

I use the adv_output() as its easier than output()

But you need to install it correctly first.

See http://zigmoyd.sourceforge.net/man/index.php for Installation

its too easy ans too cool.

I dont use while loop anymore.

[hadoob024]

Does your method look for exact matches though?  I'm trying to dumb my stuff down for the average user so they don't have to learn how to spell correctly.  In my testing, I'm finding that SOUNDS LIKE works great!

[Wildbug]

Seems pretty quick, but twice as slow as text match:

 

mysql> SELECT BENCHMARK(1000000,"bargle" SOUNDS LIKE "argle");
+-------------------------------------------------+
| BENCHMARK(1000000,"bargle" SOUNDS LIKE "argle") |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
1 row in set (0.22 sec)

mysql> SELECT BENCHMARK(1000000,"bargle" LIKE "bar%");
+-----------------------------------------+
| BENCHMARK(1000000,"bargle" LIKE "bar%") |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.11 sec)

 

I had to do it a million times to notice a measurable time, though.

[hadoob024]

Cool.  Thanks!  Yeah, I just ran it too, but couldn't notice any discernable difference in time.  Good enough for me!  Thanks for the tip!