CODEWITHSUNDEEP
phpmysqlloopswhile-loop
contool
contool

Reputation: 1074

mysql INSERT INTO loop not inserting some rows

I have a loop that is inserting new rows into my database. It is specifically designed to insert a specific number of rows with a unique value in the 'name' column.

while($rows_inserted < 100) {
    // Create a random number  
      
    //Min & Max values for the random numbers
    $min = 5001;
    $max = 5100;
    
    $number = rand($min,$max);
    
    //Connect to the DB using $con details & execute query
    mysqli_query($con,"INSERT INTO cmssps_card_codes (id, parent_id, name, status) VALUES ('', '', '$number', '0')");
    
    if (mysqli_affected_rows($con) === 1) {
        $rows_inserted++;  // update the counter
    }
}

In this particular limited instance, I'm creating 100 "random" numbers between 5001 and 5100 so it's redundant in this instance but the random number is used in production instances - just to avoid confusion.

My problem is that this statement executes successfully, however only 87 odd rows are actually created. I then try running the code to insert a single instance of the previously excluded rows and it enters an endless loop - I can, however, create the row directly in the database. It's as if the PHP code recognises that the row has been created when in fact it hasn't.

Any ideas?

Upvotes: 2

Views: 456

Answers (1)

echo_Me
echo_Me

Reputation: 37233

you have to define $rows_inserted before the while.

 $rows_inserted = 0;
 while($rows_inserted < 100) {
 ....

You are inserting 100 rows with 100 different $number, so you have to make sure that random can repeat some values example 5012, 5060, 5012 ..., if your column name has a unique constraint in it that it will not save the duplicate numbers.

You had 87 values saved means you got 13 duplicated not saved. So either:

1- get unique $number like that

$number = 5001 + $rows_inserted ;
mysqli_query($con,"INSERT INTO cmssps_card_codes (id, parent_id, name, status) 
                   VALUES ('', '', '$number', '0')");

2- or change your column name and remove the UNIQUE constraint there to accept duplicate values.

Upvotes: 1

Related Questions