Autocomplete search box from MySQL that displays multiple columns-CodePudding

I've been trying to make an autocomplete search box from a MySQL database that displays multiple columns of data when searching.(ie. Searching for an item #, at it displays the item number, manufacturer, and price)

Below is what I have currently done, which displays everything in one line separated by spaces. I would like to have a way to change the style for each column or make each result display in multiple lines if possible.

I'm a complete noob at this so any advice/resources would be awesome!

//ajax-db-search.php
 <?php
require_once "db.php";
if (isset($_GET['term'])) {
     
   $query = "SELECT DISTINCT MFG_Item_ID, MFG_Name, Price FROM H_Item_Master WHERE MFG_Item_ID LIKE '{$_GET['term']}%' LIMIT 5";
    $result = mysqli_query($conn, $query);
 
    if (mysqli_num_rows($result) > 0) {
     while ($user = mysqli_fetch_array($result)) {
      $res[] = $user['MFG_Item_ID'] . " " . $user['MFG_Name'] . " " . $user['Price'];
     }
    } else {
      $res = array();
    }
    //return json res
    echo json_encode($res);
}
?>

//in my index.php
<!-- Topbar Search Catalog -->
                   
                    <form
                        >
                        <div >
                            <input type="text" name="term" id="term" placeholder="Search Catalog" 
                                aria-label="Search" aria-describedby="basic-addon2">
                            <div >
                                <button  id="benchbutton" type="Submit">
                                
                                
                                    <i ></i>
                                    
                                </button>
                            </div>
                        </div>
                    </form>

                        <script type="text/javascript">
  $(function() {
     $( "#term" ).autocomplete({
       source: 'ajax-db-search.php',
     });
  });
</script>

CodePudding user response：

You can override the default autocomplete style this way, so you can use html br tags and your own css stylesheet :

<script type="text/javascript">
  $(function() {
     $( "#term" ).autocomplete({
       source: 'ajax-db-search.php',
       select: function(event, ui) {
           $("#term").val(ui.item.name);
           return false;
        }
     })
    .autocomplete("instance")._renderItem = function(ul, item) {
        return $("<li class='each'>")
        .append("<div class='item'><span class='upc'>"  
            item.upc   "</span><br><span class='name'>"  
            item.name   "</span><br><span class='price'>"  
            item.price   "</span><br></div>")
        .appendTo(ul);
    };
});
</script>

Using the span's classes, you have full control on any attribute (upc, name and price) in CSS :

<style>
.each .item .upc{
  font-style:italic;
  color:blue;
}
</style>

Here is the final result :

Using this dataset :

PS : Here is how to use prepared statement to select and fetch datas from database :

    if(isset($_GET['term']))
    {   
        $term = '%' . $_GET['term'] . '%';

        $sql = "SELECT * FROM items WHERE CONCAT(upc, name) LIKE ? LIMIT 5";

        $stmt = $conn->prepare($sql); 
        $stmt->bind_param("s", $term);   
    
        $stmt->execute();

        $result = $stmt->get_result();
    
        $items = [];

        if ($result->num_rows > 0) {

          // output data of each row
          while($row = $result->fetch_assoc()) {

            $items[] = $row;

          }
        } 

        $conn->close();
    
        echo json_encode($items);

    }