mysql INSERT INTO loop not inserting some rows

Question

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?

You'll have to show us the table definition, to see if there is some constraint you're trying to violate. Expect to have some duplicate $number's in there. — Phil Perry
– Phil Perry, Commented Mar 3, 2014 at 15:25
if you generate 100 random numbers in an interval of 100 (e.g. 1 .. 100), you are unlikely to get 100 unique numbers! There will be duplicates. That is why you get 87 rows from 100 random numbers. — Ryan Vincent
– Ryan Vincent, Commented Mar 3, 2014 at 16:58

Alex · Accepted Answer · 2020-09-10 03:26:17Z

1

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.

edited Sep 10, 2020 at 3:26

Alex

1,6101 gold badge16 silver badges27 bronze badges

answered Mar 3, 2014 at 15:25

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

contool Over a year ago

Thanks. This seemed to do the trick. It was as others have said too, an issue with duplicates being created and then just skipped.

Collectives™ on Stack Overflow

mysql INSERT INTO loop not inserting some rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related