Question 183693 in Mysql Question posted in
The official MySQL documentation can be found here.

Fetch Product Price From MySQL Database Automatically

I am creating an Invoice System in PHP and MySQL. What I want is for the user to be able to select a product in a drop down field and the price of that product should be fetched from database and displayed in the price input field automatically using javascript. So far I am able to fetch the products and display them in a drop down field. Note that the name of the product and price are both stored in the same table.

 <div class="col-md-4 mb-3">
                <select name="product_name[]" id="live_search" class="form-control" placeholder="product name" required autocomplete="off">
                <option value="" selected="selected"></option>
               <?php 
                  // fetch products
                  $sqlCAT="SELECT * FROM  products";
                  $queryCAT=mysqli_query($dbCon, $sqlCAT);
                  while($result = mysqli_fetch_assoc($queryCAT))
                  {
                  $product = $result['product_name'];
                  $id = $result['product_id'];
                  ?>
              <option value="<?php echo $product ?>"><?php echo $product ?></option>
              <?php } ?>
              </select>
            </div>
<div class="col-md-2 mb-3">
                <input type="number" name="product_price[]" id="search_result" class="form-control" placeholder="item price" required autocomplete="off">
            </div>
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to solve the problem with the following code. I added an event listener function to the product drop down box and used ajax to fetch the price and append it to the price input field.

    // get product names and assign the ID in the drop down box
     <select name="product_name[]" id="product" class="form-control" placeholder="product name" required autocomplete="off">
                <option value="" selected="selected">Select Product</option>
               <?php 
                  // fetch product categories
                  $sqlCAT="SELECT * FROM  products";
                  $queryCAT=mysqli_query($dbCon, $sqlCAT);
                  while($result = mysqli_fetch_assoc($queryCAT))
                  {
                  $product = $result['product_name'];
                  $id = $result['product_id'];
                  ?>
              <option value="<?php echo $id ?>"><?php echo $product ?></option>
              <?php } ?>
              </select>

    Secondly I created a file to fetch the prices

    // get_price.php
if (isset($_GET['id'])) {
    $productId = $_GET['id'];
    
    $result = mysqli_query($dbCon, "SELECT product_price FROM products WHERE product_id = $productId");
    $row = mysqli_fetch_assoc($result);
    
    // Return the price as JSON
    echo json_encode(array('price' => $row['product_price']));
}

    Lastly I added some javascript

    // live search
   $("#product").change(function() {
        // Retrieve the selected product ID
        var productId = $(this).val(); 
        // Send an AJAX request to fetch the product price
        $.ajax({
            type: "GET",
            url: "get_price.php",
            data: { id: productId },
            dataType: "json",
            success: function(response) {
                // Update the price input field with the fetched price
                $("#price").val(response.price);
            },
            error: function(xhr, status, error) {
                console.error(error);
            }
        });
    });

    The problem was solved


  2. 0

    As you provided little in the way of code and virtually no database table details you will need to study and modify the following rather than take as a working example.

    The essence of the following is:

    • Bind an event listener to a suitable parent element for the SELECT element(s) that will process change events. The event listener here tests that the select menu is named appropriately before sending a GET request using AJAX to the backend PHP server.
    • The PHP script intercepts GET requests that have an ID and an action in the querystring. By using the action parameter you could fire many AJAX requests to the same backend script but with different values for action if required.
    • Create a Prepared Statement to mitigate SQL injection attacks that queries the db using the ID of the product so that you can find the price. ( You might have several items named the same but the IDs will be different, hence using ID! )
    • Send the price back to the AJAX callback using exit($price)
    • The AJAX callback then updates the HTML input element to show the price.
    <?php
    
    require 'dbcon.php';
    
    if( isset( 
        $_GET['action'],
        $_GET['id']
    )){
        
        ob_clean();
        
        switch( $_GET['action'] ){
            case 'get-price':
                # create the sql that will select the price ( Assumed column is called "price" )
                $sql='select `price` from `products` where `product_id`=?';
                $stmt=$dbCon->prepare( $sql );
                $stmt->bind_param('s',$_GET['id']);
                $stmt->execute();
                $stmt->bind_result( $price );
                $stmt->fetch();
                
                exit( $price );
            break;
            
            case 'banana':
                $sql='select * from x';
                /* etc */
            break;
        }
        exit();
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title></title>
    </head>
    <body>
    
        <div class="col-md-4 mb-3">
            <select name="product_name[]" id="live_search" class="form-control" placeholder="product name" required autocomplete="off">
                <option selected hidden disabled>Select Product
                <?php 

                    $sql="SELECT * FROM  products";
                    $res=mysqli_query( $dbCon, $sql );
                    
                    while( $result = mysqli_fetch_assoc( $res ) ){
                        $product = $result['product_name'];
                        $id = $result['product_id'];
                        
                        printf('<option value="%s">%s',$id,$product);
                }
                ?>
            </select>
        </div>
        <div class="col-md-2 mb-3">
            <input type="number" name="product_price[]" id="search_result" class="form-control" placeholder="item price" required autocomplete="off" />
        </div>
        
        
        <script>
        
            const callback=(r)=>document.getElementById('search_result').value=r
            
            // delegated event listener bound to the document as the full HTML structure is unknown
            document.addEventListener('change',e=>{
                
                // if the event was triggered by a SELECT element being changed - proceed
                if( e.target instanceof HTMLSelectElement ){
                
                    // if the element was the Product select
                    if( e.target.name=='product_name[]' ){
                    
                        // send GET request using ID of product and update price displayed in "search_result"
                        
                        fetch( '?action=get-price&id=' + e.target.value )
                            .then( r=>r.text() )
                            .then( callback )
                            .catch( alert )
                    }
                }
            })
        </script>
    </body>
</html>

    This is just one possible way of doing it…this could easily be modified to process ALL select elements and other elements too which would make it far more useful

    Login or Signup to reply.
  3. 0

    You’re asking 2 things.

    1. How to get the price value from your dropdown

    1.1 Add price to the option, since it’s in the same table you can do this easily (I guessed how your columns are named, so replace $productPrice & $productName with the correct values)

    <option value="<?php echo $productPrice ?>"><?php echo $productName ?></option>

    1.2 Fetch the selected option via Js & display in browser console for demo purposes

    <script>
    const selector = document.getElementById("live_search");
    
    function onChange() {
        const value = selector.value;
        const text = selector.options[selector.selectedIndex].text;
        
        console.log(value, text);
    }
    
    selector.onchange = onChange;
    onChange();
</script>

    1. Update the snippet above to show the price to the price input box via the value property through JS

       const selector = document.getElementById("live_search");
 const priceField = document.getElementById("search_result");

 function onChange() {
     const value = selector.value;
     const text = selector.options[selector.selectedIndex].text;

     priceField.value = value;
 }

 selector.onchange = onChange;
 onChange();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search