Page 1 of 1

How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:03 am
by mc3
Hello,
I'm trying to write PHP that will create a table with a name provided by an HTML form (easy). And then I want to populate the table with 5000 rows which contain random values for the user and pw fields.
Here's what I have so far:

Code: Select all

 
mysql_query("CREATE TABLE $album (
 id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 user VARCHAR( 255 ) CHARACTER SET ASCII COLLATE ascii_bin NOT NULL ,
 pw VARCHAR( 255 ) CHARACTER SET ASCII COLLATE ascii_bin NOT NULL ,
) ENGINE = MYISAM CHARACTER SET ASCII COLLATE ascii_bin")
echo "Album successfully added!";
 
I'm stuck with how to insert rows with random data for the user and pw fields. I've found code like this for generating randomness:

Code: Select all

SUBSTRING(MD5(RAND()) FROM 1 FOR 6)
But not sure how to use this to achieve what I want. Should I first insert blank rows and then update them? Or can I do it all in one step?

Any help is appreciated!

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:12 am
by dhrosti
mc3 wrote:Hello,
I'm trying to write PHP that will create a table with a name provided by an HTML form (easy). And then I want to populate the table with 5000 rows which contain random values for the user and pw fields.
Here's what I have so far:

Code: Select all

 
mysql_query("CREATE TABLE $album (
 id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 user VARCHAR( 255 ) CHARACTER SET ASCII COLLATE ascii_bin NOT NULL ,
 pw VARCHAR( 255 ) CHARACTER SET ASCII COLLATE ascii_bin NOT NULL ,
) ENGINE = MYISAM CHARACTER SET ASCII COLLATE ascii_bin")
echo "Album successfully added!";
 
I'm stuck with how to insert rows with random data for the user and pw fields. I've found code like this for generating randomness:

Code: Select all

SUBSTRING(MD5(RAND()) FROM 1 FOR 6)
But not sure how to use this to achieve what I want. Should I first insert blank rows and then update them? Or can I do it all in one step?

Any help is appreciated!
you could use...

Code: Select all

INSERT INTO $album 
(user, pw) 
    VALUES 
(SUBSTRING(MD5(RAND()) FROM 1 FOR 6), SUBSTRING(MD5(RAND()) FROM 1 FOR 6)),
(SUBSTRING(MD5(RAND()) FROM 1 FOR 6), SUBSTRING(MD5(RAND()) FROM 1 FOR 6)), etc, etc, etc
how ever many times you need rows.

I cant think of how to loop it X number of times using sql though.

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:19 am
by mc3
Fantastic! Thanks.

Now I just need to figure out an elegant way of doing this 5000 times....Doesn't seem like I should need to have 5000 lines of code for it...

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:22 am
by Stryks
Well ... one way ...

Code: Select all

for($i=0;$i<=5000;$i++){
    // ... run query
}

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:24 am
by dhrosti
yea that's what i'd do, just have to put up with 5000 sql queries though.

but then you might aswell do the random string generation using php too.

Code: Select all

<?php
 
// Create table stuff here
// ...
 
// Insert rows
$sql = 'INSERT INTO $album (user, pw) VALUES ';
 
for ($i=0; $i<=5000; $i++) :
    
    $user = substr(md5(rand()), 0, 5);
    $pw = substr(md5(rand()), 0, 5);
 
    $sql .= "('$user', '$pw'), ";
 
endforeach;
 
$sql = substr($sql, 0, -2);
 
mysql_query($sql);
 
?>
EDIT: I altered the above code so it now only uses 1 query, the for loop just builds up the one sql string

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:49 am
by mc3
Hmmmm...seems like this should work but it's not. It's printing "record added" 5000 times, which is a good sign that it's working but my table is not being updated.

Thoughts?

Code: Select all

function insert_random($num_rows = 5000)
{
   
   for($i = 0; $i < $num_rows; $i++)
   {
    mysql_query("INSERT INTO $album
    (user, pw)
    VALUES
    (SUBSTRING(MD5(RAND()) FROM 1 FOR 12), SUBSTRING(MD5(RAND()) FROM 1 FOR 12))");
   echo "record added";
   }
   
}
 
insert_random();
 

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 10:54 am
by dhrosti
$album doesn't exist within the scope of the function, pass the $album variable as a parameter too...

Code: Select all

function insert_random($album, $num_rows = 5000) {
 
    // bla, bla, bla
 
}
 
insert_random($album);

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 11:14 am
by mc3
Awesome. Thanks!

Re: How to insert several rows with random data?

Posted: Wed Jul 16, 2008 1:48 pm
by zainal_se
Doing multiple inserts in MYSQL is a lot better than doing multiple mysql_query on your PHP. It is more efficient in all angles.

I'd suggest you use the first solution being introduced to you

Code: Select all

 
$iteration = 5000;
$sql = "INSERT INTO my_table (`column1`, `column2`) VALUES ";
for($i =0; $i < $iteration; $i++) {
   $sql .= "(SUBSTRING(MD5(RAND()) FROM 1 FOR 6), SUBSTRING(MD5(RAND()) FROM 1 FOR 6)";
   if ($i < ($iteration - 1)) {
        $sql .= ",";
   }
   $sql .= " ";
}
 
@mysql_query($sql); // minimize executing this function to save resources