Add a fast AJAX Search to your Database

Just start typing in a search field to get your results with some additional info such as reg#, sire and dam and date of birth.
You will notice that there is the letter “Q” at every end of functions, filenames,… the reason is, you can have more than one fast search on the same page, BUT the functions, filenames,… must be different.

Before starting you should create a view in your mySQL Database with exactly the fields we use later in the php file:

select `Search1`.`dogid` AS `countryQ_id`,`Search1`.`dogname` AS `countryQ_name`,`Search1`.`dogid` AS `Search1_id`,
`Search1`.`reg_nr` AS `reg_nr`,`dogTable3`.`sirenamelink` AS `sire`,`dogTable3`.`damnamelink` AS `dam`,
`Search1`.`DOB` AS `DOB` from (`Search1` join `dogTable3`) where (`Search1`.`dogid` = `dogTable3`.`dogid`) 
 order by `Search1`.`dogname`

Now let’s start with the html:

<center> <input type="text"  
class = "form-control round" style="display: inline;" id="countryQ_id" placeholder="Search by typing a dog name ..." 
onkeyup="autocompletQ() " autocomplete="off"></center>
<br> 
<ul style="margin-left: 2px;" id="countryQ_list_id"></ul> 
<script type="text/javascript" async src="js/scriptDogQ.js?ts=<?= time(); ?>"></script>

Now the JS file – the name of the js file must correspond with the one in the code above!

scriptDogQ.js:

function autocompletQ() {
var min_length = 3; // min caracters to display the autocomplete
var keyword = $('#countryQ_id').val();
if (keyword.length >= min_length) {
$.ajax({
url: '../ajax/searchDogQ.php',
type: 'POST',
data: {keyword:keyword},
success:function(data){
$('#countryQ_list_id').show();
$('#countryQ_list_id').html(data);
}
});
} else {
$('#countryQ_list_id').hide();
}
}
$( '<style>ul.countryQ_list_id { width: ' + $( '.search-field' ).outerWidth() + 'px; }</style>').appendTo('head');
function set_itemQ(item) {
// change input value
$('#countryQ_id').val(item);
// hide proposition list
$('#countryQ_list_id').hide();
}

You can change in line 2 the min length for results to appear.

The last file we need is the search file – searchDogQ.php:

<?php
$keyword = '%'.$_POST['keyword'].'%';
$sql = "SELECT * FROM selectSearchQ WHERE countryQ_name LIKE (:keyword) ORDER BY countryQ_name ASC LIMIT 0, 50";
$query = $dbh->prepare($sql);
$query->bindParam(':keyword', $keyword, PDO::PARAM_STR);
$query->execute();
$list = $query->fetchAll();
foreach ($list as $rs) {
// put in bold the written text
$countryQ_name = str_replace($_POST['keyword'], '<b>'.$_POST['keyword'].'</b>', $rs['countryQ_name']);
// add new option
echo '<li><a href='details.php?id='.$rs['countryQ_id'].'&gens=4 >'.$countryQ_name.'</a> - '.$rs['reg_nr'].' 
('.$rs['sire'].' x '.$rs['dam'].') *'.$rs['DOB'].'</li>';
}
?>