How to fetch the price from database for the selected product in Dynamic row input fields?

Question

I'm setting up a dynamic row in a form. Need to fetch the price of the product from selecting dropdown list.

Product dropdown is already populated with values from database.

<?php 
function fill_unit_select_box($con){
    $output ='';
    $query = "SELECT * FROM pricelist";
    $result = $con->query($query);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
        $output .= '<option value="'.$row["product"].'">'.$row["product"].'</option>';
        }
        return $output;
    }
}
?>

<div class="form-body pal">
    <div class="row">
        <div class="col-md-12 col-md-offset-10">
            <div class="form-group">
                <button type="button" class="btn btn-info add-new-product_record_details"><i class="fa fa-plus"></i> Add Product</button>
            </div>
        </div>
    </div>
</div>
<div class="form-body pal">
    <div class="row">
        <div class="col-md-12">
            <div class="form-group">
                <div class="table-responsive">
                    <table class="table table-striped table-bordered table-hover add_row" id="product_record_details">
                        <thead>
                            <tr>
                                <th class="success text-center">Product<span class='require'>*</span></th>
                                <th class="success text-center">Price<span class='require'>*</span></th>
                                <th class="success text-center">Delete</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr></tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<div class="form-body pal">
    <div class="col-md-10 col-md-offset-10">
        <div class="form-group">
            <input type="hidden" id="main_product_name"/>
            <input type="hidden" id="main_row_product_count"/>
            <input type="hidden" name="submit_product_creation" class="btn btn-primary">
            <button type="submit" name="product_creation" id="product_creation" class="btn btn-primary"><i class="fa fa-arrow-right"></i> Save</button>
        </div>
    </div>
</div>


<script>
    $(document).ready(function() {
    // Append table with add row form on add new button click
        $(".add-new-product_record_details").click(function(){
            //$(this).attr("disabled", "disabled");
            var index = $("#product_record_details tbody tr:last-child").index();
            // Random value has generated to set the unique id value of the table
            var randaom_val = Math.floor(Math.random() * 100000000)
            var row_index = index+randaom_val;
            var row = '<tr>' +
                '<td><select name="product_id[]"  id="product_id'+row_index+'" onchange="fetch_product_price(this.id,'+row_index+')" class="select_format form-control"><option value="">Select</option><?php echo fill_unit_select_box($con); ?></select></td>'+
                '<td><input type="number" name="alead_price[]" id="alead_price'+row_index+'" placeholder="Price"  class="form-control"/></td>' +
        '<td><input type="number" name="aquantity[]" id="aquantity'+row_index+'" placeholder="Price"  class="form-control"/></td>' +
                '<td><button type="button" class="delete btn btn-danger">Delete</button></td>' +
                '</tr>';
            $("#product_record_details").append(row);
            $(".select_format").select2({
                width: 'resolve'
            });
            // $("#main_spare_record_details tbody tr").eq(index + 1).find(".add, .edit").toggle();
            $("#product_record_details tbody tr").eq(index + 1).find("").toggle();
        });
        // Delete row on delete button click
        $(document).on("click", ".delete", function() {
            $(this).parents("tr").remove();
            //$(".add-new").removeAttr("disabled");
        });
    });
</script>

<script>
    function fetch_product_price(product_id,row_count) {
        var test = $('.select_format').val();
        // alert(test);
        $('#main_product_name').val($('#'+spare_id).val());
        $('#main_row_product_count').val(row_count);
    }
</script>

What i Need

1. So onchange the product, it should fetch the price of the product from database.

2. Auto multiply the price with entering the number of Quantity For ex: The fetched price of the product is "2000" and we are entering the quantity value as "2" so the output should be display as "4000", This value to be entered in price column

Thanks

Answer 1

OK, this is an example of what I meant, when you populate your products <select> include the prices along in a data-price attribute

$output = "";
while($row = $result->fetch_assoc()) {
$output .= '<option value="'. $row["id"] .'" data-price = "' .$row["price"] . '">'. 
         htmlspecialchars($row["product"]).'</option>';
}
return $output;

Now on the client side, get the price of the product in the event listener of the select

$("select#products").change(function (){
  var price = $(this).find("option:selected").data("price");
  var quantity = $("input#quantity").val();
  var total = price * quantity;
  alert("total is " + total);
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<input id="quantity" placeholder="quantity">
<select id="products">
  <option data-price="100">product1</option>
  <option data-price="200">product2</option>
  <option data-price="300">product3</option>
  <option data-price="400">product4</option>
</select>

note:

Make sure you verify the price on the server if you want to perform any action based on this price, because the prices could be changed after you inject them into the HTML. In fact you should be doing that anyway as the client is never trusted in general.

For example you are going to perform an action(sell or purchase a product) based on the price submitted by the client

$price = $_POST['price'];
$productId = $_POST['productId'];
//don't take that price as guaranteed, any one can send any data to your server
// so you need to check if this price is really the price of this product
$query = "select price from product where product_id = ?"
$stmt = $con->prepare($query);
$stmt->bind_param("i", $productId);
$stmt->execute();
// now if the REAL price you just selected from the DB is the same as
//$_POST['price'] you proceed, if not , you abort.