文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Php Mysql Ajax插入多行

问Php Mysql Ajax插入多行
EN

Stack Overflow用户
提问于 2017-07-30 22:39:12
回答 2查看 4.1K关注 0票数 0

我使用的是javascript ajax动态html表单，它可以添加、删除输入字段序列。我使用php来插入数据，但是当我想插入多个行/数据时，它是insert into database，只有第一行表示第一个输入字段序列。

​

​

所以请检查我的代码中有什么问题

下面是我的html代码：

代码语言：javascript
复制
<form action="invoice.php" id="invoice-form" method="post"  class="invoice-form" role="form" novalidate>
        <div class='row'>
            <div class='col-xs-12 col-sm-12 col-md-12 col-lg-12'>
                <h1 class="text-center title">AFOJAL MINI MARKAET</h1>
            </div>
        </div>
        <h2>From,</h2>
        <div class='row'>
            <div class='col-xs-12 col-sm-4 col-md-4 col-lg-4'>
                <div class="form-group">
                    <input type="email" class="form-control" id="companyName" placeholder="Company Name">
                </div>
                <div class="form-group">
                    <textarea class="form-control" rows='3' id="companyAddress" placeholder="Your Address"></textarea>
                </div>
            </div>
            <div class='col-xs-12 col-sm-offset-3 col-md-offset-3 col-lg-offset-3 col-sm-4 col-md-4 col-lg-4'>

            </div>
        </div>
        <h2>To,</h2>
        <div class='row'>
            <div class='col-xs-12 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 class="form-group">
                    <input type="email" class="form-control" id="clientCompanyName" placeholder="Company Name">
                </div>
                <div class="form-group">
                    <textarea class="form-control" rows='3' id="clientAddress" placeholder="Your Address"></textarea>
                </div>

            </div>
            <div class='col-xs-12 col-sm-offset-3 col-md-offset-3 col-lg-offset-3 col-sm-4 col-md-4 col-lg-4'>
                <div class="form-group">
                    <input type="number" class="form-control" id="invoiceNo" placeholder="Invoice No">
                </div>
                <div class="form-group">
                    <input type="date" class="form-control" id="invoiceDate" placeholder="Invoice Date">
                </div>
                <div class="form-group">
                    <input type="number" class="form-control amountDue" id="amountDueTop" placeholder="Amount Due">
                </div>
            </div>
        </div>
        <h2>&nbsp;</h2>
        <div class='text-center'>
            <span>Note: Sample Item No. to Search <b>S1, S2</b></span>
        </div>
        <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 class='col-xs-12 col-sm-offset-4 col-md-offset-4 col-lg-offset-4 col-sm-5 col-md-5 col-lg-5'>
                <form class="form-inline">
                    <div class="form-group">
                        <label>Subtotal: &nbsp;</label>
                        <div class="input-group">
                            <div class="input-group-addon">$</div>
                            <input type="number" class="form-control" 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">$</div>
                            <input type="number" class="form-control" 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 type="number" class="form-control" 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">$</div>
                            <input type="number" class="form-control" id="totalAftertax" placeholder="Total" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                        </div>
                    </div>
                    <div class="form-group">
                        <label>Amount Paid: &nbsp;</label>
                        <div class="input-group">
                            <div class="input-group-addon">$</div>
                            <input type="number" class="form-control" id="amountPaid" placeholder="Amount Paid" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                        </div>
                    </div>
                    <div class="form-group">
                        <label>Amount Due: &nbsp;</label>
                        <div class="input-group">
                            <div class="input-group-addon">$</div>
                            <input type="number" class="form-control amountDue" id="amountDue" placeholder="Amount Due" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;">
                        </div>
                    </div>
                </form>

下面是我的ajax代码：

