Autocomplete search multiple fields from database

Autocomplete search multiple fields from database


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.

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <html xmlns="">
    <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" />
            $().ready(function() {
            $("#search_data").autocomplete("get_emp_list.php", {
               width: 237,
                matchContains: true,
                mustMatch: true,
                selectFirst: false
            }); $("#search_data").result(function(event, data, formatted) {
        $("#search_data").result(function(event, data, formatted) {

      <form name="empform" method="POST" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
            <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>
                    <td>Employee Name: </td>
                    <td><input type="text" name="employee_name" id="employee_name" value="<?php echo $employee_name;?>"  size="30"></td>
  2. get_emp_list.php code
    if ($q == '') {
       header("HTTP/1.0 404 Not Found", true, 404);  


    $sql = "SELECT pe.employee_no, pe.employee_name, pe.passport_no,
        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";