Product Search php mysql

Question

Im trying to create a search for a product from the database.

MY DB:

tbl_brand
-----------
brand_id Brand_name
1        Canon
2        Nikon

tbl_product
-------------
prod_id  prod_name                                                    brand_id
1        Canon PowerShot A810 16MP Point & Shoot Digital Camera       1
2        Canon Digital Camera PowerShot 8MP Point & Shoot             1
3        PowerShot 8MP Point & Shoot Digital Camera                   1
4        PowerShot 8MP Point & Shoot Digital                          1

My Keyword for search :

1. $keyword = 'Camera';
2. $keyword = 'Digital Camera';
3. $keyword = 'Canon Digital Camera';
4. $keyword = 'Canon Camera';

my query

Select * from tbl_product where prod_name like '%$keyword%'

I get results for first three keywords. But for 4th keyword i get results empty.

Canon Camera - keyword does not match the product name. But all the products are under canon brand only and all the products are camera only.

I need to show all the products for 'Canon Camera' keyword . I am newbie for php and mysql. Kindly help in this.

Answer 1

<?php include "header.php"; ?>

        <div id="page-wrapper">

            <div class="container-fluid">

                <!-- Page Heading -->
                <div class="row">
                    <div class="col-lg-12">
                         <?php
$query = $_GET['query']; 
    $min_length = 1;
  if(strlen($query) >= $min_length)
  { 
        $query = htmlspecialchars($query); 
        $query = mysqli_real_escape_string($con, $query);  

  echo "<h4>Your match result(s) for <font color=#B22222>$query</font></h4>
  <hr>";
  //select 2 tables - search
  $result = mysqli_query($con, "SELECT category_name, image, category_id, datetime, description, category_id FROM category 
  WHERE (`category_name` LIKE '%" .$query. "%') 
  UNION SELECT product_name, image, product_id, datetime, description, category_id FROM products WHERE (`product_name` LIKE '%" .$query. "%' )
  UNION SELECT username, fname, lname, email, contact, user_id FROM users WHERE (`username` LIKE '%" .$query. "%' )
  ") or die(mysqli_error()); 


        if (mysqli_num_rows($result) > 0)
  {
            while($info = mysqli_fetch_array($result))
   { 
        echo "";  ?>

        <?php Print ""  ?>
         <div class="row">
            <div class="col-md-7">
                <a href="#">
                    <img width="280" height="250" src="<?php echo $info['image']; ?>" alt="">
                </a>
            </div>
            <div class="col-md-5">
               <?php 
        if (strpos($info, '') === true)

        {  Print "<h4><a href='view_cat.php?category_id=".$info['category_id']."'>".$info['category_name']."</a></h4>";  }

    else if (strpos($query, 'username') === true)
    { Print "<h4><a href='view_cat.php?category_id=".$info['category_id']."'>".$info['username']."</a></h4>"; }



               ?>
                <hr>
                <h4>Added at: <?php echo date("d-M-Y", strtotime($info['datetime'])); ?></h4>
                <h4>Description: <?php echo $info['description']; ?></h4>
            </div>
        </div>
        <?php "";

            }

           }
        else { 
            echo "<p>
            <img src='../image/alert.png'><br />
            <h4>Sorry... No results for $query</h4>"; 
   echo "";  
        }   
    }
    else{ 
        echo "<h4>Search empty field. Please input your category name</h4>";
    }
?>
                    </div>
                </div>
                <!-- /.row -->

            </div>`enter code here`
            <!-- /.container-fluid -->

        </div>
        <!-- /#page-wrapper -->

    </div>
    <!-- /#wrapper -->`enter code here`

<?php include "footer.php"; ?>

Answer 2

try this with MATCH .. AGAINST

Here's how to get it to work:

1. First make sure your table uses the MyISAM storage engine. MySQL FULLTEXT indexes support only MyISAM tables. (edit 11/1/2012: MySQL 5.6 is introducing a FULLTEXT index type for InnoDB tables.)

   ALTER TABLE tbl_product ENGINE=MyISAM;
   

2. Create a fulltext index.

   CREATE FULLTEXT INDEX searchindex ON tbl_product(prod_name);
   

3. Search it!

   $keyword = mysql_real_escape_string($keyword);
   $query = mysql_query("SELECT * FROM tbl_product 
       WHERE MATCH(prod_name) AGAINST('$keyword')");
   

   Note that the columns you name in the MATCH clause must be the same columns in the same order as those you declared in the fulltext index definition. Otherwise it won't work.

Answer 3

$keywords = explode(' ', $keyword);
foreach($keywords as $words) {
$x++
if($x == 1) {
    $searchterm .= "prod_name LIKE %$words%";
}else {
    $searchterm .= " OR prod_name LIKE %$words%";
}
}
Select * from tbl_product where prod_name like '%$searchterm%';

it will search for each word u typed

Answer 4

You may try this...

$keyword = 'Canon Camera';
$keys = explode(" ",$keyword);
$sql = "SELECT * FROM tbl_product WHERE ";

//$wheresql = "prod_name LIKE '".$keyword."'";
$wheresql = "1=2 OR ";

foreach ($keys as $key) {
$wheresql .= " OR prod_name LIKE '".$key."' ";
}
print_r($sql.$wheresql);

Answer 5

Use this:-

$keywords = explode(' ', $keyword);
$searchTermKeywords = array();
foreach ($keywords as $word) 
{

$searchTermKeywords[] = "prod_name LIKE '%$word%'";

}

$result= mysql_query("SELECT * FROM tablename WHERE ".implode(' AND ', $searchTermKeywords).");

Explanation:- In this code i am splitting the search keywords using space and storing it into array. After that i am adding the sql string to an array. The no. of elements will depend on the number of search terms entered by the user. In the mysql_query function i am imploding the array with AND which after execution will look like(for 'digital camera' keyword):-

SELECT * FROM tablename WHERE prod_name LIKE '%digital%' AND prod_name LIKE '%camera%'

Answer 6

You simply don't have 'Canon Camera' in the database :) Your code is totally fine. With this search %'Canon Camera'% you can have everything before and after the string, but you MUST have the exact string 'Canon Camera' in the middle.

Answer 7

$keywords = str_replace(' ','%',$keywords);
Select * from tbl_product where prod_name like '%$keyword%'