Insert value into database 1000x times

Question

I would like to enter some value into database 1000x times.

Example:

      <?php
     $n = rand(1,30000);
    $n2 = $n."20";
for($i=1;$i=1000;$i++){
mysql_query("INSERT INTO reedem(5) VALUES ('".$n2."')");
}
?>

How could I do that correctly?

Answer 1

Your loop is incorrect:

for($i=1;$i=1000;$i++){
         ^^^^^^^

That should be $i == 1000 - you're ASSIGNING 1000 to $i, and turning this into an infinite loop - since `$i = 1000 will ALWAYS be a "true" value, the loop never ends.

And your INSERT query is incorrect:

mysql_query("INSERT INTO reedem(5) VALUES ('".$n2."')");
                                ^---

A field cannot be named with just a number, unless it's escaped:

mysql_query("INSERT INTO reedem(`5`) VALUES ('".$n2."')");
                                ^-^---

and even then it's still not a good idea to do so.

Answer 2

Prepared statements are useful when you need to run the same query multiple times.

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
$stmt = $mysqli->prepare("INSERT INTO redeem (myColumnName) VALUES (?)");
$n = rand(1,30000) . '20';
$stmt->bind_param("i",$n);

foreach(range(1,1000) as $i) {
    $stmt->execute();
}

$stmt->close();

if you want to insert 1000 different random values (as opposed to the same random value 1000 times) move the code that defines $n inside the loop

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
$stmt = $mysqli->prepare("INSERT INTO redeem (myColumnName) VALUES (?)");
$stmt->bind_param("i",$n);

foreach(range(1,1000) as $i) {
    $n = rand(1,30000) . '20';
    $stmt->execute();
}

$stmt->close();

Answer 3

Seems like you want to insert a random value between 120 and 3000020 into a field of a table. You can do that with one update:

UPDATE table SET field = ROUND(30000*RAND()+20)

So you wouldn't need INSERT if the table rows already exist. The table could be users, for instance. If you really need to do inserts, and you cannot use updates, then you can do multiple inserts in one command:

INSERT INTO table (column) 
VALUES (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20)),
       (ROUND(30000*RAND()+20));

So with 10 inserts you only need to loop a 100 times, which will be a lot quicker.

As others have said, you shouldn't use the mysql extension anymore. Go to mysqli or PDO. Prepared statements can also help to make things more efficient.

Answer 4

When you want to insert many identical values, you can use insert-select, where select selects 1000 rows. You could use a table with 1000 rows, or a larger table from which you select the first 1000 rows, or you can generate 1000 rows on the fly using connect by as in:

select level
from dual
connect by level <= 100
;

This gives you 100 rows with one column, whose values are the numbers 1 .. 100. (This is oracle syntax, for an equivalent way of doing this in mySql see here.).

So to insert 1000 rows with a value of 42 into a column xxx.x you'd go

insert into xxx (
    select 42 
    from (
        select level
        from dual
        connect by level <= 100
    )
);