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







share|improve this question

























  • 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











  • 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
















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







share|improve this question

























  • 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











  • 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














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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 12:21







Zubair Ahmad

















asked Nov 14 '18 at 19:11









Zubair AhmadZubair Ahmad

277




277













  • 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











  • 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



















  • 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











  • 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

















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












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






share|improve this answer
























  • 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













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


















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






share|improve this answer
























  • 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


















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






share|improve this answer
























  • 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
















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






share|improve this answer













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







share|improve this answer












share|improve this answer



share|improve this answer










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





















  • 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






















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














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





















































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







-

Popular posts from this blog

Florida Star v. B. J. F.

Image
Florida Star v. B. J. F. From Wikipedia, the free encyclopedia Jump to navigation Jump to search United States Supreme Court case Florida Star v. B. J. F. Supreme Court of the United States Argued March 21, 1989 Decided June 21, 1989 Full case name The Florida Star v. B. J. F. Citations 491 U.S. 524 ( more ) 109 S. Ct. 2603; 105 L. Ed. 2d 443; 1989 U.S. LEXIS 3120; 57 U.S.L.W. 4816; 16 Media L. Rep. 1801 Prior history The Florida Star v. B.J.F., 530 So.2d 286 (1988) Supreme Court of Florida; Florida Star v. B.J.F., 499 So.2d 883 (1986) Fla. Dist. Court of Appeals Holding Florida Stat. § 794.03 is unconstitutional to the extent it makes the truthful reporting of information that was a matter of public record unlawful, as it violates the First Amendment. Court membership Chief Justice William Rehnquist Associate Justices William J. Brennan Jr.  · Byron White Thurgood Marshall  · Harry Blac
Read more

Error while running script in elastic search , gateway timeout

Image
0 while running script in elastic search, I got 504 Gateway timeout error. { "query": { "bool": { "filter": { "script": { "script": " doc['creted_date'].date.getMonthOfYear() == 12 " } } } }, "aggs": { "test": { "date_histogram": { "field": "creted_date", "interval": "month", "format": "MMM" }, "aggs": { "cost": { "sum": { "field": "cost" } } }
Read more

Adding quotations to stringified JSON object values

Image
0 My stringified object data doesn't include quotation marks around object values, which errors when attempting JSON.parse() : '{ "affiliation": CORPORATE, "userId": 75c35d1c-5d12-4485-8fa8-b2f1551a3e6e }' I need the string to be: '{ "affiliation": "CORPORATE", "userId": "75c35d1c-5d12-4485-8fa8-b2f1551a3e6e" }' I'm using this regex to add quotes to the object keys: var newStr = str.replace(/(['"])?([a-z0-9A-Z_]+)(['"])?:/g, '"$2": '); For instance: '{ affiliation: CORPORATE }' to '{ "affiliation": CORPORATE }' There are only string values in my data, so I don't need to check value types. How can I alter my regex expression to add quotations to
Read more