Autocomplete search multiple fields from database

Autocomplete search multiple fields from database

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.




  1. <!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>                                                                                                                                 
  2. 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";
    }