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);

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.

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)

I am using MySQL version 5.0.92 I believe.

Hi there. I am trying to develop an all purpose MySQL default query for my web store. I want results to come from the product name, product description, and product merchant (its a mall-type store). The product_name and product_description are part of a FULLTEXT index, whereas the merchant name is not. The "?" in the sql statement below are placeholders for prepared input (i.e. for mysqli prepared statements). I'd like the results to be sorted by relevance, where products with a matching name or matching merchant are most relevant and products with matching descriptions are the second most relevant. This is what I have now: SELECT * FROM products p LEFT JOIN merchants a ON p.product_merchant_id = a.merchant_id WHERE (MATCH(product_name,product_description) AGAINST (?) OR merchant_name LIKE ?) AND p.active = 1 It works fair, but perhaps someone with more experience can give additional advice for how this should be done. Anna