php - Populate html form select element with results from query on database table

Question

I am trying to make a form which includes a text input and a dropdown select element. The goal is to be able to enter text into the text box with a customer last name which will be used in a query. The results from the query then need to be placed in the select dropdown box for further use with the value being custID and the text being the first and last name of the customer and possibly there phone number. So far whenever I run it and enter any value I only get data[] and status: succesful in my alert box no matter what I input.

Here is the form and the ajax I have been playing with.

    <form  method="post" class="form-container" >
                <input type="text" name="lName" id="lName" placeholder="Last name" 
                onkeyup="showCustomer(this.value)"><br>
                <select id = "custSelect" name="custSelect0" onchange="show(this)">
                <option value="">-- Select Customer--</option>
            </select><br>
    </form>
<script>

    function showCustomer(str) {
          //alert("click");
          var LastName = str;
          var ele = document.getElementById('custSelect');
       //alert (LastName);
       $.ajax({
            url:"getcustomer.php",
            type:"POST",
            data:{customer:LastName},

            success:function(data, status){
                alert("Data: " + data + "
Status: " + status);
                //alert("Search succesful")
                    /*  $.each(data, function(i,item)) {
                          // POPULATE SELECT ELEMENT WITH JSON.
                          ele.innerHTML = ele.innerHTML +
                              '<option value="' + data[i]['custId'] + '">' + data[i]['fname'] + ' ' + data[i]['lname'] + '</option>';
                      }*/
            }
          })
        }

        function show(ele) {
          // GET THE SELECTED VALUE FROM <select> ELEMENT AND SHOW IT.
          var msg = document.getElementById('msg');
          msg.innerHTML = 'Selected Customer: <b>' + ele.options[ele.selectedIndex].text + '</b> </br>' +
              'ID: <b>' + ele.value + '</b>';
}
    </script>

Here is the php file I am using to access the database and do the query. It is using PDO.

<?php

      require_once "configPDO.php";
    if(isset($_POST['customer'])){
      $customer = $_POST['customer'];
    
    
    $data = array();
    
    $sql = "SELECT *
    FROM `customers`
    WHERE `LastName`
    LIKE '$customer'";
    
    $statement = $connect->prepare($sql);
    //$statement->bind_param("s", $_GET['q']);
    $statement->execute();
    $result = $statement->fetchAll();
    
    foreach($result as $row){
      $data[] = array(
        'custId' => $row["Cust_ID"],
        'fname' => $row["FirstName"],
        'lname' => $row["LastName"],
        'phone' => $row["PhoneNumber"],
        'altPhone' => $row["AltPhone"]
      );
    }
    
    echo json_encode($data);
    }
    
    ?>

question from:https://stackoverflow.com/questions/65838862/populate-html-form-select-element-with-results-from-query-on-database-table

Answer 1

After taking some of the suggested changes using info from this video https://www.youtube.com/watch?v=RcW8mMiIexc and correcting the spelling of my table to search, I have been able to get it to return results back to the form.

<?php
      require_once "/home/users/web/b1240/dom.heather93124/public_html/resources/configPDO.php";
    if(isset($_POST['customer'])){
        $customer = $_POST['customer'];
    
        $data = array();
    
        try{
          $sql = "SELECT * FROM `Customer` WHERE `LastName` LIKE :customer";
          $customer = "%$customer%";
          $stmt = $connect->prepare($sql);
          $stmt->bindValue(':customer', $customer);
          $stmt->execute();
          while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            $data[] = array(
              'custId' => $row["Cust_ID"],
              'fname' => $row["FirstName"],
              'lname' => $row["LastName"],
              'phone' => $row["PhoneNumber"],
              'altPhone' => $row["AltPhone"]
            );
            }
    
        echo json_encode($data);
        }catch(Exception $e){
          echo $e-getMessage();
        }
    }
    ?>