Skip to content
Advertisement

How can i insert these multiple row data into mysql database

This is my html ajax and js code, it’s dynamic input field form i want to insert multiple data into mysql database following these column “pro” “des” “qty” “price” “sub_total” . “qty” and “price” used auto sum value to “sub_total” input field

please see my full html code then Please write a php code for me . Last time i was failed many time to do it.

so please help me

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="description" content="invoice system, php invoice script, invoice script, pro invoice maker, free php invoice/billing script, php Invoice software, php invoice generator script, invoice script open source, invoice generator php script, invoice script in php, javascript invoice script, invoice maker php script, php invoice script tutorial">
    <meta name="keywords" content="invoice system, php invoice script, invoice script, pro invoice maker, free php invoice/billing script, php Invoice software, php invoice generator script, invoice script open source, invoice generator php script, invoice script in php, javascript invoice script, invoice maker php script, php invoice script tutorial">
    <meta name="author" content="http://tanbhirhossain.me">
    <title>AFOJAL MINI MARKET</title>
    <!-- Bootstrap -->
    <link href='http://fonts.googleapis.com/css?family=Source+Sans+Pro:400,300,200' rel='stylesheet' type='text/css'>
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="css/font-awesome.min.css">
    <link href="css/admin.css" rel="stylesheet">

    <!-- Script -->
    <script src="js/jquery.min.js"></script>
    <script src="js/wayfinder.js" ></script>
    <!-- Include all compiled plugins (below), or include individual files as needed -->

    <script>
        $(document).ready(function() {
            jQuery('.load-animate').waypoint({
                triggerOnce: true,
                offset: '80%',
                handler: function() {
                    jQuery(this).addClass('animated fadeInUp');
                }
            });
        });
    </script>
  </head>
 <body>
    <!-- Static navbar -->
    <div role="navigation" class="navbar navbar-inverse navbar-fixed-top">
        <div class="container-fluid">
            <div class="navbar-header">
                <button type="button" data-toggle="collapse"
                    data-target=".navbar-collapse" class="navbar-toggle collapsed">
                    <span class="sr-only">Toggle navigation</span> <span
                        class="icon-bar"></span> <span class="icon-bar"></span> <span
                        class="icon-bar"></span>
                </button>
                <a class="navbar-brand" href="#">Afojal Mini Market Invoice System</a>
            </div>
                        <div class="collapse navbar-collapse">
                                <ul class="nav navbar-nav navbar-right">


                    <li class="dropdown">
                        <a href="#" data-toggle="dropdown" class="dropdown-toggle">
                            Hello
                            muni                            <span class="caret"></span>
                        </a>
                        <ul role="menu" class="dropdown-menu account-menu">
                            <li> <a href="account.php"> <i class="fa fa-user"></i> My Account</a> </li>
                            <li class="divider"></li>
                            <li style="background: #e67e22; color:#fff"> <a class="logout" href="logout.php"> <i class="fa fa-power-off"></i> Signout</a> </li>
                        </ul>
                    </li>
                </ul>
                            </div>
            <!--/.nav-collapse -->
        </div>
    </div>
