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);
?>