Page 1 of 1

Searching mySQL databse with PHP

Posted: Tue Jul 14, 2009 2:09 pm
by kieransymes
Hi there

I'm currently setting up a shopping website where I am using a PHP/mySQL script called affilistore (http://www.affilistore.com/)

The website will search baby products from a few different merchants and present them for the user to look at.

The script, out of the box, works quite well, however, the creators have made it so that it searches the items description. This is causing a few problems. If somebody searches for something like "baby wipes" it will show some baby wipes and also things like changing bags (as it states in the description of change bags "great for storing wipes"). This become even more of a problem when searching for "prams" as it will output raincovers, accessories for prams, anything with "pram" in the description.

The best way I can think of getting around this would be to make the script only search in the product name. However, I have no idea where to start. I was looking for some pointers, any help would be appreciated :)

I have uploaded the PHP include that, as far as I can tell actually searches and outputs the products. It can be found here in txt format:
http://tooseypegs.co.uk/public/products.inc.txt

I believe it's these lines that need to be changed. I have tried but then get scripting errors. (lines 43-51 of products.inc.php)

Code: Select all

// output search results
$dbcondition = '';
if ($indcount == 0) {
$sql = "SELECT *, ( (1.3 * (MATCH(prodName) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) + (0.6 * (MATCH(prodDescription,merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) ) AS relevance FROM affiliSt_products1 WHERE ( MATCH(prodName,prodDescription,merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE) ) HAVING relevance > 0 AND dbProdID != '".$pDetails['dbProdID']."' ORDER BY relevance DESC LIMIT 0, ".$maxRows_product."";
} else {
$sql = "SELECT * FROM affiliSt_products1 WHERE MATCH (prodName,prodDescription,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") AND dbProdID != '".$pDetails['dbProdID']."' LIMIT 0, ".$maxRows_product."";
}
$getSimilar = mysql_query($sql, $databaseConnect) or die(mysql_error());
$similar = mysql_fetch_assoc($getSimilar);
Many thanks
kie.

Re: Searching mySQL databse with PHP

Posted: Tue Jul 14, 2009 3:31 pm
by Skara

Code: Select all

WHERE MATCH (prodName,prodDescription,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).")
I'm pretty sure... there's a fulltext search that includes those four items. If you want to search without prodDescription, the code should be so:

Code: Select all

WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).")
but the table will also have to be modified. I can tell you in phpMyAdmin I think all you need to do is check those three items and click on the fulltext button.

Re: Searching mySQL databse with PHP

Posted: Tue Jul 14, 2009 4:04 pm
by kieransymes
Skara wrote:

Code: Select all

WHERE MATCH (prodName,prodDescription,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).")
I'm pretty sure... there's a fulltext search that includes those four items. If you want to search without prodDescription, the code should be so:

Code: Select all

WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).")
but the table will also have to be modified. I can tell you in phpMyAdmin I think all you need to do is check those three items and click on the fulltext button.
Hi and thanks for your help

Turns out I was looking in completely in the wrong place. I now believe the search aspect of the code is in head.inc.php

I found this code.

Code: Select all

    // ---------- search
    case "search":
    if ($_GET['ps'] == NULL) { header("Location: ".$installDir['value']); }
    // get keywords
    $pieces = explode(" ", strip_tags($_GET['ps']));
    $kp = count($pieces);
    // reset pieces
    $pluspieces = '';
    for ($pi=0;$pi<$kp;$pi++) {
    $pluspieces .= $pieces[$pi].' ';
    }
    $pluspieces = rtrim($pluspieces, " ");
    // output search results
    $extracondition = '';
        if (isset($_GET['pscat'])){
        $extracondition .= " AND prodCategory = ".quote_smart($_GET['pscat'])."";
        }
        if (isset($_GET['pslow'])){
        $extracondition .= " AND (prodPrice BETWEEN ".quote_smart($_GET['pslow'])." AND ".quote_smart($_GET['pshigh']).")";
        }
    if ($indcount == 0) {
    $query_product = "SELECT *, ( (1.3 * (MATCH(prodName) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) + (0.6 * (MATCH(prodDescription,merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) ) AS relevance FROM affiliSt_products1 WHERE ( MATCH(prodName,prodDescription,merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE) ) $extracondition HAVING relevance > 0 $sortby";
    } else {
    $query_product = "SELECT *, MATCH (prodName,prodDescription,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") AS relevance FROM affiliSt_products1 WHERE MATCH (prodName,prodDescription,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") $extracondition HAVING relevance > 0 $sortby";
    }
    break;
Ive tried changing this to:

Code: Select all

if ($indcount == 0) {
    $query_product = "SELECT *, ( (1.3 * (MATCH(prodName) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) + (0.6 * (MATCH(merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE))) ) AS relevance FROM affiliSt_products1 WHERE ( MATCH(prodName,merchant,prodCategory,prodBrand) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE) ) $extracondition HAVING relevance > 0 $sortby";
    } else {
    $query_product = "SELECT *, MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") AS relevance FROM affiliSt_products1 WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") $extracondition HAVING relevance > 0 $sortby";
    }
    break;
I got an error saying

Code: Select all

Can't find FULLTEXT index matching the column list
I went into phpmyadmin and clicked the little icon with the T, when I hovered over it said Fulltext. Did this for merchant, prodBrand, prodCategory and prodName but still getting that error.

Re: Searching mySQL databse with PHP

Posted: Tue Jul 14, 2009 7:14 pm
by Skara
Given the last bit of code there, there must be a fulltext for "MATCH(prodName)" and one that includes "MATCH (prodName,prodBrand,prodCategory)" and you still have one in the first query for "MATCH(prodName,merchant,prodCategory,prodBrand)"