代码语言：javascript
复制
var i=$('table tr').length;
$(".addmore").on('click',function(){
    html = '<tr>';
    html += '<td><input class="case" type="checkbox"/></td>';
    html += '<td><input type="text" data-type="productCode" name="pro[]" id="itemNo_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
    html += '<td><input type="text" data-type="productName" name="des[]" id="itemName_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
    html += '<td><input type="text" name="price[]" id="price_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
    html += '<td><input type="text" name="qty[]" id="quantity_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
    html += '<td><input type="text" name="sub_total[]" id="total_'+i+'" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
    html += '</tr>';
    $('table').append(html);
    i++;
});

//to check all checkboxes
$(document).on('change','#check_all',function(){
    $('input[class=case]:checkbox').prop("checked", $(this).is(':checked'));
});

//deletes the selected table rows
$(".delete").on('click', function() {
    $('.case:checkbox:checked').parents("tr").remove();
    $('#check_all').prop("checked", false); 
    calculateTotal();
});


var prices = ["S24_4620|1961 Chevrolet Impala|32.33"];

//autocomplete script
$(document).on('focus','.autocomplete_txt',function(){
    type = $(this).data('type');

    if(type =='productCode' )autoTypeNo=0;
    if(type =='productName' )autoTypeNo=1;  

    $(this).autocomplete({
        source: function( request, response ) {  
             var array = $.map(prices, function (item) {
                 var code = item.split("|");
                 return {
                     label: code[autoTypeNo],
                     value: code[autoTypeNo],
                     data : item
                 }
             });
             //call the filter here
             response($.ui.autocomplete.filter(array, request.term));
        },
        autoFocus: true,            
        minLength: 2,
        select: function( event, ui ) {
            var names = ui.item.data.split("|");                        
            id_arr = $(this).attr('id');
            id = id_arr.split("_");
            $('#itemNo_'+id[1]).val(names[0]);
            $('#itemName_'+id[1]).val(names[1]);
            $('#quantity_'+id[1]).val(1);
            $('#price_'+id[1]).val(names[2]);
            $('#total_'+id[1]).val( 1*names[2] );
            calculateTotal();
        }               
    });
});

//price change
$(document).on('change keyup blur','.changesNo',function(){
    id_arr = $(this).attr('id');
    id = id_arr.split("_");
    qty = $('#quantity_'+id[1]).val();
    price = $('#price_'+id[1]).val();
    if( qty!='' && price !='' ) $('#total_'+id[1]).val( (parseFloat(price)*parseFloat(qty)).toFixed(2) );   
    calculateTotal();
});

$(document).on('change keyup blur','#tax',function(){
    calculateTotal();
});

//total price calculation 
function calculateTotal(){
    subTotal = 0 ; sub_total = 0; 
    $('.totalLinePrice').each(function(){
        if($(this).val() != '' )subTotal += parseFloat( $(this).val() );
    });
    $('#subTotal').val( subTotal.toFixed(2) );
    tax = $('#tax').val();
    if(tax != '' && typeof(tax) != "undefined" ){
        taxAmount = subTotal * ( parseFloat(tax) /100 );
        $('#taxAmount').val(taxAmount.toFixed(2));
        sub_total = subTotal + taxAmount;
    }else{
        $('#taxAmount').val(0);
        sub_total = subTotal;
    }
    $('#totalAftertax').val( sub_total.toFixed(2) );
    calculateAmountDue();
}

$(document).on('change keyup blur','#amountPaid',function(){
    calculateAmountDue();
});

//due amount calculation
function calculateAmountDue(){
    amountPaid = $('#amountPaid').val();
    sub_total = $('#totalAftertax').val();
    if(amountPaid != '' && typeof(amountPaid) != "undefined" ){
        amountDue = parseFloat(sub_total) - parseFloat( amountPaid );
        $('.amountDue').val( amountDue.toFixed(2) );
    }else{
        sub_total = parseFloat(sub_total).toFixed(2);
        $('.amountDue').val( sub_total);
    }
}


//It restrict the non-numbers
var specialKeys = new Array();
specialKeys.push(8,46); //Backspace
function IsNumeric(e) {
    var keyCode = e.which ? e.which : e.keyCode;
    console.log( keyCode );
    var ret = ((keyCode >= 48 && keyCode <= 57) || specialKeys.indexOf(keyCode) != -1);
    return ret;
}

