Live MySQL Database Search on user input

Question

So I have recently created this small script that searches my Customer Database when the user start typing in a certain box.

This allows them to tie a record to another record to make it easier in the future.

However when the customer they're looking for appears they have to click the name, once they've clicked the name it'll populate the box.

However I need to be able to identify which customer they've clicked on so I can store the customer ID in the database for future.

So far the script i've got will display 3 columns, the customers firstName, lastName and ID. How can i separate the ID away from the firstName and lastName to store it in a database?

This is my PHP search code...

try {
$customerConn = new PDO('mysql:host=DATABASE HOST NAME HERE;dbname=DATABASE NAME HERE;charset=utf8', 'USERNAME', 'PASSWORD');  
// echo 'client version: ', $conn->getAttribute(PDO::ATTR_CLIENT_VERSION), "\n";
// echo 'server version: ', $conn->getAttribute(PDO::ATTR_SERVER_VERSION), "\n";
$customerConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$customerConn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $err) {
var_dump($err->getMessage());
die('...');
}

// Attempt search query execution
try{
if(isset($_REQUEST["term"])){
    // create prepared statement
    $sql = "SELECT * FROM customerLeads WHERE lastName LIKE :term";
    $stmt = $customerConn->prepare($sql);
    $term = $_REQUEST["term"] . '%';
    // bind parameters to statement
    $stmt->bindParam(":term", $term);
    // execute the prepared statement
    $stmt->execute();
    if($stmt->rowCount() > 0){
        while($row = $stmt->fetch()){
            echo "<p>".$row['firstName']." ".$row["lastName"]." - ".$row['customerID']."</p>";
        }
    } else{
        echo "<p>No matches found</p>";
    }
}  
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close statement
unset($stmt);

// Close connection
unset($customerConn);

Also I've replaced the database details just for security!

Edit Here is the JavaScript that I have in page

        <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $('.search-box input[type="text"]').on("keyup input", function(){
            /* Get input value on change */
            var inputVal = $(this).val();
            var resultDropdown = $(this).siblings(".result");
            if(inputVal.length){
                $.get("../../scripts/customerSearch.php", {term: inputVal}).done(function(data){
                    // Display the returned data in browser
                    resultDropdown.html(data);
                });
            } else{
                resultDropdown.empty();
            }
        });

        // Set search input value on click of result item
        $(document).on("click", ".result p", function(){
            $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
            $(this).parent(".result").empty();
        });
    });
    </script>

and here is the relevant HTML

<div class="form-group search-box">
 <label>Customer Name</label>
 <input type="text" placeholder="Search Customer" autocomplete="off" name="customerName">
 <div class="result"></div>
</div> <!-- end .form-group --> 

Answer 1

If I understood correctly (!?) then you could modify the generated HTML slightly to include, for instance, a dataset attribute such as data-cid which you could use to later send an ajax request to the backend so that you can store the customerID. For example - not including any Ajax code

Bind an event listener to the results container - the listener will register hits on elements that match the criteria of being a paragraph tag with a data-cid attribute. In place of the alert simply send an ajax request to whatever php script will process the click event on the customerID/details.

The HTML snippet below is an emulation of the generated HTML from the db lookup which is being populated into the div.result container

<div class='form-group'>
    <div class='result'>
        <p data-cid=23>lux interior - 23</p>
        <p data-cid=44>marilyn monroe - 44</p>
        <p data-cid=27>sid vicious - 27</p>
    </div>
</div>

<script>
    document.querySelector('.form-group > div.result').addEventListener('click',e=>{
        e.preventDefault();
        if( e.target!=e.currentTarget && e.target.tagName.toLowerCase()=='p' && e.target.hasAttribute('data-cid') ){
            alert( 'send ajax request to log customerID='+e.target.dataset.cid )
        }
    })
</script>

A modified version to include basic Ajax request

<div class='form-group search-box'>
    <label>Customer Name</label>
    <input type='text' placeholder='Search Customer' autocomplete='off' name='customerName'>
    <div class='result'>
        <p data-cid=23>bobby davro - 23</p>
        <p data-cid=44>marilyn monroe - 44</p>
        <p data-cid=27>sid vicious - 27</p>
    </div>
</div>


<script src='//code.jquery.com/jquery-latest.js'></script>
<script>

    let container=document.querySelector('.form-group');
    let results=container.querySelector('div.result');
    let text=container.querySelector( 'input[ name="customerName" ][ type="text" ]' );

    results.addEventListener('click',e=>{
        e.preventDefault();     
        if( e.target!=e.currentTarget && e.target.tagName.toLowerCase()=='p' && e.target.hasAttribute('data-cid') ){
            $.ajax({
                url:location.href,
                data:{cid:e.target.dataset.cid},
                type:'POST',
                error:e=>{ alert('Error: '+e) },
                success:r=>{ alert( 'logged to db: '+r ) }
            });
            text.value=e.target.innerText
        }
    })
</script>