I am new in PHP and javascript I am creating an order form that has four fields customer, product, price, amount what I want. when I select a product it will get the last order price of the selected customer if he already exists. if the new customer then gets the price from the product_list table. here is my code.
<?php $conn= new mysqli('localhost','root','','pharmacy_db')or die("Could not connect to mysql".mysqli_error($con));?> <label>Customer</label> <div> <select name="customer_id" id="" class="js-example-basic-single" style="width:40%" > <option value="0" selected="">Guest</option> <?php $customer = $conn->query("SELECT * FROM customer_list order by name asc"); while($row=$customer->fetch_assoc()): ?> <option value="<?php echo $row['id'] ?>"><?php echo $row['name'] ?></option> <?php endwhile; ?> </select> </div> <div><select name="" id="product" class="js-example-basic-single" style="width:85%"> <option value=""></option> <?php $product = $conn->query("SELECT * FROM product_list order by name asc"); while($row=$product->fetch_assoc()): $prod[$row['id']] = $row; ?> <option value="<?php echo $row['id'] ?>" data-name="<?php echo $row['name'] ?>" data-measurement="<?php echo $row['measurement'] ?>" data-description="<?php echo $row['description'] ?>" data-price="<?php echo $row['price'] ?>"><?php echo $row['name']?></option> <?php endwhile; ?> </select> </div> <div><input type="number" step="any" id="qty" ></div> <div><input type="number" step="any" id="price" ></div> <div><input type="number" step="any" id="amount" ></div> <div><button class="btn btn-block btn-sm btn-primary" type="button" id="add_list"><i class="fa fa-plus"></i> Add</button></td> <script> $(document).ready(function() { $('.js-example-basic-single').select2(); });
Advertisement
Answer
okay at the first i dont know your customer & product_list schema table, so i just i create table order
only with information at your code, and i think you need to avoid named table with function already exist at mysql, so i rename table order
to order_list
, the schema will like this
table customer_list
CREATE TABLE `customer_list` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `customer_list` ADD PRIMARY KEY (`id`); INSERT INTO `customer_list` (`id`, `name`) VALUES (1, 'Customer A'), (2, 'Customer B');
table product_list
CREATE TABLE `product_list` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `measurement` varchar(255) NOT NULL, `description` varchar(255) NOT NULL, `price` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `product_list` ADD PRIMARY KEY (`id`); INSERT INTO `product_list` (`id`, `name`, `measurement`, `description`, `price`) VALUES (1, 'Product 1', '-', '-', 100), (2, 'Product 2', '-', '-', 50);
table order_list
CREATE TABLE `order_list` ( `id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `qty` int(11) NOT NULL, `price` int(11) NOT NULL, `amount` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `order_list` ADD PRIMARY KEY (`id`); INSERT INTO `order_list` (`id`, `customer_id`, `product_id`, `qty`, `price`, `amount`) VALUES (1, 1, 1, 5, 100, 500), (2, 1, 1, 3, 100, 300), (3, 1, 2, 2, 50, 100);
i create 2 file, first is form.php , its include code you create and add javascript
to get the last order customer from table order_list
with ajax. the code will like this
<?php $conn = new mysqli('localhost','root','','test_db'); ?> <label>Customer</label> <div> <select name="customer_id" id="customer_id" class="js-example-basic-single" style="width:40%" > <option value="0" selected="">Guest</option> <?php $customer = $conn->query("SELECT * FROM customer_list order by name asc"); while($row=$customer->fetch_assoc()): ?> <option value="<?php echo $row['id'] ?>"><?php echo $row['name'] ?></option> <?php endwhile; ?> </select> </div> <div> <select name="product" id="product" class="js-example-basic-single" style="width:85%"> <option value=""></option> <?php $product = $conn->query("SELECT * FROM product_list order by name asc"); while($row=$product->fetch_assoc()): $prod[$row['id']] = $row; ?> <option value="<?php echo $row['id'] ?>" data-name="<?php echo $row['name'] ?>" data-measurement="<?php echo $row['measurement'] ?>" data-description="<?php echo $row['description'] ?>" data-price="<?php echo $row['price'] ?>"><?php echo $row['name']?></option> <?php endwhile; ?> </select> </div> <div> <input type="number" step="any" id="qty" value=""> </div> <div> <input type="number" step="any" id="price" value=""> </div> <div> <input type="number" step="any" id="amount" value=""> </div> <div> <button class="btn btn-block btn-sm btn-primary" type="button" id="add_list"><i class="fa fa-plus"></i> Add</button> </div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <script> $("#product,#customer_id").change(function(){ var customer_id = $('#customer_id').val(); var product_id = $('#product').val(); var price = $('#product').find(':selected').attr('data-price') $.ajax({ type: "get", url: 'getorder.php', data: {customer_id:customer_id,product_id:product_id}, success: function(response){ data = JSON.parse(response); if(data!=null){ $('#qty').val(data.qty); $('#price').val(data.price); $('#amount').val(data.amount); }else{ $('#qty').val(''); $('#price').val(price); $('#amount').val(''); } } }); }); $("#qty,#price").change(function(){ var qty = $('#qty').val(); var price = $('#price').val(); var amount = qty * price; $('#amount').val(amount); }); </script>
the function will fired when id product
and customer_id
at select option change, at the first its 3 var , customer_id
, product_id
, and price
it will get the selected value from selected option customer_id
and product
and var price
will save data-price
from selected option product
. and i use ajax to get the last data from order_list
it will send var customer_id,product_id
to the second form getorder.php
getorder.php
will like this
<?php $conn = new mysqli('localhost','root','','test_db'); $query = "SELECT * FROM order_list WHERE customer_id='".$_GET['customer_id']."' AND product_id='".$_GET['product_id']."' ORDER BY id DESC LIMIT 1"; $result = $conn->query($query); $row = $result->fetch_assoc(); echo json_encode($row); ?>
that code above is for get the last data from table order_list
and the result will parse to json, at the ajax if succes get data success: function(response)
it will check if the response null or not, if null the input field qty
and amount
will be empty and price
input field value filled with var price
, if the response not null the data input field will filled with data you get. and at the function $("#qty,#price").change(function(){});
it will calculate the input field amount
when data at input field qty
or price
changed