PHP不使用ajax插入超过91行的数据到数据库 [英] PHP not inserting more then 91 rows form data to data base using ajax

问题描述

我正在尝试向数据库插入超过150行，但发布请求仅发生在91行和控制台日志说内部服务器错误

I am trying to insert more then 150 rows to data base but the post request is happening for only 91 rows and the console log says internal server error

插入91行后出现错误

您的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便在第1行''附近使用正确的语法

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

我的HTML表格

<form id="mySpan4" style="display: block;">
<table class="fixed_headers"><tbody>

<!-- SKIPPED TO OCC 90 Showing only 90 to 94 -->

        </tr><tr>
        <td>PRASAD  D R <input type="hidden" name="student[90]" value="PRASAD  D R"><input type="hidden" name="Reg[90]" value="13KQC31170"><input type="hidden" name="schoolid[90]" value="FT001"><input type="hidden" name="section[90]" value="A"><input type="hidden" name="standard[90]" value="III BCOM"><input type="hidden" name="Subject[90]" value="COM-42"><input type="hidden" name="date1[90]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[90]" value="STUDENT"><input type="hidden" name="Userid[90]" value="admin"><input type="hidden" name="date_user_submitted[90]" value="2016-01-07  03:51:45 pm"></td>



        <td><input id="radio01[90]" name="present[90]" type="radio" value="Present"><label for="radio01[90]"><span></span></label></td>
        <td><input id="radio02[90]" name="present[90]" type="radio" value="Absent"><label for="radio02[90]"><span></span></label></td>
        <td><input id="radio03[90]" name="present[90]" type="radio" value="Leave"><label for="radio03[90]"><span></span></label></td>
        </tr><tr>
        <td>PRASAD K T <input type="hidden" name="student[91]" value="PRASAD K T"><input type="hidden" name="Reg[91]" value="13KQC31171"><input type="hidden" name="schoolid[91]" value="FT001"><input type="hidden" name="section[91]" value="A"><input type="hidden" name="standard[91]" value="III BCOM"><input type="hidden" name="Subject[91]" value="COM-42"><input type="hidden" name="date1[91]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[91]" value="STUDENT"><input type="hidden" name="Userid[91]" value="admin"><input type="hidden" name="date_user_submitted[91]" value="2016-01-07  03:51:45 pm"></td>



        <td><input id="radio01[91]" name="present[91]" type="radio" value="Present"><label for="radio01[91]"><span></span></label></td>
        <td><input id="radio02[91]" name="present[91]" type="radio" value="Absent"><label for="radio02[91]"><span></span></label></td>
        <td><input id="radio03[91]" name="present[91]" type="radio" value="Leave"><label for="radio03[91]"><span></span></label></td>
        </tr><tr>
        <td>PRASANNA B <input type="hidden" name="student[92]" value="PRASANNA B"><input type="hidden" name="Reg[92]" value="13KQC31172"><input type="hidden" name="schoolid[92]" value="FT001"><input type="hidden" name="section[92]" value="A"><input type="hidden" name="standard[92]" value="III BCOM"><input type="hidden" name="Subject[92]" value="COM-42"><input type="hidden" name="date1[92]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[92]" value="STUDENT"><input type="hidden" name="Userid[92]" value="admin"><input type="hidden" name="date_user_submitted[92]" value="2016-01-07  03:51:45 pm"></td>



        <td><input id="radio01[92]" name="present[92]" type="radio" value="Present"><label for="radio01[92]"><span></span></label></td>
        <td><input id="radio02[92]" name="present[92]" type="radio" value="Absent"><label for="radio02[92]"><span></span></label></td>
        <td><input id="radio03[92]" name="present[92]" type="radio" value="Leave"><label for="radio03[92]"><span></span></label></td>
        </tr><tr>
        <td>PRASANNAKUMAR K <input type="hidden" name="student[93]" value="PRASANNAKUMAR K"><input type="hidden" name="Reg[93]" value="13KQC31173"><input type="hidden" name="schoolid[93]" value="FT001"><input type="hidden" name="section[93]" value="A"><input type="hidden" name="standard[93]" value="III BCOM"><input type="hidden" name="Subject[93]" value="COM-42"><input type="hidden" name="date1[93]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[93]" value="STUDENT"><input type="hidden" name="Userid[93]" value="admin"><input type="hidden" name="date_user_submitted[93]" value="2016-01-07  03:51:45 pm"></td>



        <td><input id="radio01[93]" name="present[93]" type="radio" value="Present"><label for="radio01[93]"><span></span></label></td>
        <td><input id="radio02[93]" name="present[93]" type="radio" value="Absent"><label for="radio02[93]"><span></span></label></td>
        <td><input id="radio03[93]" name="present[93]" type="radio" value="Leave"><label for="radio03[93]"><span></span></label></td>
        </tr><tr>
        <td>PREETHI C R <input type="hidden" name="student[94]" value="PREETHI C R"><input type="hidden" name="Reg[94]" value="13KQC31175"><input type="hidden" name="schoolid[94]" value="FT001"><input type="hidden" name="section[94]" value="A"><input type="hidden" name="standard[94]" value="III BCOM"><input type="hidden" name="Subject[94]" value="COM-42"><input type="hidden" name="date1[94]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[94]" value="STUDENT"><input type="hidden" name="Userid[94]" value="admin"><input type="hidden" name="date_user_submitted[94]" value="2016-01-07  03:51:45 pm"></td>



        <td><input id="radio01[94]" name="present[94]" type="radio" value="Present"><label for="radio01[94]"><span></span></label></td>
        <td><input id="radio02[94]" name="present[94]" type="radio" value="Absent"><label for="radio02[94]"><span></span></label></td>
        <td><input id="radio03[94]" name="present[94]" type="radio" value="Leave"><label for="radio03[94]"><span></span></label></td>
        </tr><tr>
        <td>RADHA H N <input type="hidden" name="student[95]" value="RADHA H N"><input type="hidden" name="Reg[95]" value="13KQC31179"><input type="hidden" name="schoolid[95]" value="FT001"><input type="hidden" name="section[95]" value="A"><input type="hidden" name="standard[95]" value="III BCOM"><input type="hidden" name="Subject[95]" value="COM-42"><input type="hidden" name="date1[95]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[95]" value="STUDENT"><input type="hidden" name="Userid[95]" value="admin"><input type="hidden" name="date_user_submitted[95]" value="2016-01-07  03:51:45 pm"></td>

