Update multiple record with single query in database using php
0
I am making an invoice in PHP where multiple products are inserted at once into one table and there grand total goes to another table. I am trying to UPDATE the invoice in MYSQL and PHP. When I press the submit button, the multiple records data from the form goes to the update.php but the query does not run.
database.php
<?php
$connect = mysqli_connect('localhost','root','','invoice');
if (!$connect){
die("Connection failed: " . mysqli_connect_error());
}
?>
edit_invoice.php
<!DOCTYPE html>
<html>
<head>
<title></title>
<style>
table,tr,td,th { border: 1px black solid;}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<body>
<a href="list_invoice.php">Back to Invoice List</a><br><br>
<?php
include('database.php');
$invoice_number = $_GET['invoice_number'];
$sql = "SELECT * from invoice where invoice_number = '$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query->num_rows > 0) {
// output data of each row
$fetch = $query->fetch_assoc();
}
?>
<form method="POST" action="update_invoice.php">
<table>
<thead>
<th>Product</th>
<th>Price</th>
<th>Quantity</th>
<th>Width</th>
<th>Height</th>
<th>Total</th>
<th>Action</th>
</thead>
<?php
$sql2 = "SELECT * from invoice_order where invoice_number = '$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2->num_rows > 0) {
// output data of each row
$srno = 1;
$count = $query2->num_rows;
for ($i=0; $i < $count; $i++) {
while($row = $query2->fetch_assoc()) {
?>
<tbody id="product_table">
<tr>
<td><input type="text" name="product" value="<?php echo $row["product"]; ?>"></td>
<td><input type="text" name="price" value="<?php echo $row["price"]; ?>"></td>
<td><input type="text" name="quantity" value="<?php echo $row["quantity"]; ?>"></td>
<td><input type="text" name="width" value="<?php echo $row["width"]; ?>"></td>
<td><input type="text" name="height" value="<?php echo $row["height"]; ?>"></td>
<td><input type="text" name="total" value="<?php echo $row["total"]; ?>" class="totalPrice" readonly></td>
<td><input type="button" value="X" onclick="deleteRow(this)"/></td>
</tr>
</tbody>
<?php
}
}
} else {
echo "No Record Found";
}
?>
<input type="button" name="submit" value="Add Row" onclick="add_fields();">
<span>Invoice Date:<input type="date" value="<?php echo $fetch["invoice_date"]; ?>" name="invoice_date"></span>
<span>Invoice #:<input type="text" name="invoice_number" value="<?php echo $fetch["invoice_number"]; ?>" readonly></span>
<span>Select Customer:
<select name="to_user" class="form-control">
<option><?php echo $fetch["customer_id"]; ?></option>
<?php
include('database.php');
$sql = mysqli_query($connect, "SELECT * From customer");
$row = mysqli_num_rows($sql);
while ($row = mysqli_fetch_array($sql)){
echo "<option value='". $row['customer_id'] ."'>" .$row['customer_id'] ." - " .$row['customer_name'] ."</option>" ;
}
?>
</select>
</span>
</table>
<span>Grand Total<input type="text" name="grandtotal" id="grandtotal" value="<?php echo $fetch["grandtotal"]; ?>" readonly></span><br><br>
<span>Paid Amount<input type="text" name="paid" id="paid" value="<?php echo $fetch["paid"]; ?>"></span><br><br>
<span>Balance<input type="text" name="balance" id="balance" value="<?php echo $fetch["balance"]; ?>" readonly></span><br><br>
<input type="submit" name="send" value="Submit">
</form>
</body>
<script>
const table = document.getElementById('product_table');
table.addEventListener('input', ({ target }) => {
const tr = target.closest('tr');
const [product, price, quantity, width, height, total] = tr.querySelectorAll('input');
var size = width.value * height.value;
var rate = price.value * quantity.value;
if (size != "") {
total.value = size * rate;
}else{
total.value = rate;
}
totalPrice();
});
function add_fields() {
var row = document.createElement("tr");
row.innerHTML =
'<td><input type="text" name="product"></td>' +
'<td><input type="text" name="price"></td>' +
'<td><input type="text" name="quantity"></td>' +
'<td><input type="text" name="width" value="0"></td>' +
'<td><input type="text" name="height" value="0"></td>' +
'<td><input type="text" name="total" class="totalPrice" readonly></td>' +
'<td><input type="button" value="X" onclick="deleteRow(this)"/></td>';
table.appendChild(row);
}
function deleteRow(btn) {
var row = btn.parentNode.parentNode;
row.parentNode.removeChild(row);
totalPrice();
}
function totalPrice() {
var grandtotal = 0;
var paid = 0;
$(".totalPrice").each(function() {
grandtotal += parseFloat($(this).val());
paid = grandtotal;
});
$("#grandtotal").val(grandtotal);
$("#paid").val(paid);
}
$(document).ready(function() {
$('#paid').on('input', function() {
grandtotal = $("#grandtotal").val();
paid = $("#paid").val();
balance = parseFloat(grandtotal) - parseFloat(paid);
$("#balance").val(balance);
})
});
</script>
</html>
Update_invoice.php
<?php
include('database.php');
if (isset($_POST['send'])) {
$product = $_POST['product'];
$price = $_POST['price'];
$quantity = $_POST['quantity'];
$width = $_POST['width'];
$height = $_POST['height'];
$total = $_POST['total'];
$customer_id = $_POST['to_user'];
$invoice_date = $_POST['invoice_date'];
$invoice_number = $_POST['invoice_number'];
$grandtotal = $_POST['grandtotal'];
$paid = $_POST['paid'];
$balance = $_POST['balance'];
$amount_status = "";
if ($grandtotal == $paid) {
$amount_status = "Paid";
} elseif ($grandtotal == $balance) {
$amount_status = "Due";
} else {
$amount_status = "Partial";
}
// Start of Updating data to invoice_order table
for ($i = 0; $i < count($_POST['total']); $i++) {
if ($i <> count($_POST['total'])) {
$sql = "UPDATE invoice_order SET invoice_number = '$invoice_number' , product = '$_POST['product'][$i]', price = '$_POST['price'][$i]' , quantity = '$_POST['quantity'][$i]', width = '$_POST['width'][$i]' , height = '$_POST['height'][$i]' , total = '$_POST['total'][$i]' WHERE invoice_number='$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query) {
header('location: list_invoice.php');
} else {
echo "Unable to enter records in invoice_order table";
}
}
}
// End of updating data to invoice_order table
// Start of updating data to invoice table
$sql2 = "UPDATE invoice SET customer_id = '$customer_id', grandtotal = '$grandtotal', invoice_number = '$invoice_number', invoice_date = '$invoice_date', paid = '$paid', balance = '$balance', amount_status = '$amount_status' WHERE invoice_number='$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2) {
header('location: list_invoice.php');
} else {
echo "Unable to enter record in invoice table";
}
// End of updating data to invoice table
}
?>
php mysql
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
add a comment |
0
I am making an invoice in PHP where multiple products are inserted at once into one table and there grand total goes to another table. I am trying to UPDATE the invoice in MYSQL and PHP. When I press the submit button, the multiple records data from the form goes to the update.php but the query does not run.
database.php
<?php
$connect = mysqli_connect('localhost','root','','invoice');
if (!$connect){
die("Connection failed: " . mysqli_connect_error());
}
?>
edit_invoice.php
<!DOCTYPE html>
<html>
<head>
<title></title>
<style>
table,tr,td,th { border: 1px black solid;}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<body>
<a href="list_invoice.php">Back to Invoice List</a><br><br>
<?php
include('database.php');
$invoice_number = $_GET['invoice_number'];
$sql = "SELECT * from invoice where invoice_number = '$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query->num_rows > 0) {
// output data of each row
$fetch = $query->fetch_assoc();
}
?>
<form method="POST" action="update_invoice.php">
<table>
<thead>
<th>Product</th>
<th>Price</th>
<th>Quantity</th>
<th>Width</th>
<th>Height</th>
<th>Total</th>
<th>Action</th>
</thead>
<?php
$sql2 = "SELECT * from invoice_order where invoice_number = '$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2->num_rows > 0) {
// output data of each row
$srno = 1;
$count = $query2->num_rows;
for ($i=0; $i < $count; $i++) {
while($row = $query2->fetch_assoc()) {
?>
<tbody id="product_table">
<tr>
<td><input type="text" name="product" value="<?php echo $row["product"]; ?>"></td>
<td><input type="text" name="price" value="<?php echo $row["price"]; ?>"></td>
<td><input type="text" name="quantity" value="<?php echo $row["quantity"]; ?>"></td>
<td><input type="text" name="width" value="<?php echo $row["width"]; ?>"></td>
<td><input type="text" name="height" value="<?php echo $row["height"]; ?>"></td>
<td><input type="text" name="total" value="<?php echo $row["total"]; ?>" class="totalPrice" readonly></td>
<td><input type="button" value="X" onclick="deleteRow(this)"/></td>
</tr>
</tbody>
<?php
}
}
} else {
echo "No Record Found";
}
?>
<input type="button" name="submit" value="Add Row" onclick="add_fields();">
<span>Invoice Date:<input type="date" value="<?php echo $fetch["invoice_date"]; ?>" name="invoice_date"></span>
<span>Invoice #:<input type="text" name="invoice_number" value="<?php echo $fetch["invoice_number"]; ?>" readonly></span>
<span>Select Customer:
<select name="to_user" class="form-control">
<option><?php echo $fetch["customer_id"]; ?></option>
<?php
include('database.php');
$sql = mysqli_query($connect, "SELECT * From customer");
$row = mysqli_num_rows($sql);
while ($row = mysqli_fetch_array($sql)){
echo "<option value='". $row['customer_id'] ."'>" .$row['customer_id'] ." - " .$row['customer_name'] ."</option>" ;
}
?>
</select>
</span>
</table>
<span>Grand Total<input type="text" name="grandtotal" id="grandtotal" value="<?php echo $fetch["grandtotal"]; ?>" readonly></span><br><br>
<span>Paid Amount<input type="text" name="paid" id="paid" value="<?php echo $fetch["paid"]; ?>"></span><br><br>
<span>Balance<input type="text" name="balance" id="balance" value="<?php echo $fetch["balance"]; ?>" readonly></span><br><br>
<input type="submit" name="send" value="Submit">
</form>
</body>
<script>
const table = document.getElementById('product_table');
table.addEventListener('input', ({ target }) => {
const tr = target.closest('tr');
const [product, price, quantity, width, height, total] = tr.querySelectorAll('input');
var size = width.value * height.value;
var rate = price.value * quantity.value;
if (size != "") {
total.value = size * rate;
}else{
total.value = rate;
}
totalPrice();
});
function add_fields() {
var row = document.createElement("tr");
row.innerHTML =
'<td><input type="text" name="product"></td>' +
'<td><input type="text" name="price"></td>' +
'<td><input type="text" name="quantity"></td>' +
'<td><input type="text" name="width" value="0"></td>' +
'<td><input type="text" name="height" value="0"></td>' +
'<td><input type="text" name="total" class="totalPrice" readonly></td>' +
'<td><input type="button" value="X" onclick="deleteRow(this)"/></td>';
table.appendChild(row);
}
function deleteRow(btn) {
var row = btn.parentNode.parentNode;
row.parentNode.removeChild(row);
totalPrice();
}
function totalPrice() {
var grandtotal = 0;
var paid = 0;
$(".totalPrice").each(function() {
grandtotal += parseFloat($(this).val());
paid = grandtotal;
});
$("#grandtotal").val(grandtotal);
$("#paid").val(paid);
}
$(document).ready(function() {
$('#paid').on('input', function() {
grandtotal = $("#grandtotal").val();
paid = $("#paid").val();
balance = parseFloat(grandtotal) - parseFloat(paid);
$("#balance").val(balance);
})
});
</script>
</html>
Update_invoice.php
<?php
include('database.php');
if (isset($_POST['send'])) {
$product = $_POST['product'];
$price = $_POST['price'];
$quantity = $_POST['quantity'];
$width = $_POST['width'];
$height = $_POST['height'];
$total = $_POST['total'];
$customer_id = $_POST['to_user'];
$invoice_date = $_POST['invoice_date'];
$invoice_number = $_POST['invoice_number'];
$grandtotal = $_POST['grandtotal'];
$paid = $_POST['paid'];
$balance = $_POST['balance'];
$amount_status = "";
if ($grandtotal == $paid) {
$amount_status = "Paid";
} elseif ($grandtotal == $balance) {
$amount_status = "Due";
} else {
$amount_status = "Partial";
}
// Start of Updating data to invoice_order table
for ($i = 0; $i < count($_POST['total']); $i++) {
if ($i <> count($_POST['total'])) {
$sql = "UPDATE invoice_order SET invoice_number = '$invoice_number' , product = '$_POST['product'][$i]', price = '$_POST['price'][$i]' , quantity = '$_POST['quantity'][$i]', width = '$_POST['width'][$i]' , height = '$_POST['height'][$i]' , total = '$_POST['total'][$i]' WHERE invoice_number='$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query) {
header('location: list_invoice.php');
} else {
echo "Unable to enter records in invoice_order table";
}
}
}
// End of updating data to invoice_order table
// Start of updating data to invoice table
$sql2 = "UPDATE invoice SET customer_id = '$customer_id', grandtotal = '$grandtotal', invoice_number = '$invoice_number', invoice_date = '$invoice_date', paid = '$paid', balance = '$balance', amount_status = '$amount_status' WHERE invoice_number='$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2) {
header('location: list_invoice.php');
} else {
echo "Unable to enter record in invoice table";
}
// End of updating data to invoice table
}
?>
php mysql
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
according to the comment// Start of Adding data to invoice_order tableI'd expect the statement to be aninsert into ...rather than andupdate...- that aside I think when it comes to using variables ( such as$_POST['price'][$i]) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like{$_POST['price'][$i]}etc ~ you would be better usingprepared statements
– RamRaider
Nov 14 '18 at 19:26
WARNING: When usingmysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put$_POST,$_GETor any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.
– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface tomysqliis significantly less verbose, making code easier to read and audit, and is not easily confused with the obsoletemysql_queryinterface. Before you get too invested in the procedural style it’s worth switching over. Example:$db = new mysqli(…)and$db->prepare("…")The procedural interface is an artifact from the PHP 4 era whenmysqliAPI was introduced and should not be used in new code.
– tadman
Nov 14 '18 at 20:50
add a comment |
0
0
0
1
I am making an invoice in PHP where multiple products are inserted at once into one table and there grand total goes to another table. I am trying to UPDATE the invoice in MYSQL and PHP. When I press the submit button, the multiple records data from the form goes to the update.php but the query does not run.
database.php
<?php
$connect = mysqli_connect('localhost','root','','invoice');
if (!$connect){
die("Connection failed: " . mysqli_connect_error());
}
?>
edit_invoice.php
<!DOCTYPE html>
<html>
<head>
<title></title>
<style>
table,tr,td,th { border: 1px black solid;}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<body>
<a href="list_invoice.php">Back to Invoice List</a><br><br>
<?php
include('database.php');
$invoice_number = $_GET['invoice_number'];
$sql = "SELECT * from invoice where invoice_number = '$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query->num_rows > 0) {
// output data of each row
$fetch = $query->fetch_assoc();
}
?>
<form method="POST" action="update_invoice.php">
<table>
<thead>
<th>Product</th>
<th>Price</th>
<th>Quantity</th>
<th>Width</th>
<th>Height</th>
<th>Total</th>
<th>Action</th>
</thead>
<?php
$sql2 = "SELECT * from invoice_order where invoice_number = '$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2->num_rows > 0) {
// output data of each row
$srno = 1;
$count = $query2->num_rows;
for ($i=0; $i < $count; $i++) {
while($row = $query2->fetch_assoc()) {
?>
<tbody id="product_table">
<tr>
<td><input type="text" name="product" value="<?php echo $row["product"]; ?>"></td>
<td><input type="text" name="price" value="<?php echo $row["price"]; ?>"></td>
<td><input type="text" name="quantity" value="<?php echo $row["quantity"]; ?>"></td>
<td><input type="text" name="width" value="<?php echo $row["width"]; ?>"></td>
<td><input type="text" name="height" value="<?php echo $row["height"]; ?>"></td>
<td><input type="text" name="total" value="<?php echo $row["total"]; ?>" class="totalPrice" readonly></td>
<td><input type="button" value="X" onclick="deleteRow(this)"/></td>
</tr>
</tbody>
<?php
}
}
} else {
echo "No Record Found";
}
?>
<input type="button" name="submit" value="Add Row" onclick="add_fields();">
<span>Invoice Date:<input type="date" value="<?php echo $fetch["invoice_date"]; ?>" name="invoice_date"></span>
<span>Invoice #:<input type="text" name="invoice_number" value="<?php echo $fetch["invoice_number"]; ?>" readonly></span>
<span>Select Customer:
<select name="to_user" class="form-control">
<option><?php echo $fetch["customer_id"]; ?></option>
<?php
include('database.php');
$sql = mysqli_query($connect, "SELECT * From customer");
$row = mysqli_num_rows($sql);
while ($row = mysqli_fetch_array($sql)){
echo "<option value='". $row['customer_id'] ."'>" .$row['customer_id'] ." - " .$row['customer_name'] ."</option>" ;
}
?>
</select>
</span>
</table>
<span>Grand Total<input type="text" name="grandtotal" id="grandtotal" value="<?php echo $fetch["grandtotal"]; ?>" readonly></span><br><br>
<span>Paid Amount<input type="text" name="paid" id="paid" value="<?php echo $fetch["paid"]; ?>"></span><br><br>
<span>Balance<input type="text" name="balance" id="balance" value="<?php echo $fetch["balance"]; ?>" readonly></span><br><br>
<input type="submit" name="send" value="Submit">
</form>
</body>
<script>
const table = document.getElementById('product_table');
table.addEventListener('input', ({ target }) => {
const tr = target.closest('tr');
const [product, price, quantity, width, height, total] = tr.querySelectorAll('input');
var size = width.value * height.value;
var rate = price.value * quantity.value;
if (size != "") {
total.value = size * rate;
}else{
total.value = rate;
}
totalPrice();
});
function add_fields() {
var row = document.createElement("tr");
row.innerHTML =
'<td><input type="text" name="product"></td>' +
'<td><input type="text" name="price"></td>' +
'<td><input type="text" name="quantity"></td>' +
'<td><input type="text" name="width" value="0"></td>' +
'<td><input type="text" name="height" value="0"></td>' +
'<td><input type="text" name="total" class="totalPrice" readonly></td>' +
'<td><input type="button" value="X" onclick="deleteRow(this)"/></td>';
table.appendChild(row);
}
function deleteRow(btn) {
var row = btn.parentNode.parentNode;
row.parentNode.removeChild(row);
totalPrice();
}
function totalPrice() {
var grandtotal = 0;
var paid = 0;
$(".totalPrice").each(function() {
grandtotal += parseFloat($(this).val());
paid = grandtotal;
});
$("#grandtotal").val(grandtotal);
$("#paid").val(paid);
}
$(document).ready(function() {
$('#paid').on('input', function() {
grandtotal = $("#grandtotal").val();
paid = $("#paid").val();
balance = parseFloat(grandtotal) - parseFloat(paid);
$("#balance").val(balance);
})
});
</script>
</html>
Update_invoice.php
<?php
include('database.php');
if (isset($_POST['send'])) {
$product = $_POST['product'];
$price = $_POST['price'];
$quantity = $_POST['quantity'];
$width = $_POST['width'];
$height = $_POST['height'];
$total = $_POST['total'];
$customer_id = $_POST['to_user'];
$invoice_date = $_POST['invoice_date'];
$invoice_number = $_POST['invoice_number'];
$grandtotal = $_POST['grandtotal'];
$paid = $_POST['paid'];
$balance = $_POST['balance'];
$amount_status = "";
if ($grandtotal == $paid) {
$amount_status = "Paid";
} elseif ($grandtotal == $balance) {
$amount_status = "Due";
} else {
$amount_status = "Partial";
}
// Start of Updating data to invoice_order table
for ($i = 0; $i < count($_POST['total']); $i++) {
if ($i <> count($_POST['total'])) {
$sql = "UPDATE invoice_order SET invoice_number = '$invoice_number' , product = '$_POST['product'][$i]', price = '$_POST['price'][$i]' , quantity = '$_POST['quantity'][$i]', width = '$_POST['width'][$i]' , height = '$_POST['height'][$i]' , total = '$_POST['total'][$i]' WHERE invoice_number='$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query) {
header('location: list_invoice.php');
} else {
echo "Unable to enter records in invoice_order table";
}
}
}
// End of updating data to invoice_order table
// Start of updating data to invoice table
$sql2 = "UPDATE invoice SET customer_id = '$customer_id', grandtotal = '$grandtotal', invoice_number = '$invoice_number', invoice_date = '$invoice_date', paid = '$paid', balance = '$balance', amount_status = '$amount_status' WHERE invoice_number='$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2) {
header('location: list_invoice.php');
} else {
echo "Unable to enter record in invoice table";
}
// End of updating data to invoice table
}
?>
php mysql
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
I am making an invoice in PHP where multiple products are inserted at once into one table and there grand total goes to another table. I am trying to UPDATE the invoice in MYSQL and PHP. When I press the submit button, the multiple records data from the form goes to the update.php but the query does not run.
database.php
<?php
$connect = mysqli_connect('localhost','root','','invoice');
if (!$connect){
die("Connection failed: " . mysqli_connect_error());
}
?>
edit_invoice.php
<!DOCTYPE html>
<html>
<head>
<title></title>
<style>
table,tr,td,th { border: 1px black solid;}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
</head>
<body>
<a href="list_invoice.php">Back to Invoice List</a><br><br>
<?php
include('database.php');
$invoice_number = $_GET['invoice_number'];
$sql = "SELECT * from invoice where invoice_number = '$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query->num_rows > 0) {
// output data of each row
$fetch = $query->fetch_assoc();
}
?>
<form method="POST" action="update_invoice.php">
<table>
<thead>
<th>Product</th>
<th>Price</th>
<th>Quantity</th>
<th>Width</th>
<th>Height</th>
<th>Total</th>
<th>Action</th>
</thead>
<?php
$sql2 = "SELECT * from invoice_order where invoice_number = '$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2->num_rows > 0) {
// output data of each row
$srno = 1;
$count = $query2->num_rows;
for ($i=0; $i < $count; $i++) {
while($row = $query2->fetch_assoc()) {
?>
<tbody id="product_table">
<tr>
<td><input type="text" name="product" value="<?php echo $row["product"]; ?>"></td>
<td><input type="text" name="price" value="<?php echo $row["price"]; ?>"></td>
<td><input type="text" name="quantity" value="<?php echo $row["quantity"]; ?>"></td>
<td><input type="text" name="width" value="<?php echo $row["width"]; ?>"></td>
<td><input type="text" name="height" value="<?php echo $row["height"]; ?>"></td>
<td><input type="text" name="total" value="<?php echo $row["total"]; ?>" class="totalPrice" readonly></td>
<td><input type="button" value="X" onclick="deleteRow(this)"/></td>
</tr>
</tbody>
<?php
}
}
} else {
echo "No Record Found";
}
?>
<input type="button" name="submit" value="Add Row" onclick="add_fields();">
<span>Invoice Date:<input type="date" value="<?php echo $fetch["invoice_date"]; ?>" name="invoice_date"></span>
<span>Invoice #:<input type="text" name="invoice_number" value="<?php echo $fetch["invoice_number"]; ?>" readonly></span>
<span>Select Customer:
<select name="to_user" class="form-control">
<option><?php echo $fetch["customer_id"]; ?></option>
<?php
include('database.php');
$sql = mysqli_query($connect, "SELECT * From customer");
$row = mysqli_num_rows($sql);
while ($row = mysqli_fetch_array($sql)){
echo "<option value='". $row['customer_id'] ."'>" .$row['customer_id'] ." - " .$row['customer_name'] ."</option>" ;
}
?>
</select>
</span>
</table>
<span>Grand Total<input type="text" name="grandtotal" id="grandtotal" value="<?php echo $fetch["grandtotal"]; ?>" readonly></span><br><br>
<span>Paid Amount<input type="text" name="paid" id="paid" value="<?php echo $fetch["paid"]; ?>"></span><br><br>
<span>Balance<input type="text" name="balance" id="balance" value="<?php echo $fetch["balance"]; ?>" readonly></span><br><br>
<input type="submit" name="send" value="Submit">
</form>
</body>
<script>
const table = document.getElementById('product_table');
table.addEventListener('input', ({ target }) => {
const tr = target.closest('tr');
const [product, price, quantity, width, height, total] = tr.querySelectorAll('input');
var size = width.value * height.value;
var rate = price.value * quantity.value;
if (size != "") {
total.value = size * rate;
}else{
total.value = rate;
}
totalPrice();
});
function add_fields() {
var row = document.createElement("tr");
row.innerHTML =
'<td><input type="text" name="product"></td>' +
'<td><input type="text" name="price"></td>' +
'<td><input type="text" name="quantity"></td>' +
'<td><input type="text" name="width" value="0"></td>' +
'<td><input type="text" name="height" value="0"></td>' +
'<td><input type="text" name="total" class="totalPrice" readonly></td>' +
'<td><input type="button" value="X" onclick="deleteRow(this)"/></td>';
table.appendChild(row);
}
function deleteRow(btn) {
var row = btn.parentNode.parentNode;
row.parentNode.removeChild(row);
totalPrice();
}
function totalPrice() {
var grandtotal = 0;
var paid = 0;
$(".totalPrice").each(function() {
grandtotal += parseFloat($(this).val());
paid = grandtotal;
});
$("#grandtotal").val(grandtotal);
$("#paid").val(paid);
}
$(document).ready(function() {
$('#paid').on('input', function() {
grandtotal = $("#grandtotal").val();
paid = $("#paid").val();
balance = parseFloat(grandtotal) - parseFloat(paid);
$("#balance").val(balance);
})
});
</script>
</html>
Update_invoice.php
<?php
include('database.php');
if (isset($_POST['send'])) {
$product = $_POST['product'];
$price = $_POST['price'];
$quantity = $_POST['quantity'];
$width = $_POST['width'];
$height = $_POST['height'];
$total = $_POST['total'];
$customer_id = $_POST['to_user'];
$invoice_date = $_POST['invoice_date'];
$invoice_number = $_POST['invoice_number'];
$grandtotal = $_POST['grandtotal'];
$paid = $_POST['paid'];
$balance = $_POST['balance'];
$amount_status = "";
if ($grandtotal == $paid) {
$amount_status = "Paid";
} elseif ($grandtotal == $balance) {
$amount_status = "Due";
} else {
$amount_status = "Partial";
}
// Start of Updating data to invoice_order table
for ($i = 0; $i < count($_POST['total']); $i++) {
if ($i <> count($_POST['total'])) {
$sql = "UPDATE invoice_order SET invoice_number = '$invoice_number' , product = '$_POST['product'][$i]', price = '$_POST['price'][$i]' , quantity = '$_POST['quantity'][$i]', width = '$_POST['width'][$i]' , height = '$_POST['height'][$i]' , total = '$_POST['total'][$i]' WHERE invoice_number='$invoice_number' ";
$query = mysqli_query($connect, $sql);
if ($query) {
header('location: list_invoice.php');
} else {
echo "Unable to enter records in invoice_order table";
}
}
}
// End of updating data to invoice_order table
// Start of updating data to invoice table
$sql2 = "UPDATE invoice SET customer_id = '$customer_id', grandtotal = '$grandtotal', invoice_number = '$invoice_number', invoice_date = '$invoice_date', paid = '$paid', balance = '$balance', amount_status = '$amount_status' WHERE invoice_number='$invoice_number' ";
$query2 = mysqli_query($connect, $sql2);
if ($query2) {
header('location: list_invoice.php');
} else {
echo "Unable to enter record in invoice table";
}
// End of updating data to invoice table
}
?>
php mysql
php mysql
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
edited Nov 15 '18 at 12:21
Zubair Ahmad
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
asked Nov 14 '18 at 19:11
Zubair AhmadZubair Ahmad
277
277
according to the comment// Start of Adding data to invoice_order tableI'd expect the statement to be aninsert into ...rather than andupdate...- that aside I think when it comes to using variables ( such as$_POST['price'][$i]) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like{$_POST['price'][$i]}etc ~ you would be better usingprepared statements
– RamRaider
Nov 14 '18 at 19:26
WARNING: When usingmysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put$_POST,$_GETor any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.
– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface tomysqliis significantly less verbose, making code easier to read and audit, and is not easily confused with the obsoletemysql_queryinterface. Before you get too invested in the procedural style it’s worth switching over. Example:$db = new mysqli(…)and$db->prepare("…")The procedural interface is an artifact from the PHP 4 era whenmysqliAPI was introduced and should not be used in new code.
– tadman
Nov 14 '18 at 20:50
add a comment |
according to the comment// Start of Adding data to invoice_order tableI'd expect the statement to be aninsert into ...rather than andupdate...- that aside I think when it comes to using variables ( such as$_POST['price'][$i]) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like{$_POST['price'][$i]}etc ~ you would be better usingprepared statements
– RamRaider
Nov 14 '18 at 19:26
WARNING: When usingmysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put$_POST,$_GETor any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.
– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface tomysqliis significantly less verbose, making code easier to read and audit, and is not easily confused with the obsoletemysql_queryinterface. Before you get too invested in the procedural style it’s worth switching over. Example:$db = new mysqli(…)and$db->prepare("…")The procedural interface is an artifact from the PHP 4 era whenmysqliAPI was introduced and should not be used in new code.
– tadman
Nov 14 '18 at 20:50
according to the comment
// Start of Adding data to invoice_order table I'd expect the statement to be an insert into ... rather than and update... - that aside I think when it comes to using variables ( such as $_POST['price'][$i] ) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like {$_POST['price'][$i]} etc ~ you would be better using prepared statements– RamRaider
Nov 14 '18 at 19:26
according to the comment
// Start of Adding data to invoice_order table I'd expect the statement to be an insert into ... rather than and update... - that aside I think when it comes to using variables ( such as $_POST['price'][$i] ) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like {$_POST['price'][$i]} etc ~ you would be better using prepared statements– RamRaider
Nov 14 '18 at 19:26
WARNING: When using
mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.– tadman
Nov 14 '18 at 20:50
WARNING: When using
mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface to
mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code.– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface to
mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code.– tadman
Nov 14 '18 at 20:50
add a comment |
1 Answer
1
active
oldest
votes
0
The approach you are using is not the preferred way of doing the job. Use mysql prepare statement. This will make the code clean, easy to read and secured. Here is the link you can refer Mysql Prepared Statement in PHP
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307225%2fupdate-multiple-record-with-single-query-in-database-using-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
The approach you are using is not the preferred way of doing the job. Use mysql prepare statement. This will make the code clean, easy to read and secured. Here is the link you can refer Mysql Prepared Statement in PHP
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
add a comment |
0
The approach you are using is not the preferred way of doing the job. Use mysql prepare statement. This will make the code clean, easy to read and secured. Here is the link you can refer Mysql Prepared Statement in PHP
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
add a comment |
0
0
0
The approach you are using is not the preferred way of doing the job. Use mysql prepare statement. This will make the code clean, easy to read and secured. Here is the link you can refer Mysql Prepared Statement in PHP
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
The approach you are using is not the preferred way of doing the job. Use mysql prepare statement. This will make the code clean, easy to read and secured. Here is the link you can refer Mysql Prepared Statement in PHP
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
answered Nov 14 '18 at 20:37
Azharuddin LaskarAzharuddin Laskar
122
122
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
add a comment |
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
I am still under confusion. Can you please give me a proper code solution?
– Zubair Ahmad
Nov 15 '18 at 5:09
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
@ZubairAhmad please attach the sql and the database.php content, so i can rewrite the code.
– Azharuddin Laskar
Nov 15 '18 at 10:26
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
I have edited the question and write all the code.
– Zubair Ahmad
Nov 15 '18 at 12:22
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307225%2fupdate-multiple-record-with-single-query-in-database-using-php%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
according to the comment
// Start of Adding data to invoice_order tableI'd expect the statement to be aninsert into ...rather than andupdate...- that aside I think when it comes to using variables ( such as$_POST['price'][$i]) within a quoted SQL statement ( incidentally making it vulnerable to SQL injection ), you need to use curly braces around the variables... like{$_POST['price'][$i]}etc ~ you would be better usingprepared statements– RamRaider
Nov 14 '18 at 19:26
WARNING: When using
mysqliyou should be using parameterized queries andbind_paramto add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put$_POST,$_GETor any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake.– tadman
Nov 14 '18 at 20:50
Note: The object-oriented interface to
mysqliis significantly less verbose, making code easier to read and audit, and is not easily confused with the obsoletemysql_queryinterface. Before you get too invested in the procedural style it’s worth switching over. Example:$db = new mysqli(…)and$db->prepare("…")The procedural interface is an artifact from the PHP 4 era whenmysqliAPI was introduced and should not be used in new code.– tadman
Nov 14 '18 at 20:50