//datepicker
$(function () {
    $.fn.datepicker.defaults.format = "dd-mm-yyyy";
    $('#invoiceDate').datepicker({
        startDate: '-3d',
        autoclose: true,
        clearBtn: true,
        todayHighlight: true
    });
});

这是我的php代码：

代码语言：javascript
复制
<?php

$connect = mysqli_connect("localhost", "root", "", "inv");
if(isset($_POST["data"]))
{
 $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';
 }
}
?>
javascript
php
jquery
mysql
ajax
EN

回答 2

Stack Overflow用户

发布于 2017-07-30 22:56:07

AJAX

代码语言：javascript
复制
 html += '<td><input type="text" data-type="productCode" name="pro[]" id="itemNo_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
 html += '<td><input type="text" data-type="productName" name="des[]" id="itemName_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';

PHP

代码语言：javascript
复制
 $item_name = $_POST["pro"];
 $item_code = $_POST["des"];

* php中的这些赋值似乎是颠倒的，在ajax中，productCode的名称是'pro‘。尝试在PHP中替换以下代码：

代码语言：javascript
复制
$item_name = $_POST["des"];
 $item_code = $_POST["pro"];
票数 0
EN

Stack Overflow用户

发布于 2017-07-30 23:03:18

追加新行时未使用相同的name属性值。然后将键发票设置为数组(参见第二和第三个代码片段)，因为您需要在PHP中循环遍历该数组(参见第四个代码片段)。

代码语言：javascript
复制
html += '<td><input class="case" type="checkbox"/></td>';
html += '<td><input type="text" data-type="productCode" name="pro[]" id="itemNo_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
html += '<td><input type="text" data-type="productName" name="des[]" id="itemName_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
html += '<td><input type="text" name="price[]" id="price_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
html += '<td><input type="text" name="qty[]" id="quantity_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
html += '<td><input type="text" name="sub_total[]" id="total_'+i+'" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';

对比

代码语言：javascript
复制
<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>

在追加新行时使用相同的属性名称：

代码语言：javascript
复制
html += '<td><input class="case" type="checkbox"/></td>';
html += '<td><input type="text" data-type="productCode" name="data[Invoice][][pro]" id="itemNo_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
html += '<td><input type="text" data-type="productName" name="data[Invoice][][des]" id="itemName_'+i+'" class="form-control autocomplete_txt" autocomplete="off"></td>';
html += '<td><input type="text" name="data[Invoice][][price]" id="price_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
html += '<td><input type="text" name="data[Invoice][][qty]" id="quantity_'+i+'" class="form-control changesNo" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';
html += '<td><input type="text" name="data[Invoice][][sub_total]" id="total_'+i+'" class="form-control totalLinePrice" autocomplete="off" onkeypress="return IsNumeric(event);" ondrop="return false;" onpaste="return false;"></td>';

顺便说一句，您还可以使用clone方法来追加表行的副本。

PHP脚本..在PHP部分中，我看到两个地方出了问题(除了缺少sql注入预防)。您应该遍历数据发票键，因为它们携带行数组。然后，您应该在sql查询中添加分号以结束语句。

代码语言：javascript
复制
if (isset($_POST["data"])) {

    foreach($_POST["data"]["Invoice"] as $item) {

        $item_name = $item["pro"];
        $item_code = $item["des"];
        $item_des = $item["qty"];
        $item_price = $item["price"];
        $subtotal = $item["sub_total"];
        $query = '';

        $item_name_clean = mysqli_real_escape_string($connect, $item_name);
        $item_code_clean = mysqli_real_escape_string($connect, $item_code);
        $item_des_clean = mysqli_real_escape_string($connect, $item_des);
        $item_price_clean = mysqli_real_escape_string($connect, $item_price);
        $subtotal_clean = mysqli_real_escape_string($connect, $subtotal);
        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';
        }
    }
}
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/45400967

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档