Searching mySQL databse with PHP - PLEASEEEEE HELP!

[kie]

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)

 

// 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.

[J.Daniels]

If you don't want to search the product description, I believe you just need to take it out of the MATCH function:

 

if ($indcount == 0) {
    $sql = "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) ) HAVING relevance > 0 AND dbProdID != '".$pDetails['dbProdID']."' ORDER BY relevance DESC LIMIT 0, ".$maxRows_product."";
} else {
    $sql = "SELECT * FROM affiliSt_products1 WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") AND dbProdID != '".$pDetails['dbProdID']."' LIMIT 0, ".$maxRows_product."";
}

[kie]

If you don't want to search the product description, I believe you just need to take it out of the MATCH function:

 

if ($indcount == 0) {
    $sql = "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) ) HAVING relevance > 0 AND dbProdID != '".$pDetails['dbProdID']."' ORDER BY relevance DESC LIMIT 0, ".$maxRows_product."";
} else {
    $sql = "SELECT * FROM affiliSt_products1 WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces).") AND dbProdID != '".$pDetails['dbProdID']."' LIMIT 0, ".$maxRows_product."";
}

 

I thought that would be the case, whenever I do that though I get this:

 

Can't find FULLTEXT index matching the column list

 

Edit:

Having said that, I get that message when I click on the More Info link, it still brings up some unrelated products.

 

So that means I would be in the wrong place completely right?

[J.Daniels]

It was my mistake.  The MATCH function must match a FULLTEXT index.  To create a new index see the MySQL Reference Manual

[kie]

I gotta be honest, that is way over my head

 

Here's some more info:

 

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.

 

    // ---------- 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:

 

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

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.

[J.Daniels]

To create another FULLTEXT index, run this in phpmyadmin:

 

CREATE FULLTEXT INDEX newIndex ON affiliSt_products1 (prodName,prodBrand,prodCategory);

[kie]

To create another FULLTEXT index, run this in phpmyadmin:

 

CREATE FULLTEXT INDEX newIndex ON affiliSt_products1 (prodName,prodBrand,prodCategory);

 

Oh my God!  You are a legend!  That has worked really well!  Thanks very much.

 

One more thing I need to do though that I don't know much about.

 

At the moment, if somebody searches for "chicco pram" the script will match anything for "chicco" OR "pram".  I really need it to treat it as "chicco" AND "pram".  Does anybody know the area of the code to make this change?

 

Again, many thanks

[J.Daniels]

What is the value of $pluspieces when you do a search?

[kie]

What is the value of $pluspieces when you do a search?

 

I would have no idea to be honest, I am really new to PHP, this is all a learning curve.

 

It looks like they have tried to buld this feature in but there is a comment saying it's disabled.

 

	if ($navC['searchType'] == 0) {
$searchType = 'OR';
} else {
$searchType = 'AND';
}

 

I have tried to comment out the code like this:

 

	//if ($navC['searchType'] == 0) {
//$searchType = 'OR';
//} else 
       //{
$searchType = 'AND';
//}

 

Didnt work though

 

That's the the code for head.inc.php which is here:

 

http://tooseypegs.co.uk/public/head.inc.txt

 

[J.Daniels]

By default, MySQL will perform a natural language search.  To change it to a boolean search, try making these changes:

 

    $pluspieces .= $pieces[$pi].' ';

to:

    $pluspieces .= '+'.$pieces[$pi].' ';

 

and

 

$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";

to:

$query_product = "SELECT *, MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE) AS relevance FROM affiliSt_products1 WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (".quote_smart($pluspieces)." IN BOOLEAN MODE) $extracondition HAVING relevance > 0 $sortby";

[kie]

Again, that seems to have done the trick!

 

Thankyou very much once again! 

[kie]

Hia again, just ran some test searches and for some reason it brings up different (but still relevant results) if you type "wipes" or "Wipes" and vice versa.  Any suggestions why?

 

Thanks again

Kieran

[J.Daniels]

I think the search is case sensitive.

[kie]

I think the search is case sensitive.

 

Is there anyway to get the script to ignore the case?

[aschk]

You need to set your character set and encoding to a case insensitive version.

What is your current table character encoding?

[J.Daniels]

This may work:

 

$query_product = "SELECT *, MATCH (prodName,prodBrand,prodCategory) AGAINST (UPPER(".quote_smart($pluspieces).") IN BOOLEAN MODE) AS relevance FROM affiliSt_products1 WHERE MATCH (prodName,prodBrand,prodCategory) AGAINST (UPPER(".quote_smart($pluspieces).") IN BOOLEAN MODE) $extracondition HAVING relevance > 0 $sortby";

[kie]

You need to set your character set and encoding to a case insensitive version.

What is your current table character encoding?

 

I'm assuming that's where it says collation in phpmyadmin?

 

It's showing as latin1_swedish_ci for the affiliSt_products1 table.