I am trying to do a live search using AJAX, PHP, and MYSQL. User-Profile.php has all user data retrieved from the database and there is a search box to input details to find users whose first name starting as "xxx"
Using AJAX script I am getting value from the Input box and POST it to search.php where it query database and retrieve search result.
Using Echo Alert I have found the search.php getting the value from AJAX script but it not query the database to show the actual result.
Even though I am searching exact data in table & database it shows output as else condition statement "NO RECORDS FOUND".
Please find my below script and Please help me to resolve it.
1. User-Profile.php
<head>
<script src="vendor/jquery/3.3.1/jquery.min.js">
</script>
<script src="vendor/ajax/libs/popper.js/1.14.6/umd/popper.min.js">
</script>
<script src="vendor/bootstrap/4.2.1/js/bootstrap.min.js">
</script>
</head>
<div class="container-fluid">
<div class="row justify-content-center"><div class="form-inline">
<label for="search" class="font-weight-bold lead">Search User</label>
<input type="text" name="search" id="search_text">
</div></div>
<div class="row">
<div class="col-lg-12">
<div class="table-responsive">
<?php
$stmt=$con->prepare("select * from user");
$stmt->execute();
$result=$stmt->get_result();
?>
<table class="table" id="table-data">
<thead>
<tr>
<th>First Name</th>
<th>Mobile Number</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php while($row=$result->fetch_assoc()){ ?>
<tr>
<td><?= $row['fname']; ?></td>
<td><?= $row['mobile']; ?></td>
<td><?= $row['miruid']; ?></td>
</tr>
<?php } ?>
</tbody>
</table></div></div></div></div>
<script type="text/javascript">
$(document).ready(function(){
$("#search_text").keyup(function(){
var search = $(this).val();
$.ajax({
url:'search.php',
method:'post',
data:{query:search},
success:function(response){
$("#table-data").html(response);
}
});
});
});
</script>
2. search.php
<?php
require('php-includes/connect.php');
?>
<?php
$output='';
if(isset($_POST['query'])){
$search=$_POST['query'];
$stmt =$con->prepare("select * from user where fname like
concat('%',?,'%')");
$stmt->bind_param("ss",$search,$search);
}else{
$stmt = $con->prepare("select * from user");
}
$stmt->execute();
$result=$stmt->get_result();
if($result->num_rows>0){
$output="<thead>
<tr>
<th>First Name</th>
<th>Mobile Number</th>
<th>Email</th>
</tr>
</thead>
<tbody>";
while($row=$result->fetch_assoc()){
$output .="
<tr>
<td>".$row['fname']."</td>
<td>".$row['mobile']."</td>
<td>".$row['miruid']."</td>
</tr>";
}
$output .="</tbody>";
echo $output;
}else{
echo"<h3>No Records Found!</h3>";
}
?>
I have gone through comments and fixed the issue by replacing below two line code,
1. Error
$stmt =$con->prepare("select * from user where fname like concat('%',?,'%')");
$stmt->bind_param("ss",$search,$search);
2. Solution
$stmt =$con->prepare("select * from user where fname like '%".$search."%'");
//Removed this line code $stmt->bind_param("ss",$search,$search);//