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