Tuesday 31 May 2016

How to search multiple words at a time in Mysql php


If you want to learn how can search multiple words from Mysql table in a single query in php. You have show any web site there is on search textbox available for search data from that website. User enter the search query and it will result search result. This type of things we will learn in this post. For this things I have make one form with one textbox for entering search query and one button for search. When user click on search button use's request will send to server and on server side if user enter more than one words than that words will be converted into array by using explode() function and from that array I will make search string with mysql LIKE operator with that array and making complete search query for more than one words. In Video you can get get more information in details.

Source Code

Database


 --  
 -- Table structure for table `tbl_video`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_video` (  
  `video_id` int(11) NOT NULL AUTO_INCREMENT,  
  `video_title` varchar(300) NOT NULL,  
  `video_link` varchar(100) NOT NULL,  
  PRIMARY KEY (`video_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;  
 --  
 -- Dumping data for table `tbl_video`  
 --  
 INSERT INTO `tbl_video` (`video_id`, `video_title`, `video_link`) VALUES  
 (1, 'Export MySQL data to Excel in PHP - PHP Tutorial', 'http://www.google.com'),  
 (2, 'Live Table Add Edit Delete using Ajax Jquery in PHP Mysql', 'http://www.google.com'),  
 (3, 'Make SEO Friendly or Clean Url in PHP using .htaccess', 'http://www.google.com'),  
 (4, 'How to Add Watertext or Watermark to an Image using PHP GD Library', 'http://www.google.com'),  
 (5, 'Create Simple Image using PHP', 'http://www.google.com'),  
 (6, 'How to check Multiple value exists in an Array in PHP', 'http://www.google.com'),  
 (7, 'How to merge two PHP JSON Array', 'http://www.google.com'),  
 (8, 'How To Insert Data Using Stored Procedure In Php Mysql', 'http://www.google.com'),  
 (9, 'How to check Username availability using php, Ajax, Jquery and Mysql', 'http://www.google.com'),  
 (10, 'Rename uploaded image in php with upload validation', 'http://www.google.com'),  
 (11, 'How to generate simple random password in php?', 'http://www.google.com'),  
 (12, 'Auto Refresh Div Content Using jQuery and AJAX', 'http://www.google.com'),  
 (13, 'Insert Update Delete using Stored Procedure in Mysql and PHP', 'http://www.google.com');  

advance_search.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 if(isset($_POST["submit"]))  
 {  
      if(!empty($_POST["search"]))  
      {  
           $query = str_replace(" ", "+", $_POST["search"]);  
           header("location:advance_search.php?search=" . $query);  
      }  
 }  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Search multiple words at a time in Mysql php</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container" style="width:500px;">  
                <h3 align="center">Search multiple words at a time in Mysql php</h3><br />  
                <form method="post">  
                     <label>Enter Search Text</label>  
                     <input type="text" name="search" class="form-control" value="<?php if(isset($_GET["search"])) echo $_GET["search"]; ?>" />  
                     <br />  
                     <input type="submit" name="submit" class="btn btn-info" value="Search" />  
                </form>  
                <br /><br />  
                <div class="table-responsive">  
                     <table class="table table-bordered">  
                     <?php  
                     if(isset($_GET["search"]))  
                     {  
                          $condition = '';  
                          $query = explode(" ", $_GET["search"]);  
                          foreach($query as $text)  
                          {  
                               $condition .= "video_title LIKE '%".mysqli_real_escape_string($connect, $text)."%' OR ";  
                          }  
                          $condition = substr($condition, 0, -4);  
                          $sql_query = "SELECT * FROM tbl_video WHERE " . $condition;  
                          $result = mysqli_query($connect, $sql_query);  
                          if(mysqli_num_rows($result) > 0)  
                          {  
                               while($row = mysqli_fetch_array($result))  
                               {  
                                    echo '<tr><td>'.$row["video_title"].'</td></tr>';  
                               }  
                          }  
                          else  
                          {  
                               echo '<label>Data not Found</label>';  
                          }  
                     }  
                     ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 </html>  

5 comments:

  1. hi
    please make a video and source code as " search multiple radio button at a time" or " by clicking multi radio buttons and have result search"
    thank you

    ReplyDelete
  2. hello can you make a search engine that has a result of link. if the link click it will go to another page

    ReplyDelete
  3. hello can do the code in pdo

    ReplyDelete
  4. Thanks... How can I display records in descending order means How to show new records first

    ReplyDelete