How can I pass a hidden SQL ID field value alongside other data during a form lookup?

I’m doing my best to put together a basic property management app that will allow users to check out keys to contractors (and check them back in of course). I’m a relatively inexperienced coder and am currently struggling to work out how to pass an additional SQL ID value during a lookup.

The user can currently search for a contractor name and company in the ‘searchcontractor’ input. The backend-search.php file populates this input with FirstName, LastName and Company from the Contractors database, but I cannot work out how to also pass that record’s ContractorID value into a hidden input ‘contractorid’.

Would hugely appreciate any pointers, thank you.

PHP

<?php
include "msbkeys.php";
$sql = "SELECT KeySets.KeySetID, KeySets.KeyDescription, Sites.SiteID, Sites.SiteName FROM Sites INNER JOIN KeySets ON Sites.SiteID=KeySets.SiteID WHERE KeySets.KeyAssignedTo IS NULL;";
$result = $db->query($sql);
if ($result->num_rows > 0) {
echo "<table><tr><th>Keyset ID</th><th>Site ID</th><th>Site name</th><th>Description</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
    $keysetidsubmit = $row['KeySetID'];
echo "<tr><td>".$row["KeySetID"]."</td><td>".$row["SiteID"]."</td><td> ".$row["SiteName"]."</td><td> ".$row["KeyDescription"]."</td><td> <form  method='post'><input type='hidden' name='keysetid' value=".$keysetidsubmit."><div class='search-box'>
        <input name='searchcontractor' type='text' autocomplete='off' placeholder='Search contractor...' />
        <div class='result'></div>
    </div><input type='hidden' name='contractorid' value="**how can I populate this with the ID of the contractor chosen in the searchcontractor input?**"> <input name='checkoutkey' type='submit' value='Check out key'></form>  </td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}
mysqli_close($db); // Close connection
?>

JavaScript

<script>
$(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("backend-search.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>

backend-search.php

<?php
include "msbkeys.php";
 
if(isset($_REQUEST["term"])){
    // Prepare a select statement
    $sql = "SELECT * FROM Contractors WHERE CONCAT (FirstName,' ',LastName) LIKE ?";
    
    if($stmt = mysqli_prepare($db, $sql)){
        // Bind variables to the prepared statement as parameters
        mysqli_stmt_bind_param($stmt, "s", $param_term);
        
        // Set parameters
        $param_term = $_REQUEST["term"] . '%';
        
        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);
            
            // Check number of rows in the result set
            if(mysqli_num_rows($result) > 0){
                // Fetch result rows as an associative array
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
                    echo "<p>" . $row["FirstName"]." ".$row["LastName"] ." (".$row["Company"].")</p>";
                }
            } else{
                echo "<p>No matches found</p>";
            }
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($db);
        }
    }
     
    // Close statement
    mysqli_stmt_close($stmt);
}
 
// close connection
mysqli_close($db);
?>

Answer

Add the contractor ID as an attribute in the <p> containing the contractor name.

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
    echo "<p data-contractorid='{$row["id"]}'>" . $row["FirstName"]." ".$row["LastName"] ." (".$row["Company"].")</p>";
}

Replace $row["id"] with the actual column name of the contractor ID.

Then copy this to the hidden input field.

$(document).on("click", ".result p", function(){
    $(this).closest(".search-box").find('input[type="text"]').val($(this).text());
    $(this).closest("td").find('input[name=contractorid]').val($(this).data('contractorid'));
    $(this).parent(".result").empty();
});

Advertisement

Answer