INSERT & UPDATE in While Loop not working after about 111 Entries

MySQL database
#1

I’m having problems where the INSERT is not bringing all the records from a form after about 111 records. It will create the record with the “job_no”, but doesn’t bring in the other fields after about 111 records. Below is the code. Thanks for any help. (I added the “a” after the UID as a test. it is not necessary and didn’t help)

$resultx = mysql_query(“SELECT * FROM materials WHERE job_no=‘SAC51’ ORDER BY UID”);

while($row = mysql_fetch_array($resultx))
{
$venmat = “venmatchg” . $row[‘UID’] . “a” ;
$package = “packagechg” . $row[‘UID’] . “a” ;
$inv_no = “inv_nochg” . $row[‘UID’] . “a” ;
$date = “datechg” . $row[‘UID’] . “a” ;
$qty = “qtychg” . $row[‘UID’] . “a” ;
$uom = “uomchg” . $row[‘UID’] . “a” ;
$up = “upchg” . $row[‘UID’] . “a” ;
$invtotal = “invtotalchg” . $row[‘UID’] . “a” ;
$job = $_POST[jobnumber] . “a” ;
$uid = $row[‘UID’] . “a” ;

mysql_query(“INSERT INTO materialstest (job_no, venmat, package, invoice_no, date, qty, uom, unit_price, invtotal)
VALUES(‘SAC51’,’$_POST[$venmat]’,’$_POST[$package]’,’$_POST[$inv_no]’,’$_POST[$date]’,’$_POST[$qty]’,’$_POST[$uom]’,’$_POST[$up]’,’$_POST[$invtotal]’ )”);

}

#2

First and foremost, your code is outdated. PDO or mysqli are the only classes that should be used when dealing with a database.

Next, Prepared statements and parameterized statements. You should not be dropping user supplied data directly into a table. It is unsafe, and hazardous to your system.

Lastly, I am not entirely sure what your goal is, but this looks like a bad way to accomplish it.

#3

OK short of redoing everything, any ideas on why I would be having this problem.

#4

It is likely because of how the mysql api handles connections and you quickly hit your max_connections limit. As to how to fix it, not with that code.

#5

What I have been doing is echoing a form that returns the data from a database into an input field so it is easier to edit when I need to make changes. (Edit the whole database on a single form) Then I was using UPDATE in a Loop to put the changes back into the database. The example I gave with the INSERT function is just how I was testing it to figure out the problem. What I guess i’ll do now is limit the number of records I return on the editable form to less than 100 and go from there.
I’m sure there is a much more efficient way to do this, but after much trial and error this is what I came up with and it is still a great improvement from what I used to do. I’m self taught on all the coding I know and only learn enough to put whatever concept I have to practice.

#6

If all this is for is to update the databse in a single area, there are better more widespread tools like, phpmyadmin and mysql workbench that you are directly interacting with the database.

#7

Why, why, why do you people INSIST on using your obsolete, insecure code? You are just asking to be hacked. That code is absolute junk and a SEVERE security risk. Just forget it and move on to PDO. Nobody should be helping you “fix” it. It is un-fixable.

https://phpdelusions.net/pdo

#8

It makes you wonder why they created such a DANGEROUS code in the first place right. Thanks for the help.

#9

OK after a bit of trial and error, switched to mysqli, but still get same results. It will only INSERT 111 records.

$stmt = $con->prepare(“INSERT INTO materialstest (job_no, venmat, package, invoice_no, date, qty, uom, unit_price, invtotal)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)”);
$stmt->bind_param(“sssssisii”, $job ,$venmat ,$package ,$inv_no ,$date ,$qty ,$uom ,$up ,$invtotal);

$sqlx = “SELECT * FROM materials WHERE job_no=‘SAC51’ ORDER BY UID”;
$resultx=mysqli_query($con,$sqlx);

while($row= $resultx->fetch_assoc())
{
$venmatx = “venmatchg” . $row[‘UID’] ;
$packagex = “packagechg” . $row[‘UID’] ;
$inv_nox = “inv_nochg” . $row[‘UID’] ;
$datex = “datechg” . $row[‘UID’] ;
$qtyx = “qtychg” . $row[‘UID’] ;
$uomx = “uomchg” . $row[‘UID’] ;
$upx = “upchg” . $row[‘UID’] ;
$invtotalx = “invtotalchg” . $row[‘UID’] ;
$jobx = $_POST[jobnumber] ;
$uidx = $row[‘UID’] ;

$venmat = "$_POST[$venmatx]" ;
$package = $_POST[$packagex] ;
$inv_no = $_POST[$inv_nox] ;
$date = $_POST[$datex] ;
$qty = $_POST[$qtyx] ;
$uom = $_POST[$uomx] ;
$up = $_POST[$upx] ;
$invtotal = $_POST[$invtotalx] ;
$job = 	"SAC51" ;
$uid = $_POST[$uidx] ;

if ($venmat != "")	$stmt->execute();

}

#10

Why are you selecting records to insert into another table?

#11

To edit multiple records at a time in a table form.

#12

Then you would be updating, not inserting new.

For you issue, I still think it has to do with the number of open connections to the database.

Check this topic out, max_connections

#13

Classic XY Problem http://xyproblem.info/

#14

I like it

#15

I explained my insert and update trials, other things I have tried, etc above. my question/problem is simple. I’m not asking for a solution to another problem. I want to know why I seem to be limited to specifically 111 Inserts or updates to the database.

#16

I think I mentioned the number of open connectionsjaving to be the issue.

Sponsor our Newsletter | Privacy Policy | Terms of Service