MySQLi Search Contain Keyword

9/11/2014 9:34 pm | #1 Christopher WestCommunity Expert

Hi there, (with the help of Ray) I have managed to get a a product name search via MySQLi recordset...However what its currently doing is matching the URL value to the database value...in otherwords it only checks exact matches.....what I would like to do is the search box checks for the value to "contain".....for example a customer enters into the search box: ARMCHAIR ...but what I would like it to do is return all database records containing CHAIR..

Here is the currently MySQLi recordset:

php:

if (isset($_GET["Product"])) {

                    $productName = $_GET["Product"];

                    $rsProducts->addFilter("ProductName","=","s",$productName);

                }

and the form im using:

<form>
 <input name="Department" type="hidden" id="Department" value="Search">
 <input type="text" name="Product" value=""><input type="submit">

 </form>

I have looked up on Google, and found things like incorporating php such as STRPOS and PREG_MATCH, however Im a little hazy in understanding how I would put this into my PHP recordset?

Any help would be appriciated :)
Chris

9/12/2014 9:03 am | #2 Jason ByrnesWebAssist

so, your getting the value to use in the search using this code:
$productName = $_GET["Product"];

you could remove ARM from ARMCHAIR using:
$productName = str_replace("ARM","" ,$_GET["Product"]);

and where you want to use includes, you will need to edit this line:
$rsProducts->addFilter("ProductName","=","s",$productName);

to change the filter type "s" (string) to "i" (includes)
$rsProducts->addFilter("ProductName","=","i",$productName);

also edit the SQL, which you don't include here, to change the where clause from = to LIKE

9/12/2014 10:22 am | #3 Christopher WestCommunity Expert

you could remove ARM from ARMCHAIR using:
$productName = str_replace("ARM","" ,$_GET["Product"]);

Obviously ARMCHAIR was just an example. So in the above code I wouldn't want to hardcore in "ARM" unless the above was means as a visual example in some way. I think you confused me a little with the above coding suggestion :)

9/12/2014 10:36 am | #4 Jason ByrnesWebAssist

there has to be some sort of predetermined known factor.

a specific word, or number of characters, or something.

your asking a wide question using a specific example without further details on the parameters.

I think you confused me a little with the above coding suggestion :)

the above coding example addresses the question as asked with the information provided.

if you want something more generic for the replacement, you'll need to provide more details.

9/12/2014 10:46 am | #5 Christopher WestCommunity Expert

ok well I was hoping for a more generic solution :)

actually re-reading what my original post stated...I was asking for something generic originally, in this case if you read what I originally said:

"what I would like to do is the search box checks for the value to "contain".....for example a customer enters into the search box: ARMCHAIR"

im ny oriignal above statement, I did say "for example a customer enters..." So I assumed it was obvious want I wanted was generic (just saying) :)

Slight issue, you told me to change the = to LIKE in my recordset...however the recordset in use is a straight "SELECT * FROM products so there is no place to put the LIKE clause.

here is the complete recordset (its using a case statement and the search by product name is at the bottom of this code block)

php:

case "Search":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products");

            

            if (isset($_GET["Price"])) {

                $prices = explode(":",$_GET["Price"]);

                $rsProducts->addFilter("ProductPrice",">=","d",$prices[0]);

                $rsProducts->addFilter("ProductPrice","<=","d",$prices[1]);

            }

            

            $productTypes = array();

            

                if (isset($_GET["Wood"])) {

                    $productTypes[] = "Wood";

                }

                if (isset($_GET["Metal"])) {

                    $productTypes[] = "Metal";

                }

                if (isset($_GET["Glass"])) {

                    $productTypes[] = "Glass";

                }

                if (sizeof($productTypes) > 0) $rsProducts->addFilter("ProductType","=","s",$productTypes);

                if (isset($_GET["Product"])) {

                    $productName = $_GET["Product"];

                    $rsProducts->addFilter("ProductName","=","s",$productName);

                }

            $rsProducts->setFilter();

            $rsProducts->execute();

        break;

Chris

9/12/2014 11:09 am | #6 Jason ByrnesWebAssist

How would you like the more generic solution to work? every time there is a word containing chair, remove anything but chair? you need to give some specifics on what you want.

sorry, haven't used the setFilter method of adding comparisons before, you would change it to:

$rsProducts->addFilter("ProductName","LIKE","s","%".$productName."%");

9/26/2014 9:40 am | #7 Christopher WestCommunity Expert

Hi Jason, had a support ticket with Ray and the solution was:

php:

if (isset($_GET["Product"])) {

                    $productName = $_GET["Product"];

                    $splitName = explode(" ",$productName);

                    for ($x=0; $x<sizeof($splitName); $x++) {

                        $splitName[$x] = "%" . $splitName[$x]."%";

                    }

                    $rsProducts->addFilter("ProductName","LIKE","s",$splitName);

                }

            $rsProducts->setFilter();

            $rsProducts->execute();

and in context in case it help anyone else:

Because I am using multiple search boxes on my page I used a SWITCH/CASE

php:

<?php

if(true){

    

    switch(isset($_GET['Department'])?$_GET['Department']:""){

        

        case "New-Arrivals":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductNewArrival = 1");

            $rsProducts->execute();

        break;

        

        case "Best-Sellers":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductBestSeller = 1");

            $rsProducts->execute();

        break;

        

        case "InStore-Now":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products WHERE ProductInStoreNow = 1");

            $rsProducts->execute();

        break;

        

        case "Search":

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT * FROM products");

            

            if (isset($_GET["Price"])) {

                $prices = explode(":",$_GET["Price"]);

                $rsProducts->addFilter("ProductPrice",">=","d",$prices[0]);

                $rsProducts->addFilter("ProductPrice","<=","d",$prices[1]);

            }

            

            $productTypes = array();

            

                if (isset($_GET["Wood"])) {

                    $productTypes[] = "Wood";

                }

                if (isset($_GET["Metal"])) {

                    $productTypes[] = "Metal";

                }

                if (isset($_GET["Glass"])) {

                    $productTypes[] = "Glass";

                }

                if (sizeof($productTypes) > 0) $rsProducts->addFilter("ProductType","=","s",$productTypes);

                if (isset($_GET["Product"])) {

                    $productName = $_GET["Product"];

                    $splitName = explode(" ",$productName);

                    for ($x=0; $x<sizeof($splitName); $x++) {

                        $splitName[$x] = "%" . $splitName[$x]."%";

                    }

                    $rsProducts->addFilter("ProductName","LIKE","s",$splitName);

                }

            $rsProducts->setFilter();

            $rsProducts->execute();

        break;

            

        default:

            $rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);

            $rsProducts->setQuery("SELECT products.*, categories.* FROM products INNER JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE categories.CategoryLink = ? AND categories.CategoryLocation = ?");

            $rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"")  ."", "-1"); //WAQB_Param1

            $rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"")  ."", "-1"); //WAQB_Param2

            $rsProducts->execute();

        break;

    }

}

?>

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

MySQLi Search Contain Keyword

9/11/2014 9:34 pm | #1 Christopher WestCommunity Expert