<link href="http://code.jquery.com/ui/1.11.2/themes/smoothness/jquery-ui.min.css" rel="stylesheet">
<link href="css/datepicker.css" rel="stylesheet">


    <!-- Begin page content -->
    <div class="container content-invoice">
        <form action="invoice.php" id="invoice-form" method="post"  class="invoice-form" role="form" novalidate> 
            <div class="load-animate">
                <input type="hidden" value="" name="data[id]">

                <div class='row'>
                    <div class='col-xs-8 col-sm-8 col-md-8 col-lg-8'>
                        <h1 class="title">PHP Invoice System</h1>
                    </div>

                    <div class='col-xs-4 col-sm-4 col-md-4 col-lg-4'>
                        <input data-loading-text="Saving Invoice..." type="submit" name="invoice_btn" value="Save Invoice" class="btn btn-success submit_btn invoice-save-top form-control"/>
                    </div>
                </div>
                <input id="currency" type="hidden" value="RM">

                <div class='row'>
                    <div class='col-xs-12 col-sm-12 col-md-12 col-lg-12'>
                        <table class="table table-bordered table-hover">
                            <thead>
                                <tr>
                                    <th width="2%"><input id="check_all" class="formcontrol" type="checkbox"/></th>
                                    <th width="15%">Product</th>
                                    <th width="38%">Description</th>
                                    <th width="15%">Price</th>
                                    <th width="15%">Quantity</th>
                                    <th width="15%">Total</th>
                                </tr>
                            </thead>
                            <tbody>
                                                                    <tr>
                                        <td><input class="case" type="checkbox"/></td>
                                        <td><input type="text" data-type="productCode" name="data[Invoice][pro][]" id="ipro_1" class="form-control autocomplete_txt" autocomplete="off"></td>
                                        <td><input type="text" data-type="productName" name="data[Invoice][des][]" id="ides_1" class="form-control autocomplete_txt" autocomplete="off"></td>
                                        <td><input type="number" name="data[Invoice][price][]" id="price_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                                        <td><input type="number" name="data[Invoice][qty][]" id="quantity_1" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                                        <td><input type="number" name="data[Invoice][sub_total][]" id="total_1" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>
                                    </tr>
                                                            </tbody>
                        </table>
                    </div>
                </div>
                <div class='row'>
                    <div class='col-xs-12 col-sm-3 col-md-3 col-lg-3'>
                        <button class="btn btn-danger delete" type="button">- Delete</button>
                        <button class="btn btn-success addmore" type="button">+ Add More</button>
                    </div>
                </div>
                <div class='row'>   
                    <div class='col-xs-12 col-sm-8 col-md-8 col-lg-8'>
                        <h3>Notes: </h3>
                        <div class="form-group">
                            <textarea class="form-control txt" rows='5' name="data[notes]" id="notes" placeholder="Your Notes"></textarea>
                        </div>

                        <div class='form-group text-center'>
                            <input data-loading-text="Saving Invoice..." type="submit" name="invoice_btn" value="Save Invoice" class="btn btn-success submit_btn invoice-save-btm"/>
                        </div>

                    </div>
                    <div class='col-xs-12 col-sm-4 col-md-4 col-lg-4'>
                        <span class="form-inline">
                            <div class="form-group">
                                <label>Subtotal: &nbsp;</label>
                                <div class="input-group">
                                    <div class="input-group-addon currency">$</div>
                                    <input value="" type="number" class="form-control" name="data[subTotal]" id="subTotal" placeholder="Subtotal" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                                </div>
                            </div>
                            <div class="form-group">
                                <label>Tax: &nbsp;</label>
                                <div class="input-group">
                                    <div class="input-group-addon currency">$</div>
                                    <input value="" type="number" class="form-control" name="data[tax]" id="tax" placeholder="Tax" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                                </div>
                            </div>
                            <div class="form-group">
                                <label>Tax Amount: &nbsp;</label>
                                <div class="input-group">
                                    <input value="" type="number" class="form-control" name="data[taxAmount]" id="taxAmount" placeholder="Tax" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                                    <div class="input-group-addon">%</div>
                                </div>
                            </div>
                            <div class="form-group">
                                <label>Total: &nbsp;</label>
                                <div class="input-group">
                                    <div class="input-group-addon currency">$</div>
                                    <input value="" type="number" class="form-control" name="data[totalAftertax]" id="totalAftertax" placeholder="Total" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                                </div>
                            </div>

                        </span>
                    </div>
                </div>
                <div class="clearfix"></div>
                <div class='row'>   

                    <div class='col-xs-12 col-sm-6 col-md-6 col-lg-6 text-center'>

                    </div>
                </div>  

            </div>
        </form>         
    </div>
    <script src="js/jquery-ui.min.js"></script>
    <script src="js/ajax.js"></script>
    <script>
        $('.submit_btn').on('click', function(){
            $(this).button('loading');
        });

        $(document).ready(function(){
            $('.currency').html( $('#currency').val() );
        });

        $('#clientCompanyName').autocomplete({
            source: function( request, response ) {
                $.ajax({
                    url : 'ajax.php',
                    dataType: "json",
                    method: 'post',
                    data: {
                        name_startsWith: request.term,
                        type: 'customerName'
                    },
                    success: function( data ) {
                        response( $.map( data, function( item ) {
                            var code = item.split("|");
                                return {
                                    label: code[1],
                                    value: code[1],
                                    data : item
                                }
                            }));
                        }
                    });
            },
            autoFocus: true,            
            minLength: 1,
            select: function( event, ui ) {
                var names = ui.item.data.split("|");
                $(this).val(names[1]);
                getClientAddress(names[0]);
            }               
        });
        function getClientAddress(id){

             $.ajax({
                 url: "ajax.php",
                 method: 'post', 
                 data:{id:id, type:'clientAddress'},
                 success: function(result){
                    $("#clientAddress").html(result);
                }
            });
        }





    </script>

    <div class="clearfix"></div>

    <footer class="footer">
        <div class="container-fluid">
        <!--    <p class="text-center">&copy; Copyright by <a href="http://smarttutorials.net/" target="_blank">Smart Tutorials</a> </p> -->
        </div>
    </footer>



     <script src="js/bootstrap.min.js"></script>
    <script src="js/bootstrap-datepicker.js"></script>
  </body>
