PHP/MySQL stops Updating after table reaches certain number of rows

Question

I have a script that loads all rows and columns of a table at the same time as text fields so they can be edited simultaneously, then pushing a submit button MySQL table is UPDATED with all values.

A separate button will add or delete rows. Basically it's an inventory system.

This works great for me until my table reaches about 150 rows at which point the script will display all table values but updating and deleting will no longer work. Adding new rows is still working.

There are no errors, it looks exactly the same as when the updates are actually working. I'm running a recent version of WAMP.

It seems like a memory issue. I've tried the following modifications, it is my first time working with databases so there might be something simple I missed:

PHP.ini
memory_limit = 2048M
post_max_size = 128M
upload_max_filesize = 64M

MY.ini
innodb_buffer_pool_size = 512M
key_buffer_size = 256M
max_allowed_packet = 128M
sort_buffer_size = 128M
net_buffer_length = 8K
read_buffer_size = 64M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 512M
ead_buffer_size = 10M
write_buffer_size = 10M
sort_buffer_size_size = 40M
net_buffer_length = 64K

My table has 17 columns at the moment. Dropping columns will allow me to add 10 or 20 new rows but then the problem happens again.

So my questions: 1. How can I cheat and force my sloppy code to work. 2. How can I clean up my code to take care of things 'properly'.

Just a disclaimer, my code is most likely full of security issues, feel free to comment if you must but please give good explanations/examples when you do because this is my first project and I won't be able to understand your one liners.

Here is my code for the problem page. Remember that it works perfectly fine for me when my table is small. At the moment my UPDATE function is divided into 5 UPDATES, this was just a test but didn't seem to help. Originally the whole thing was a single UPDATE.

        




table { width:1200px;table-layout:fixed;}
table tr { height:1em;  }
td {overflow:hidden;white-space:nowrap;  }




Department
品名
Product#
Maker
Cost
/Per
Import Fee %
Prep Cost
MOQ
Weight
Size (t)
Category
Page
Specs
Comment
Stock



        
































        


        

















";




echo "


";

$sql = "SELECT * FROM $tbl_name";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    $count = $result->num_rows;
    // output data of each row
    while($row = $result->fetch_assoc()) {

echo "
";
}
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
echo "


DEL
Id
Department
品名
Product#
Maker
Cost
/Per
Import Fee %
Prep Cost
MOQ
Weight
Size (t)
Category
Page
Specs
Comment
Stock




 ";
$id[]=$row['id'];
echo $row['id'];
echo "





















































";



// Check if button name "Submit" is active, do this 
if(isset($_POST['Submit'])){
    $department= $_POST['department'];
    $productname = $_POST['productname'];
    $productnum = $_POST['productnum'];
    $maker = $_POST['maker'];
    $cost = $_POST['cost'];
    $per = $_POST['per'];   
    $importfee = $_POST['importfee'];   
    $prepcost = $_POST['prepcost']; 
    $MOQ= $_POST['MOQ'];
    $weight = $_POST['weight'];
    $sizeT = $_POST['sizeT'];   
    $category = $_POST['category'];
    $page = $_POST['page'];
    $specs = $_POST['specs'];   
    $comment = $_POST['comment'];   
    $stock = $_POST['stock'];       
for($i=0;$i<$count;$i++){
$sql1 = "UPDATE $tbl_name SET 
department='".$department[$i]."', productname='".$productname[$i]."', productnum='".$productnum[$i]."'
WHERE id='".$id[$i]."'";
$result1 = $conn->query($sql1);
}
for($i=0;$i<$count;$i++){
$sql1 = "UPDATE $tbl_name SET 
maker='".$maker[$i]."', cost='".$cost[$i]."', per='".$per[$i]."', importfee='".$importfee[$i]."', prepcost='".$prepcost[$i]."'
WHERE id='".$id[$i]."'";
$result1 = $conn->query($sql1);
}
for($i=0;$i<$count;$i++){
$sql1 = "UPDATE $tbl_name SET 
MOQ='".$MOQ[$i]."', weight='".$weight[$i]."'
WHERE id='".$id[$i]."'";
$result1 = $conn->query($sql1);
}
for($i=0;$i<$count;$i++){
$sql1 = "UPDATE $tbl_name SET 
sizeT='".$sizeT[$i]."', category='".$category[$i]."', page='".$page[$i]."'
WHERE id='".$id[$i]."'";
$result1 = $conn->query($sql1);
}
for($i=0;$i<$count;$i++){
$sql1 = "UPDATE $tbl_name SET 
specs='".$specs[$i]."', comment='".$comment[$i]."', stock='".$stock[$i]."'
WHERE id='".$id[$i]."'";
$result1 = $conn->query($sql1);
}
echo("");
}


