Response title
This is preview!
Autocomplete search multiple fields from database
in Using jQuery Plugins
•
7 years ago
Hi,
Good day!
Before I used jquery in autocomplete I only search one field from database. Now, I need to search multiple fields in one search box.
here is my code:
This code is working but my problem is when I type employee id the autocomplete display list is the employee name. How can i make it that when I type employee name, the list display is employee name, if I search id, list of id will display same with passport no. and res id.
Good day!
Before I used jquery in autocomplete I only search one field from database. Now, I need to search multiple fields in one search box.
here is my code:
This code is working but my problem is when I type employee id the autocomplete display list is the employee name. How can i make it that when I type employee name, the list display is employee name, if I search id, list of id will display same with passport no. and res id.
- <!DOCTYPE
html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="js/jquery.js"></script>
<script type='text/javascript' src='js/jquery.autocomplete.js'></script>
<link rel="stylesheet" type="text/css" href="js/jquery.autocomplete.css" />
<script>
$().ready(function() {
$("#search_data").autocomplete("get_emp_list.php", {
width: 237,
matchContains: true,
mustMatch: true,
selectFirst: false
}); $("#search_data").result(function(event, data, formatted) {
$("#employee_no").val(data[1]);
});
$("#search_data").result(function(event, data, formatted) {
$("#employee_name").val(data[0]);
});
});
</script>
</head>
<body>
<form name="empform" method="POST" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<table>
<tr>
<td style="border: none;color:#80600a;font-weight:bold;">Search:</td>
<td><input type="text" name="search_data" id="search_data" value="" size="35" autofocus></td>
</tr> <tr>
<td>Employee Number: </td>
<td><input type="text" name="employee_no" id="employee_no" value="<?php echo $employee_no;?>" size="30"></td>
</tr>
<tr>
<td>Employee Name: </td>
<td><input type="text" name="employee_name" id="employee_name" value="<?php echo $employee_name;?>" size="30"></td>
</tr>
</table>
</body>
</html> - get_emp_list.php
code
if ($q == '') {
header("HTTP/1.0 404 Not Found", true, 404);
}
else{
$sql = "SELECT pe.employee_no, pe.employee_name, pe.passport_no,
gov.res_id
FROM tbl_personal_info AS pe JOIN tbl_public_info AS pu ON (pe.employee_no = pu.employee_no) JOIN tbl_e_government_info AS gov ON (pu.employee_no = gov.employee_no)
WHERE pe.employee_no LIKE '%".$q."%' OR pe.employee_name LIKE '%".$q."%' OR pe.passport_no LIKE '%".$q."%' OR gov.res_id LIKE '%".$q."%'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$pid = $row["employee_no"];
$employee_name = $row["employee_name"];
$passport_no = $row["passport_no"];
$res_id = $row["res_id"];
echo "$employee_name|$pid|$passport_no|$res_id\n";
}
} else {
echo "0 results";
}
1