How to retrieve multiple data from ajax post function?

Question

I have a select dropdown list and some input fields. My goal is when I select any option from the dropdown list it takes it's value and insert it into the input fields, and that is the ajax post function

   <script>
        $(document).ready(function(){
            $('#productSelect').on('change',function(){
                var selectedValue = $('#productSelect').val();
                $.post('php/loadProducts.php', {
                    productId : selectedValue
                }, function(data, status) {
                    $('#id').val(data);
                    $('#name').val(data);
                    $('#price').val(data);
                });
            });
        });
    </script>

and that is what happens in the "loadProdcut.php" file

<?php

if (isset($_POST['productId'])) {
    $productId = $_POST['productId'];
    $sql = "SELECT * FROM products WHERE product_id = '$productId';";
    $result = mysqli_query($conn, $sql);
    $resultCheck = mysqli_num_rows($result);
    if ($resultCheck > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $Id = $row['product_id'];
            echo $Id;
            $Name = $row['product_name'];
            echo $Name;
            $Price = $row['product_price'];
            echo $Price;
        }
    };
}
?>

Now when I select any option of the dropdown list it inserts all the values (id, name and price) in every single input field, so what I want to achieve is to place every single value into it's input feild.

Answer 1

It's simpler and more reliable to return JSON from an AJAX call to the server if for no other reason than you then return it all in one lump rather than in multiple echo's

Also a prepared statement protects you from bad actors attempting to mess up your database.

I am assuming as you are using a product_id to access a product table there will be only one row returned, so the loop is unnecessary

<?php

if (isset($_POST['productId'])) {
    
    // query with prameter ?
    $sql = "SELECT product_id, product_name, product_price 
            FROM products WHERE product_id = ?";
    // prepare it
    $stmt = $conn->prepare($sql):
    // bind the parameter to its data
    $stmt->bind_values('i', $_POST['productId']);
    $stmt->execute();

    // get resultset from the execution of the query
    $result = $stmt->get_result();

    $row = $result->fetch_assoc();

    //return the row (an array) converted to a JSON String
    echo json_encode($row);
}

Now you need to amend the javascript to process the nice object you have just returned.

<script>
$(document).ready(function(){
    $('#productSelect').on('change',function(){
        var selectedValue = $('#productSelect').val();
        $.post('php/loadProducts.php', {productId : selectedValue}, function(data, status) {
            $('#id').val(data.product_id);
            $('#name').val(data.product_name);
            $('#price').val(data.product_price);
        });
    });
});
</script>