dynamic/live search box with php, javascript, mysql

I went through a bunch of iterations with this a couple years ago and didn’t get it working.

This is the second one I tried recently and was able to get what I wanted for hyperlinked results.

https://www.codingcage.com/2016/12/autocomplete-search-with-href-link-php.html

code blobs for posterity:

database:

--
-- Database: `codingcage`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_posts`
--

CREATE TABLE IF NOT EXISTS `tbl_posts` (
  `postID` int(11) NOT NULL AUTO_INCREMENT,
  `postTitle` varchar(255) NOT NULL,
  `postUrl` varchar(255) NOT NULL,
  PRIMARY KEY (`postID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `tbl_posts`
--

INSERT INTO `tbl_posts` (`postID`, `postTitle`, `postUrl`) VALUES
(1, 'Simple jQuery Add, Update, Delete with PHP and MySQL', 'http://goo.gl/IL6NTr'),
(2, '15 Free Bootstrap Admin Themes Demo and Download', 'http://goo.gl/1dBwEy'),
(3, 'Easy Ajax Image Upload with jQuery, PHP', 'http://goo.gl/jXZ6LY'),
(4, 'How to Send HTML Format eMails in PHP using PHPMailer', 'http://goo.gl/kQrzJP'),
(5, 'Ajax Bootstrap Signup Form with jQuery PHP and MySQL', 'http://goo.gl/yxKrha'),
(6, 'Submit PHP Form without Page Refresh using jQuery, Ajax', 'http://goo.gl/14vlBe'),
(7, 'How to Convert MySQL Rows into JSON Format in PHP', 'http://goo.gl/qgOiwB'),
(8, 'Designing Bootstrap Signup Form with jQuery Validation', 'http://goo.gl/nECERc'),
(9, 'Upload, Insert, Update, Delete an Image using PHP MySQL', 'http://goo.gl/HRJrDD'),
(10, 'Login Registration with Email Verification, Forgot Password using PHP', 'http://goo.gl/O9FKN1');

php:

<?php
 
 $DBhost = "localhost";
 $DBuser = "root";
 $DBpass = "";
 $DBname = "codingcage";
 
 try {
  $DBcon = new PDO("mysql:host=$DBhost;dbname=$DBname",$DBuser,$DBpass);
 } catch(PDOException $ex){
  die($ex->getMessage());
 }
$keyword = trim($_REQUEST['term']); // this is user input

 $sugg_json = array();    // this is for displaying json data as a autosearch suggestion
 $json_row = array();     // this is for stroring mysql results in json string
 

 $keyword = preg_replace('/\s+/', ' ', $keyword); // it will replace multiple spaces from the input.

 $query = 'SELECT postID, postTitle, postUrl FROM tbl_posts WHERE postTitle LIKE :term'; // select query
 
 $stmt = $DBcon->prepare( $query );
 $stmt->execute(array(':term'=>"%$keyword%"));
 
 if ( $stmt->rowCount()>0 ) {
  
  while($recResult = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $json_row["id"] = $recResult['postUrl'];
      $json_row["value"] = $recResult['postTitle'];
      $json_row["label"] = $recResult['postTitle'];
      array_push($sugg_json, $json_row);
  }
  
 } else {
     $json_row["id"] = "#";
     $json_row["value"] = "";
     $json_row["label"] = "Nothing Found!";
     array_push($sugg_json, $json_row);
 }
 
 $jsonOutput = json_encode($sugg_json, JSON_UNESCAPED_SLASHES); 
 print $jsonOutput;

html:

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="viewport" content="initial-scale=1.0, maximum-scale=2.0">
<title>AutoComplete Example in PHP MySQL</title>
<link rel="stylesheet" href="bootstrap/css/bootstrap.min.css">
<link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
</head>

<body>

 <div class="container">
    
     <div class="page-header">
        <h3 style="color:#00a2d1; font-size:30px; font-family: Impact, Charcoal, sans-serif; text-align: center;">AutoComplete Search with Href Link in PHP MySQL</h3>
        </div>
         
        <div class="row">
        
         <div class="col-lg-12 text-center">
          
         <div class="col-lg-offset-2">
             <form>
             <div class="form-group">
             <div class="input-group">
             <input id="txtSearch" class="form-control input-lg" type="text" placeholder="Search for PHP, MySQL, Ajax and jQuery" />
             <div class="input-group-addon"><i class="glyphicon glyphicon-search"></i></div>
             </div>
             </div>
             </form>  
             </div> 
                
            </div>
        
        </div>
        
    </div>

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="bootstrap/js/bootstrap.min.js"></script>

<script>
$(document).ready(function(){
 
 $('#txtSearch').autocomplete({
     source: "post_search.php",
     minLength: 2,
     select: function(event, ui) {
         var url = ui.item.id;
         if (url != '#') {
             location.href = url
         }
     },
     open: function(event, ui) {
         $(".ui-autocomplete").css("z-index", 1000)
     }
 })
 
}); 
</script>

</body>
</html>

This site was the first one that I got working – but I wasn’t happy with using it for hyperlinked results.

https://codeforgeek.com/ajax-search-box-php-mysql/

code blobs for posterity:

 

WARNING: sanitize your input and prevent code injection attacks

html file:

<html>
  <head>
    <title>Ajax Search Box using PHP and MySQL</title>
    <scriptsrc="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js">        </script>
     <script src="typeahead.min.js"></script>
  </head>
  <body>
   <inputtype="text" name="typeahead">
  </body>


  <script>
    $(document).ready(function(){
      $('input.typeahead').typeahead({
        name: 'typeahead',
        remote:'search.php?key=%QUERY',
        limit : 10
      });
    });
  </script>
</html>
search.php:
<?php
    $key=$_GET['key'];
    $array = array();
    $con=mysqli_connect("localhost","root","","demos");
    $query=mysqli_query($con, "select * from cfg_demos where title LIKE '%{$key}%'");
    while($row=mysqli_fetch_assoc($query))
    {
      $array[] = $row['title'];
    }
    echo json_encode($array);
    mysqli_close($con);
?>