PHP not inserting more then 91 rows form data to data base using ajax

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

?>

Show us the ACTUAL error message, then possbly we can help! — RiggsFolly
– RiggsFolly, Commented Jan 7, 2016 at 10:56
Think you might be missing a trick here - use prepared statements rather than a massively concatenated string — Professor Abronsius
– Professor Abronsius, Commented Jan 7, 2016 at 10:57
My thoughts entirely @RamRaider prepare the statement with parameters once and then execute that prepared statement any number of times. This will reduce the execution time of your queries as the query will only need to be compiled and optimized once. You are using mysqli_ but getting none of its benefits — RiggsFolly
– RiggsFolly, Commented Jan 7, 2016 at 10:59
@RayonDabre am doing this for the attendence purpose if there are 200 students i need to insert 200 rows to data base in my case now i have 150 students and it is not inserting — Shaik
– Shaik, Commented Jan 7, 2016 at 10:59
Have you hit a server default transfer size limit? Use a tool like Fiddler2 to see what is going on and show us the real error :) — iCollect.it Ltd
– iCollect.it Ltd, Commented Jan 7, 2016 at 10:59

RiggsFolly · Accepted Answer · 2016-01-07 12:26:37Z

3

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

edited Jan 7, 2016 at 12:26

answered Jan 7, 2016 at 11:31

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Shaik Over a year ago

this makes sense thaks for ur support vl investigate on it a moment

Professor Abronsius Over a year ago

Well spotted - it would be good to see how the form is generated initially ~ there is a spurious closing </tr> tag right after the opening tbody tag

Shaik Over a year ago

@RiggsFolly as i mentioned above as i coudnet post all the html code so i deleted some of it that is not the issue

RiggsFolly Over a year ago

Ok so edit your question and make sure that the HTML you post is accurate for occurance 90 throught 94

RiggsFolly Over a year ago

See additional info in the answer

|

Professor Abronsius · Accepted Answer · 2016-01-07 12:25:52Z

1

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();
    }
?>

edited Jan 7, 2016 at 12:25

answered Jan 7, 2016 at 11:21

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

3 Comments

Professor Abronsius Over a year ago

that indicates the data type being passed in at that position in the sql statement. s - string, i - integer. Similar to what you have done in the sprintf function call except without the % sign

Shaik Over a year ago

i tried again it is just inserting 91 rows the remaining it dint

RiggsFolly Over a year ago

Even though this was not the actual error, do keep using this style of parameterised and prepared queries as is also provides sql injection protection, and when used to execute the same query more than once will speed up you script execution time.

Collectives™ on Stack Overflow

PHP not inserting more then 91 rows form data to data base using ajax

2 Answers 2

13 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related