if (isset($_POST['del'])){
    $checkbox = $_POST['checkbox'];
    $count = count($checkbox);
    for($i=0;$i<$count;$i++){
        if(!empty($checkbox[$i])){ /* CHECK IF CHECKBOX IS CLICKED OR NOT */
        $id = mysqli_real_escape_string($conn,$checkbox[$i]); /* ESCAPE STRINGS */
        mysqli_query($conn,"DELETE FROM $tbl_name WHERE id = '$id'"); /* EXECUTE QUERY AND USE ' ' (apostrophe) IN YOUR VARIABLE */
        } /* END OF IF NOT EMPTY CHECKBOX */
    } /* END OF FOR LOOP */
    echo("");
} /* END OF ISSET DELETE */



if(isset($_POST['Add'])){
$sql = "INSERT INTO $tbl_name (department,productname,productnum,maker,cost,per,importfee,prepcost,MOQ,weight,sizeT,category,page,specs,comment,stock)
VALUES('$_POST[department_add]','$_POST[productname_add]','$_POST[productnum_add]','$_POST[maker_add]','$_POST[cost_add]','$_POST[per_add]','$_POST[importfee_add]','$_POST[prepcost_add]','$_POST[MOQ_add]','$_POST[weight_add]','$_POST[sizeT_add]','$_POST[category_add]','$_POST[page_add]','$_POST[specs_add]','$_POST[comment_add]','$_POST[stock_add]')";
if ($conn->query($sql) === TRUE) {
echo("");
} else {
echo "Error: " . $sql . "
" . $conn->error;
}
}


$conn->close();

?>

Taking a suggestion from @Phil I changed my submit function to a prepared statement (maybe)... it was full of errors the first time I've corrected it and now it runs but will stop if I have to many rows in my table as before. The interesting thing is that if it updates I see echo "Updated {$stmt->affected_rows} rows" when it doesn't update there is no output at all. So it looks like the statement is not even executing?

// Check if button name "Submit" is active, do this 
if(isset($_POST['Submit'])){
    $department= $_POST['department'];
    $productname = $_POST['productname'];
    $productnum = $_POST['productnum'];
    $maker = $_POST['maker'];
    $cost = $_POST['cost'];
    $per = $_POST['per'];   
    $importfee = $_POST['importfee'];   
    $prepcost = $_POST['prepcost']; 
    $MOQ= $_POST['MOQ'];
    $weight = $_POST['weight'];
    $sizeT = $_POST['sizeT'];   
    $category = $_POST['category'];
    $page = $_POST['page'];
    $specs = $_POST['specs'];   
    $comment = $_POST['comment'];   
    $stock = $_POST['stock'];   

for($i=0;$i<$count;$i++){

$sql = "UPDATE $tbl_name SET 
department=?,productname=?,productnum=?,maker=?,cost=?,per=?,importfee=?,prepcost=?,MOQ=?,weight=?,sizeT=?,
category=?,page=?,specs=?,comment=?,stock=? 
WHERE id='".$id[$i]."'";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssssssssssssss',     $department[$i], $productname[$i], $productnum[$i], $maker[$i], $cost[$i] ,$per[$i] ,$importfee[$i],  
$prepcost[$i] ,$MOQ[$i], $weight[$i] ,$sizeT[$i] ,$category[$i] ,$page[$i], $specs[$i], $comment[$i], $stock[$i]);

$stmt->execute();
if ($stmt->errno) {
  echo "FAILURE!!! " . $stmt->error;
}
else echo "Updated {$stmt->affected_rows} rows";

}
$stmt->close();
}

mochaMame · Accepted Answer

This was a php.ini issue. Since I was trying to update everything at the same time the POST limit was reached:

; How many GET/POST/COOKIE input variables may be accepted
max_input_vars = 2500

147 rows x 17 columns = 2499 POST's so 148 rows was not accepted. Increasing the limit was enough to solve the issue.

PHP/MySQL stops Updating after table reaches certain number of rows

Answers (2)

Related Questions