MySql searching on search terms with small words.

[fpearl]

I have an interesting search problem. I have an online marketplace with 9500 products. I have a fulltext index on the (product_name,product_description) fields. Unfortunately, the fulltext does not index words with only 3 characters. Because I am on a Godaddy shared server, I can not change the mysqld to allow this. I have a product called "Bar Kit" that can't be found in search because of this limitation.

 

I'd like to develop a non-fulltext search solution that can be implemented when the search terms include words with less than 3 characters. I figure I will prepare each search string by removing special characters etc., and also removing common 3-letter words. Then, if any 3 letter words are left I'll use the special search query for queries with short words, otherwise I'll use the normal query.

 

I wonder if you have any tips on what the sql statement should be for the new search?

 

Thanks

 

PHP ver 5.2.17

MySql ver 5.077

Godaddy Hosting (premium business, dedicated IP)

[xyph]

Without going to a dedicated search server like Sphinx, the fulltext search is probably the least resource intensive way to do this. You should talk to GoDaddy about a solution to this, as any other solution you decide to implement will probably impose a bigger load on their servers.

[fpearl]

I found an sql statement that works well for finding small whole words...

 

$sql = "SELECT p.product_id, p.product_name, p.product_description, p.product_price, p.product_category, p.product_artist_id, p.product_image_small, p.perms, CONCAT(a.first_name,\" \",a.last_name) fullname FROM iscript_products p LEFT JOIN iscript_artists a ON p.product_artist_id = a.artist_id WHERE product_name REGEXP '[[:<:]]$searchterm[[:>:]]'";

 

I can then UNION a few of these together. The above string throws an error in php:

 

Parse error: syntax error, unexpected '[', expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/content/14/7857114/html/mall/search.php on line 84

 

Do I need to escape the brackets? If so how?

 

Thanks.

[fpearl]

Here's what I've ended up with that seems to work:

$arr = explode(" ", $searchStr,3);//limits the search to three terms
$termArray[] = $mysqli->real_escape_string($searchStr);//searches for an exact match first
foreach($arr as $element) {
$termArray[] = $mysqli->real_escape_string($element);
}
foreach($termArray as $element){
$sqlArray[] = '(SELECT p.product_id, p.product_name, p.product_description, p.product_price, p.product_category, p.product_artist_id, p.product_image_small, p.perms, CONCAT(a.first_name," ",a.last_name) fullname FROM iscript_products p LEFT JOIN iscript_artists a ON p.product_artist_id = a.artist_id WHERE product_name REGEXP "[[:<:]]'.$element.'[[:>:]]" ORDER BY '.$ordertxt.')';
}
$sql = implode("UNION", $sqlArray);
$stmt = $mysqli->prepare($sql);

[fenway]

Don't try and roll-your-own F.T. index -- that REGEXP won't work at the end of the column.