使用php在数据库中使用单个查询更新多个记录

我正在用php制作一个发票，其中多个产品一次插入到一个表中，然后总计进入另一个表。我正在尝试用mysql和php更新invoice。当我按下submit按钮时，表单中的多条记录数据会转到update.php，但查询不会运行。
数据库.php

<?php

$connect = mysqli_connect('localhost','root','','invoice');
if (!$connect){
    die("Connection failed: " . mysqli_connect_error());
}
?>

编辑\u 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>

更新\u 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在数据库中使用单个查询更新多个记录

1条答案

相关问题

热门标签

最新问答