Create many rows in Mysql with php random number generator

Question

I need to create more than 1000 rows in my mysql database. I have written some code which generate a random number with 5 integers with the number between 2-8.

I want to create a new row in my database with the number generated.

I have all this, but i dont know to make the code remember the values already inserted.

Is there a way to do this without have to store all the values in a array and then make the code check the hole array before inserting new row?

Is there a smarter way?

my code:

$length = 5;

echo '"';
for ($i = 0; $i < $length; $i++) {

    echo generateRandomString('3'). '", "';

}


function generateRandomString($length = 10) {
    $characters = '2345678';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
    return $randomString;
}
echo '"';
?>

how random is it if you care about prior, not sure i get it. why not do it entirely in sql without PHP at all (other than the call to say DO IT ) — Drew
– Drew, Commented Aug 6, 2015 at 12:15
@DrewPierce suggestion is good, I think you have to think about it, you will win a lot of performance going the suggestion way. — Maytham Fahmi
– Maytham Fahmi, Commented Aug 6, 2015 at 12:41

Drew · Accepted Answer · 2015-08-06 12:56:11Z

This is just a quick example, way overkill, you would probably need 1 line in the random section. But it is all I have at the moment and have to head out.

create schema so_gibberish; -- creates database 
use so_gibberish;   -- use it 

-- drop table random;   -- during debug
create table random 
(   id int auto_increment primary key,
    question varchar(50) not null,
    category int not null,
    randomOrder int not null,
    key (category)
);

Create a stored procedure to insert random questions (with a ? at end) Creates 300 at a time when you call it

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
DELIMITER $$ 
drop procedure if exists createRandomQuestions$$ 
-- 17 categories of questions randomly created. yes random word questions and categories.

create procedure createRandomQuestions()
BEGIN
set @i=1;
WHILE @i<=300 DO
insert random (question,category) values ('xxx',1);
SELECT @lid:=LAST_INSERT_ID();  -- use id to seed, next 8 guaranteed different i think

-- programmer should come up with adequate random seed on their own
-- this is WAY overkill but it was what I had at the moment to show you

UPDATE random SET question=concat(
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1), ' ?'
), category=floor(rand()*17+1),randomOrder=0
WHERE id=@lid;
set @i=@i+1;
END WHILE;
END;
$$
DELIMITER ;
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

call it:

call createRandomQuestions();

cleanup:

drop schema so_gibberish;

Good luck.

Ole Haugset · Accepted Answer · 2015-08-06 12:54:38Z

0

You can create an array holding all the numbers you have previously inserted and compare your current number to whats in the array:

$inserted_numbers = array()
if( in_array( $number, $inserted_numbers ) ) {
     // The numbers is already in the array 
} else {
     // NUmber not in array, do stuff
     array_push( $inserted_numbers, $number )
}

answered Aug 6, 2015 at 12:54

Ole Haugset

3,8053 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

Create many rows in Mysql with php random number generator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related