CODEWITHSUNDEEP
phpmysqlsearch
Bala
Bala

Reputation: 3638

How to get the same search results regardless white space in the search term?

I am trying to make a search box for an ecommerce website.

The search works as follows

When a user searches for a product, the search value is being sent to a file called searchResults.php using post method via ajax

$searchVal=$_POST['searchVal'];

And then its being searched in the database from a table named product by the following query

$searchResult = mysql_query("SELECT * FROM products WHERE name LIKE '$searchVal'")

and the results are sent back as ajax response by the following if condition

if($searchResult){
 echo "result";

}
else{

 echo "No products found";     
  }

Above all everything works fine as expected.

lets assume an user is searching for cellphones and he/she types cell phone . But we have products only for category cellphones and not for cell phone. So it results No products found even though the records for cellphones are present.

I want to make it search regardless the white space, singular or plural . How can i do that ?

Upvotes: 0

Views: 2120

Answers (4)

symcbean
symcbean

Reputation: 48357

The right way to implement a search engine is to maintain a separate table of words and links to the record they appear in. Then....

 $qry="SELECT p.*, COUNT(*) 
    FROM products p
    INNER JOIN srchwords s
    ON p.id=s.product_id ";
 $searchVals=explode(' ',$_POST['searchVal']);
 foreach ($searchvals as $k=>$s) {
    $searchvals[$k]="'" . mysql_real_escape_string(trim($s)) . "'";
 }
 $qry.="WHERE s.word IN (" . implode(",",$searchvals) . ") ORDER BY COUNT(*) DESC";

An ugly and innefficient hack would be:

 $qry="SELECT p.*
   FROM products p";
 $join=" WHERE "

 $searchVals=explode(' ',$_POST['searchVal']);
 foreach ($searchvals as $k=>$s) {
     $qry.=$join . " p.desc LIKE '%" . mysql_real_escape_string(trim($s)) . "%'
     $join=' OR ';
 }

Both methods still don't not cater for plurals (just add an additional comparison for words ending in S, removing the S). You should also clean up the string to remove multiple spaces and punctuation (/^[a-z0-9 ]/i).

Or just use one of the many, well written off-the-shelf search engine solutions (e.g. the mnogo engine or Google's site search service).

Upvotes: 2

Rudolph Gottesheim
Rudolph Gottesheim

Reputation: 1701

A first step would be to explode() the search term on spaces: $terms = explode(' ', $query) and then do a 'SELECT * FROM products WHERE name LIKE "%'.$terms[0].'%" AND name LIKE "%'.$terms[1].'%" ...'.

Of course, this doesn't really solve your plurals issue.. Also, it can be very, very slow because MySQL can't use indexes on LIKE queries starting with a wildcard.

Another course of action could be to just have an "aliases" table that would look something like this:

cellphone | cell phone
cellphone | cell phones
cellphone | cellphones
...

Then you would replace the all occurances in a search query with the one on the left before querying the database for it.

The third and best and most complicated way is to use an index table. You wouldn't want to write that yourself, but I'd bet there are some great solutions out there. Personally, I'm using Doctrine, which has this feature built in.

Upvotes: 1

aF.
aF.

Reputation: 66697

Step 1: remove leading and trailling spaces:

$searchResult = mysql_query("SELECT * FROM products WHERE name LIKE trim('$searchVal')")

Step 2: replace existent spaces by '%' (it's wildcard in LIKE syntax):

$searchResult = mysql_query("SELECT * FROM products WHERE name LIKE str_replace(trim('$searchVal'), ' ', '%'")

Upvotes: 1

horsley
horsley

Reputation: 480

You can use trim() in php to strip whitespace (or other characters) from the beginning and end of a string

Upvotes: 0

Related Questions