I'm able to get jQuery AJAX live search working on an input field and it returns the desired result that I want. Now I want to do a compound search on two fields.
This is what I have but it is not working or returning any errors:
Form:
<div class="form-group">
<div class="input-group">
<span class="input-group-addon">Name</span>
<input class="form-control" type="text" name="searchName" id="searchName" placeholder="Search by name">
<span class="input-group-addon">Class</span>
<select name="current_class" id="current_class" class="form-control">
## I populate my list from the database using php
<?php
$sql = "SELECT * from classes";
if ($result = mysqli_query($connection, $sql)){
if(mysqli_num_rows($result) > 0){
while ($row = mysqli_fetch_array($result)){
echo "<option value=\"{$row['class_id']}\"
{$row['class_name']} </option>";
}
}
}
?>
</select>
</div>
##results are displayed
<div id="result"></div>
<div>
Script:
<script>
$(document).ready(function(){
$('#searchName').keyup(function(){
var name = $('#searchName').val();
var current_class = $('#current_class').val();
if (name != '' || current_class != '') {
$.ajax({
url:"fetch2.php",
method:"post",
data:{searchName:name, current_class:current_class},
dataType:"text",
success:function(data){
$("#result").html(data);
}
});
} else {
$("#result").html('');
}
});
$('#current_class').keyup(function(){
var name = $('#searchName').val();
var current_class = $('#current_class').val();
if (name != '' || current_class != '') {
$.ajax({
url:"fetch2.php",
method:"post",
data:{searchName:name, current_class:current_class},
dataType:"text",
success:function(data){
$("#result").html(data);
}
});
} else {
$("#result").html('');
}
});
});
</script>
Query in fetch2.php:
$search_name = $_POST['searchName'];
$search_class = $_POST['current_class'];
$select__args = array();
$from__args = array();
$where__args = array("1=1");
$select__args[] = "student_id";
$from__args[] = "students";
$where__args[] = "(CONCAT(first_name,' ',surname) LIKE '%{$search_name}%')";
if($search_class != ''){
$where__args[] = "current_class_id like '%{$search_class}%'";
}
$select_clause = implode(", ", $select__args);
$from_clause = implode(",", $from__args);
$where_clause = "(" . implode(") AND (", $where__args) . ")";
$query = "SELECT {$select_clause} FROM {$from_clause} WHERE {$where_clause}";
What am I missing here? What I want is a live search to be done on both fields either by name or by class. If there are two(2) students with the name: 'Nathan Siafa' but are in two(2) different classes(Grade 1 and 2) I will have to search by both name and class. Which is not the result I'm getting.
UPDATE
I have changed my code to match @maksbd19's answer. It works a little for me but I'm not getting the desired result that I want (live search experience on the two fields). I have just realized there maybe other problems. Ex. If there are two students - John Amos Doe and John James Doe with classes (grade 9 and 10). I realized that if a search is done for John Amos Doe whose class is grade 9 it works fine and returns the right record, but if I now want the record of John James Doe whose class is grade 10 I get the record of John Amos Doe (Note: my query searches by last name and first name).
I added a separate field to distinguish between the records. Now with @maksbd19 if I want John James Doe record and search is "John Doe" I still get "John Amos Doe" record even if I add the class for "John James Doe" in the class field it remains the same.
The way I get the precise record is by having the right class for "John James Doe" in placed and pretending to delete my current search value "John Doe" and it works. To me it's like ajax is only live searching on the name field.
Note: middle name is not require in my application that is why I'm not trying to perform a query on it, but is there a way that I can leverage the power of searching by a student full name(first, middle, last) even though not all student will have a middle name?
Update 2
I have manage to get the live search working the two(2) fields, based on the code I have here. My problem now is I have two(2) students in my tables - 'John Amos Doe(grade 10)' and 'John Lewis Doe(grade 9)'. If I search by first name with their specific classes it returns the desired result.
If I search 'John Doe' It brings 'John Amos Doe' record for example, and if I now want 'John Lewis Doe' record I have to put in the precise class (grade 9) in the class field which returns nothing.
I also tried to include the middle name in my search query but to no avail. Here is how I tried:
$where__args[] = "(CONCAT(first_name,' ',middle_name,' ',surname) LIKE '%{$search_name}%') OR (CONCAT(first_name,' ',surname) LIKE '%{$search_name}%')";
