Mokey Nepal: Multiple Tables Ajax Live Data Search using Jquery And PHP PDO

Monday, March 27, 2017

This is a simple tutorial for multiple tables live search in pdo jquery and ajax.

First Create A Database called "multi" in phpmyadmin.
After Creating Database in phpmyadmin create tables called 'makes' and 'country'.
Then Add Column's on country as country_id and set as autoincrement ,country_name and set as varchar.
And Then Add Column's on makes as makes_id and set as autoincrement, makes_name and set as varchar,country_id and set as integer.
And last of database section insert some values on tables columns and insert country_id of makes as 1,2,3.

Copy this php scripts and paste it on index.php file .

bramento online study

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Webslesson Tutorial</title>
<script src="/js/jquery-1.10.2.min.js"></script>
<script src="/js/bootstrap.min.js"></script>
<link href="/css/bootstrap.min.css" rel="stylesheet" />
</head>
<body>
<div class="container">
<br />
<h2 align="center">Ajax Live Data Search using Jquery PHP PDO MySql</h2><br />
<div class="form-group">
<div class="input-group">
<span class="input-group-addon">Search</span>
<input type="text" name="search" id="search_text" placeholder="Search by Customer Name" class="form-control" />
</div>
</div>
<br />
<div id="result"></div>
<script>
$(document).ready(function(){
$('#search_text').keyup(function(){
var txt = $(this).val();
if(txt != '')
{
$.ajax({
url:"fetch.php",
method:"post",
data:{search:txt},
dataType:"text",
success:function(data)
{
$('#result').html(data);
}
});
}
else
{
$('#result').html('');
}
});
});
</script>
</div>
</body>
</html>

Copy and paste this php scripts on fetch.php file.

<?Php
$dbhost = 'localhost';
$dbname = 'your database name';
$dbuser = 'root';
$dbpass = '';
try{
$dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
$dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(PDOException $ex){
die($ex->getMessage());
}
?>
<?php
$output = '';
$query = $dbcon->prepare("select c.* , p.* from country c,makes p where country_name LIKE '%".$_POST["search"]."%' AND c.country_id= p.country_id LIMIT 0 , 5");
$query->bindValue(1, PDO::PARAM_STR);
$query->execute();
// Display search result
if (!$query->rowCount() == 0) {
echo "     Search found :<br/>";
while($row = $query->fetch(PDO::FETCH_ASSOC))
{
$output .= '<h4 align="center">Search Result</h4>';
$output .= '
<tr>
<td>'.$row["country_name"].'</td>
<td>'.$row["makes_name"].'</td>
</tr>
';
?>
<?php }
echo $output;
} else {?>
<div class="search_title"> <?php echo 'Nothing found'; ?></div>
<?php }
?>