Cannot Insert More than 180 Records into Database Using PHP-Mysqli

Question

I am working on a project that requires me to forecast material usage for a period and generate a summary.I am able to generate the forecast,which can be as long as 1200rows.However,in order to generate the summary,the record need to be store on the database where they can be fetched to summarized.The issue is that: when a button is clicked to generated the summary,the code only works, when the total rows<=200.I have checked the code,search StackOverflows and even change the max_allowed_packed=999MB.Yet it is still not inserting into the Database.See code below and kindly assist as i have searched without getting solution. See the Insertion code:

if(isset($_POST['exportBtn']) && isset($_POST['sflt'])){
        //$arr = array();
        foreach($_POST['sflt'] as $key => $value) {
         set_time_limit(0);
         $eflt = mysql_prep($_POST['sflt'][$key]);
         $emodel = mysql_prep($_POST['smodel'][$key]);
         $eengine = mysql_prep($_POST['sengine'][$key]);
         $eloc = mysql_prep($_POST['sloc'][$key]);
         $estye = mysql_prep($_POST['sstye'][$key]);
         $ensvr = mysql_prep($_POST['snsvr'][$key]);
         $eehd = mysql_prep($_POST['sehd'][$key]);
         $epname = mysql_prep($_POST['spname'][$key]);

         $inExp = mysqli_query($con,"INSERT  INTO tab_forecast(fltno,model,engine,loc,serviceType,nextSvr,usageHr,partName)VALUES ('$eflt','$emodel','$eengine','$eloc','$estye','$ensvr','$eehd','$epname')");
         }//End for-each loop

Please, note that i have also tried to create a single insert statement instead of insertion one by one,yet the result is still the same - not inserting all records into the database. My database engine is set to InnoDB,yet the same result. Please, what else can i do or is there a better way to solve this issue. Note, i have search SO,but no solution meets my need, hence this question.

Where specifically is it failing? Is there an error on a specific record which stops the script from continuing? Is all of the data sent to the server in the first place? Something else? — David, Dec 20 '17 at 15:02
If your passing all of your data in POST variables, you may be reaching a limit with that — Nigel Ren, Dec 20 '17 at 15:04
@David: I have configured it to show error, no error is displaying. It is not inserting into the DB,once the records/rows is close to 200 rows — David Mukoro, Dec 20 '17 at 15:05
What is `mysql_prep()`? Is that a wrapper for `mysqli_real_escape_string()`? — RiggsFolly, Dec 20 '17 at 15:05
@RiggsFolly: sorry it is a same function as mysqli_real_escape_string() function — David Mukoro, Dec 20 '17 at 15:06
Then Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Dec 20 '17 at 15:07
@Dave: `foreach($_POST['sflt']` - What is the value of `count($_POST['sflt'])` ? When you examine your POST request in your browser's debugging tools, does it contain all of the data you expect it to? Is it always failing on the same record? What if you move that record to earlier in the data? Does that specific record still fail, or does another arbitrary one fail at around the same index of the array? There's a lot of debugging you can do here that we can't do for you. — David, Dec 20 '17 at 15:09
@David: let me examine that and get back to you now.@RiggsFolly. Thanks for that. I will work on that aspect. Please, help solve the question as well. — David Mukoro, Dec 20 '17 at 15:12
You may also need to increase `post_max_size` to be a bit larger than `max_input_vars` as the input vars + other things are part of the `post_max_size` — RiggsFolly, Dec 20 '17 at 15:18
@RiggsFolly: please, how?. Also David: I am using firefox and i have navigated to the web developer menu and selected network. I can't see where to count the post sflt. please, where can this be done — David Mukoro, Dec 20 '17 at 15:21
@RiggsFolly: it says max_input_vars = 5000 suhosin.request.max_vars = 5000 suhosin.post.max_vars = 5000, but how do i know if i am using Suhosin with Hardened PHP or not. Or is the code for all type of php? — David Mukoro, Dec 20 '17 at 15:27
Then it may not be what I thought, I have reopened the question so others can try and help — RiggsFolly, Dec 20 '17 at 15:29
@NigelRen: Please, do i count count($_POST['sflt']) or count($_POST['sflt']['Key']). Also, do i insert this after the foreach loop or where. I am sorry, a bit confused. — David Mukoro, Dec 20 '17 at 15:41
@NigelRen: I disabled the insertion part of the code and it gives 247 records, which is total rows. — David Mukoro, Dec 20 '17 at 15:48
@NigelRen: I changes the post_max_size in php.ini file from 8M to 1000M, and i am able to insert the whole 276 records for the first time. I tried close to 500 record, but didn't work. What do you think might be the issue? — David Mukoro, Dec 20 '17 at 16:01
@Dave it means your form is posting more data than your `post_max_size` allows. Ex: `post_max_size` = 10M, form posts 20M, php will only receive the first 10M of the 20M post. — Tony Chiboucas, Dec 20 '17 at 19:13

score 0 · Answer 1 · answered Dec 21 '17 at 12:26

The code i wrote works perfectly, all that needed to be done is to change certain parameters on the server. I created a new ini file in the directory where the files are located.Usually, this can .user.ini or php.ini file. The following parameters were set to override the default settings:

memory_limit = 1G
max_execution_time = 300
max_input_time = 300
post_max_size = 1G
max_input_vars = 10000
file_uploads = -1
max_file_uploads = 35
upload_max_filesize = 1G

, As soon as this is done. You can verfy by runing echo phpinfo() in a testing script to confirm the changes.Once this is done, i was able to insert more than 500 rows. Infact changing the max_input_time =-1 and max_input_vars = 50000, i was able to insert more than 1000 records.

Please, note that this can be done on local/live server, as long as you have the resources to handle the request. I hope this help someone someday. Thank you all.

Cannot Insert More than 180 Records into Database Using PHP-Mysqli

1 Answers1