JS

$(document).ready(function() {
                $('#mySpan4').submit(function(e) {
                    $.ajax({
                        type: 'post',
                        url: 'http://localhost/demo_TeacherApp/attendence_submit.php',   // you should not use absolute addresses, with http:// . only relative paths
                        data: $('#mySpan4').serialize(),
                        success: function(data) {
                            // log $sql string
                            //<!-- $('#log').html(data); -->
                        }
                    });
                    // prevents the form from really submitting through the normal way
                    e.preventDefault();
                    return false;
                })
            });

PHP

<?php
    $servername = "localhost";
    $username = "root";
    $password = "xxxx";

    $dbname = "xxxxx";
    $conn = new mysqli($servername, $username, $password, $dbname);
    // ** insert data in to data base ** //
    $sql = "INSERT INTO attendance_master (StudentFirstName,AttendanceStatus,StudentRegID,SchoolID,Section,Standard,Subjects,AttendanceDate,UserType,Attendence_taken_by,Attendence_taken_date) VALUES ";
    // **hear the data or is calculated using student as a string the number of student names are passed the same number of data is inserted in to data base ** //
    foreach($_POST['Reg']  as $i=>$student) {

        // ** so the data is inserted in to data base   ** //
        $sql .= sprintf("%s ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
        , ($i==0 ? '' : ',')  
        // **  data from Ajax   ** //
        , mysqli_real_escape_string($conn, trim($_POST['student'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['present'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['Reg'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['schoolid'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['section'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['standard'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['Subject'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['date1'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['UserType'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['Userid'][$i]))
        , mysqli_real_escape_string($conn, trim($_POST['date_user_submitted'][$i]))

        );
    }
    // ** on success full function   ** //
    if ($conn->query($sql)) {
        echo $sql;// **can do any thing to recognise if the data is inserted if this gives the out put then the data is shorly inserted in to data base  ** //
    }

?>

推荐答案

我注意到你的HTML中你的索引跳过了92到出现146.

I notice that in your HTML your indexing jumps from occurance 92 to occurance 146.

PRASANNA B 的隐藏字段也出现了92但单选按钮已经出现145.

Also the hidden fields that for PRASANNA B have occurance 92 but the radio buttons have occurance 145.

这看起来就像你创建这个表单时你的索引编排出了问题！这可能解释了为什么在将第92行加载到数据库时出现问题的原因

This looks like when you create this form something has gone wrong with your indexing stratagy! This would possibly explain why it all starts to go wrong when loading row 92 to your database

</tr><tr>
<td>PRASANNA B <input type="hidden" name="student[92]" value="PRASANNA B"><input type="hidden" name="Reg[92]" value="13KQC31172"><input type="hidden" name="schoolid[92]" value="FT001"><input type="hidden" name="section[92]" value="A"><input type="hidden" name="standard[92]" value="III BCOM"><input type="hidden" name="Subject[92]" value="COM-38"><input type="hidden" name="date1[92]" value="16-01-07 00:00:00"><input type="hidden" name="UserType[92]" value="STUDENT"><input type="hidden" name="Userid[92]" value="admin"><input type="hidden" name="date_user_submitted[92]" value="2016-01-07  03:37:22 pm"></td>

<td><input id="radio01[145]" name="present[145]" type="radio" value="Present"><label for="radio01[145]"><span></span></label></td>
<td><input id="radio02[145]" name="present[145]" type="radio" value="Absent"><label for="radio02[145]"><span></span></label></td>
<td><input id="radio03[145]" name="present[145]" type="radio" value="Leave"><label for="radio03[145]"><span></span></label></td>
</tr><tr>

现在我们已经反驳了这个想法！ 让我们从头开始。

Ok now we have disproved that idea! Lets start at the beginning.

PHP有一些参数可能会被超越，即

PHP has a couple of parameters that might be getting exceeded i.e.

• post_max_size 这意味着脚本不会收到所有帖子数组

max_input_vars 这限制了脚本将接受的< input> 变量的数量

max_input_vars Which limits the number of <input> variables that will be accepted by a script

由于你至少有 1950 输入变量，我知道这可能是第一个要检查的变量。请查看这些参数的 php.ini 文件，并告诉我它们是什么。

As you have at least 1950 input variable that I know of this may be the first one to check. So look at your php.ini file for both of these parameter and let me know what they are.

所以从您的评论中，似乎 max_input_vars = 1000 对于至少具有 1950 变量的脚本来说还不够。这肯定会解释你得到的错误！ 1000是默认值我相信这就是为什么它的评论形式。

So from your comment it seems like max_input_vars = 1000 is not enough for this script which has at least 1950 variables. This would definitely explain the error you are getting! 1000 being the default I believe which is why its in comment form.

所以修改 php.ini 并制作 max_input_vars = 2500 ，当然是安全的，没有; 评论。

So amend the php.ini and make max_input_vars = 2500, just to be on the safe side without the ; comment of course.

不要忘记在更改 php.ini 后重启Apache

Dont forget to restart Apache after you have made the change to php.ini

这篇关于PHP不使用ajax插入超过91行的数据到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！