close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

MySQLi Search Contain Keyword

Thread began 9/12/2014 12:34 am by Christopher West | Last modified 9/26/2014 12:48 pm by Jason Byrnes | 1029 views | 7 replies |

Christopher WestCommunity Expert

MySQLi Search Contain Keyword

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

Sign in to reply to this post

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

Sign in to reply to this post

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

Sign in to reply to this post

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.

Sign in to reply to this post

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

Sign in to reply to this post

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."%");

Sign in to reply to this post

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;
    }
}
?>
Sign in to reply to this post

Jason ByrnesWebAssist

glad to hear it is working.

Sign in to reply to this post

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...