Ajax post method, PHP limited insert query

Issue

I add the data from open dialog to the table as tdname[rowid]. I am getting result as below.

        <form method="post" id="user_form">
            <div class="table-responsive">
                <table id="user_data">
                    <tbody>
                        <tr id="row_1">
                            <td>1 <input type="hidden" name="hidden_pid[]" id="pid1" class="pid" value="1"></td>
                            <td>K-60 <input type="hidden" name="hidden_p_code[]" id="p_code1" class="p_code" value="K-60"></td>
                            <td>Product 1<input type="hidden" name="hidden_p_name[]" id="p_name1" value="Product 1"></td>
                            <td> <input type="hidden" name="hidden_dekorA[]" id="dekorA1" value=""></td>
                            <td class="p_quantity">1 <input type="hidden" name="hidden_p_quantity[]" class="p_quantity1" id="p_quantity1" min="0" max="2500" step="1" value="1" data-type="1"></td>
                            <td>23.40 <input type="hidden" name="hidden_p_listprice[]" class="p_listprice1" id="p_listprice1" value="23.40" data-type="23.40"></td>
                            <td>23.40 <input type="hidden" name="hidden_p_netprice[]" id="p_netprice1" value="23.40"></td>
                            <td class="p_total">23.40 <input type="hidden" name="hidden_p_total[]" class="p_total" id="p_total1" value="23.40" for="1"></td>
                            <td>122 <input type="hidden" name="hidden_preorderno[]" id="preorderno1" class="preorderno" value="122"></td>
                        </tr>
                        .
                        .
                        .
                    </tbody></table>
            </div>
            <div align="center">
                <button type="submit" name="insert" id="insert" class="btn btn-primary">Confirm Order</button>
            </div>
    </form>

Then I send the data to the insert.php file with the ajax post method.

     <script>
$(document).ready(function(){ 
    $('#user_form').on('submit', function(event){
        event.preventDefault();
            var form_data = $("[name='hidden_pid[]'],[name='hidden_p_code[]'],[name='hidden_p_name[]'],[name='hidden_dekorA[]'],[name='hidden_p_quantity[]'],[name='hidden_p_listprice[]'],[name='hidden_p_netprice[]'],[name='hidden_p_total[]'],[name='hidden_preorderno[]']").serialize();
            $.ajax({
                type:"POST",
                url:"insert.php",
                data : (form_data),
                success:function(data)
                {
                    alert('OK');
                    
                },
                error:function(data){
                    alert('hata');
                }
            });
        
    }); 
});
</script>

I don’t want to serialize all of items, so thats why im using like this serialize => [name='hidden_pid[]']
Im using stored procedure for insert. NEWLIST is my insert procedure.

Insert.php file:

$db = new PDO("mysql:host=localhost;dbname=;charset=utf8;","username","pass");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try{
       
    $sp='NEWLIST';  
    $sql=sprintf('SELECT group_concat(DISTINCT ":",`parameter_name`) as `placeholders` FROM `information_schema`.`parameters` WHERE `SPECIFIC_NAME`="%s" and `specific_schema`="wpdb"', $sp);
        
    $res=$db->query($sql)->fetch(PDO::FETCH_OBJ);
    $placeholders=$res->placeholders;
    
    $sql=sprintf('call `%s`( %s );', $sp, $placeholders);     
    $keys=explode( ',', $placeholders );
    $size = count($_REQUEST['hidden_pid']);
    $count = 0;    
    $stmt=$db->prepare($sql);
        
    while($count < $size){
        $main_arr = array(
            ':pid'          =>  $_REQUEST['hidden_pid'][$count],       
            ':p_code'       =>  $_REQUEST['hidden_p_code'][$count],
            ':p_name'       =>  $_REQUEST['hidden_p_name'][$count],
            ':dekorA'       =>  $_REQUEST['hidden_dekorA'][$count],
            ':p_quantity'   =>  $_REQUEST['hidden_p_quantity'][$count],
            ':p_listprice'  =>  $_REQUEST['hidden_p_listprice'][$count],
            ':p_netprice'   =>  $_REQUEST['hidden_p_netprice'][$count],
            ':p_total'      =>  $_REQUEST['hidden_p_total'][$count],
            ':preorderno'   =>  $_REQUEST['hidden_preorderno'][$count],
            ':yetkili'      =>  $_SESSION['id']
        );
        $count++;
        $data=array_combine($keys, array_values($main_arr));
        $stmt->execute($data);
    }
    
    $db->commit();

} catch( PDOException $e ){
    
    return "Insert failed: " . $e->getMessage();
}
$db = null;

This is working. But the problem is i can insert to mysql maximum 111 rows like this way. Im adding several hundred rows on form and ajax post sending value to insert.php but insert.php insert maximum 111 rows to mysql.
I also research on stackoverflow, people says max_allowed_packet value should 500MB. And i already do that. But still going on that issue.
The problem is on insert.php, i think. But how can i fix that issue i don’t know. I would like to see your helps..

Its not working on my both servers:

1.Test server informations

CPU: AMD Ryzen 7 2700, 3.2 GHz

RAM: 16 GB RAM

2.Raspberry pi 4 server informations

CPU: BCM2711, 1.5 GHz

RAM: 4 GB RAM

Solution

You need to increase the PHP [max_input_vars] setting in php.ini. The default value is 1,000. With 9 parameters in each row, that allows for only 111 rows.

Multiply the maximum number of rows you need to support by 9 and set it to something higher than this.

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published