ECommerce item search
// relevance score, customized to weight the individal
// cells searched. We place a lot of weight on an exact
// name match. and we do it once for the singular form
// and once for the plural form and once for the regexp.
// $order we set previously so the search can be ordered
// by price, item name or relevance
$queryText = sprintf("
SELECT `productid`,
IF(`productname` LIKE '%%%s%%',20,0)
+IF(`description` LIKE '%%%s%%',4,0)
+IF(`manufacturer` LIKE '%%%s%%',1,0)
+IF(`productcode` LIKE '%%%s%%',1,0)
+IF(`productname` LIKE '%%%s%%',20,0)
+IF(`description` LIKE '%%%s%%',4,0)
+IF(`manufacturer` LIKE '%%%s%%',1,0)
+IF(`productcode` LIKE '%%%s%%',1,0)
+IF(`productname` REGEXP '[[:<:]]%s[[:>:]]' ,2,0)
+IF(`description` REGEXP '[[:<:]]%s[[:>:]]' ,2,0)
+IF(`manufacturer` REGEXP '[[:<:]]%s[[:>:]]' ,1,0)
+IF(`productcode` REGEXP '[[:<:]]%s[[:>:]]' ,1,0) AS `rel`
FROM `ecommerce_products`
WHERE (`productname` LIKE '%%%s%%'
OR `description` LIKE '%%%s%%'
OR `manufacturer` LIKE '%%%s%%'
OR `productcode` LIKE '%%%s%%'
OR `productname` LIKE '%%%s%%'
OR `description` LIKE '%%%s%%'
OR `manufacturer` LIKE '%%%s%%'
OR `productcode` LIKE '%%%s%%'
OR `productname` REGEXP '[[:<:]]%s[[:>:]]'
OR `description` REGEXP '[[:<:]]%s[[:>:]]'
OR `manufacturer` REGEXP '[[:<:]]%s[[:>:]]'
OR `productcode` REGEXP '[[:<:]]%s[[:>:]]') $order;
",
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp),
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($plural),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($singular),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp),
mysql_real_escape_string($regexp));
// I use a db link variable to allow error messages
$query=mysql_query($queryText, $link);
if(mysql_errno($link)){echo ": " . mysql_error($link) . "\n<hr>";}
if(mysql_num_rows($query))
{
while ($dbRow = mysql_fetch_array($query))
{
$id=$dbRow[0];
// this is to weed out duplicates
$return[$id]++;
}
// this creates a nice array of product ids for us to work with.
// using only the product ids we can begin with the entire list,
// without over burdening the server
// from here we simply grab the ones we need (based on the
// start # and pagelength #)
// using the product id, I use a function that then grabs
// all the pertinent data about that item.
foreach($return as $key=>$value)
{
$prodList[]=$key;
}
Page 2 of 2 | Previous page