</html>

my php code was:

<?php
//insert.php
$connect = mysqli_connect("localhost", "root", "", "inv");
if(isset($_POST["pro"]))
{
 $item_name = $_POST["pro"];
 $item_code = $_POST["des"];
 $item_des = $_POST["qty"];
 $item_price = $_POST["price"];
 $subtotal = $_POST["sub_total"];
 $query = '';
 for($count = 0; $count<count($item_name); $count++)
 {
  $item_name_clean = mysqli_real_escape_string($connect, $item_name[$count]);
  $item_code_clean = mysqli_real_escape_string($connect, $item_code[$count]);
  $item_des_clean = mysqli_real_escape_string($connect, $item_des[$count]);
  $item_price_clean = mysqli_real_escape_string($connect, $item_price[$count]);
  $subtotal_clean = mysqli_real_escape_string($connect, $subtotal[$count]);
  if($item_name_clean != '' && $item_code_clean != '' && $item_des_clean != '' && $item_price_clean != '')
  {
   $query .= '
   INSERT INTO sell(pro, des, qty, price, sub_total) 
   VALUES("'.$item_name_clean.'", "'.$item_code_clean.'", "'.$item_des_clean.'", "'.$item_price_clean.'",  "'.$subtotal_clean.'"); 
   ';
  }
 }
 if($query != '')
 {
  if(mysqli_multi_query($connect, $query))
  {
   echo 'Item Data Inserted';
  }
  else
  {
   echo 'Error';
  }
 }

 else
 {
  echo 'All Fields are Required';
 }
}
?>

Advertisement

Answer

You will get your post data in $_POST['data']['Invoice'] array so change your script like :

//insert.php
$connect = mysqli_connect("localhost", "root", "", "inv");
if(isset($_POST['data']['Invoice']['pro']))
{
 $item_name = $_POST['data']['Invoice']['pro'];
 $item_code = $_POST['data']['Invoice']["des"];
 $item_des = $_POST['data']['Invoice']["qty"];
 $item_price = $_POST['data']['Invoice']["price"];
 $subtotal = $_POST['data']['Invoice']["sub_total"];
 $query = '';
 for($count = 0; $count<count($item_name); $count++)
 {
  $item_name_clean = mysqli_real_escape_string($connect, $item_name[$count]);
  $item_code_clean = mysqli_real_escape_string($connect, $item_code[$count]);
  $item_des_clean = mysqli_real_escape_string($connect, $item_des[$count]);
  $item_price_clean = mysqli_real_escape_string($connect, $item_price[$count]);
  $subtotal_clean = mysqli_real_escape_string($connect, $subtotal[$count]);
  if($item_name_clean != '' && $item_code_clean != '' && $item_des_clean != '' && $item_price_clean != '')
  {
   $query .= '
   INSERT INTO sell(pro, des, qty, price, sub_total) 
   VALUES("'.$item_name_clean.'", "'.$item_code_clean.'", "'.$item_des_clean.'", "'.$item_price_clean.'",  "'.$subtotal_clean.'"); 
   ';
  }
 }
 if($query != '')
 {
  if(mysqli_multi_query($connect, $query))
  {
   echo 'Item Data Inserted';
  }
  else
  {
   echo 'Error';
  }
 }
}
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement