PHP not inserting more then 91 rows form data to d

Question

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

After inserting 91 rows it is giving the error

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

MY HTML FORM

<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  ** //
    }

?>

Answer 1

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

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

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 has a couple of parameters that might be getting exceeded i.e.

• post_max_size Which would mean that not all the post array was being received by the script

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

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.

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.

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

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

Answer 2

If the issue is that you have reached the max POST size then this won't help, but as an example of using prepared statements to insert the data this should be useful. Using the form data that you posted on Pastebin in conjunction with this code it inserted it happily inserted 151 rows into the db with no errors ( yes, created a table specially ) ~ perhaps I should add that I submitted the form via POST without ajax!

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' ){

        function filter( $conn, $var ){
            return $conn->real_escape_string( filter_var( $var, FILTER_SANITIZE_STRING ) );
        }

        $servername = "localhost";
        $username = "root";
        $password = "xxxx";
        $dbname = "xxxxx";
        $conn = new mysqli( $servername, $username, $password, $dbname );
        /* I obviously used correct details for my db */

        $sql='INSERT INTO `attendance_master` ( 
                `StudentFirstName`, `AttendanceStatus`, `StudentRegID`, `SchoolID`, `Section`, 
                `Standard`, `Subjects`, `AttendanceDate`, `UserType`, `Attendence_taken_by`, `Attendence_taken_date`
            ) VALUES ( ?,?,?,?,?,?,?,?,?,?,? )';

        $stmt=$conn->prepare( $sql );


        foreach( $_POST['Reg']  as $i => $student ) {
            try{
                $stud   = filter( $conn, $_POST['student'][$i] );
                $pres   = filter( $conn, $_POST['present'][$i] );
                $reg    = filter( $conn, $_POST['Reg'][$i] );
                $id     = filter( $conn, $_POST['schoolid'][$i] );
                $sect   = filter( $conn, $_POST['section'][$i] );
                $stan   = filter( $conn, $_POST['standard'][$i] );
                $subj   = filter( $conn, $_POST['Subject'][$i] );
                $date   = filter( $conn, $_POST['date1'][$i] );
                $type   = filter( $conn, $_POST['UserType'][$i] );
                $uid    = filter( $conn, $_POST['Userid'][$i] );
                $subm   = filter( $conn, $_POST['date_user_submitted'][$i] );

                $stmt->bind_param('sssssssssss', $stud, $pres, $reg, $id, $sect, $stan, $subj, $date, $type, $uid, $subm );
                if( !$stmt->execute() ) throw new Exception( 'Insert failed @ row'.$i, 400 );
            } catch( Exception $e ){
                echo $e->getMessage();
                continue;
            }
        }
        $stmt->close();
        $conn->close();
    }
?>