Autocomplete search box from MySQL that displays multiple columns

Question

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
                        class="d-none d-sm-inline-block form-inline mr-auto ml-md-3 my-2 my-md-0 mw-100 navbar-search">
                        <div class="input-group">
                            <input type="text" name="term" id="term" placeholder="Search Catalog" class="form-control"
                                aria-label="Search" aria-describedby="basic-addon2">
                            <div class="input-group-append">
                                <button class="btn btn-primary" id="benchbutton" type="Submit">
                                
                                
                                    <i class="fas fa-search fa-sm"></i>
                                    
                                </button>
                            </div>
                        </div>
                    </form>

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

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Jul 04 '22 at 10:37

score 1 · Accepted Answer · answered Jul 04 '22 at 13:50

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);

    }

Thanks so much! I know I terribly worded my question, but this was exactly what I was looking for. Also thanks for the prepared statement explanation, was just looking up videos on this as you replied! — ac4570, Jul 04 '22 at 13:59
I am happy to help you ! If it is the right answer for you, you should check it as the right answer and up vote if you wish ;) — Alexandre Martin, Jul 04 '22 at 14:06
I’ll be sure to come back and upvote once I reach 15 reputation! — ac4570, Jul 04 '22 at 14:12

Autocomplete search box from MySQL that displays multiple columns

1 Answers1