PHP MySQL script to create table, insert test entries

29 Mar

Sometimes I need to set up a database/table in MySQL for just testing a few things, and its not that easy to do manually (especially for a large number of entries). So I wrote a quick PHP script to actually do this work for me.

<?php
$num_entries = 200000;
$echo_num_inserts = 50000;
$bulk_inserts = 1;
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";
$dbname = "cake";

$con = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db($dbname, $con);

$result = mysql_query("CREATE TABLE `blogs` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `name` VARCHAR(150) NULL DEFAULT NULL,
  `content` MEDIUMTEXT NULL DEFAULT NULL,
  `user_id` INTEGER NULL DEFAULT NULL,
  `created` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);");

$inserts = "";
foreach (range(1,$num_entries) as $i) {
	if (($i % $echo_num_inserts) == 0) echo "$i done.\n";
	$name = rand_string (20);
        $content = rand_string (120);
	$userid = rand(0,100);
	$inserts .= "(0, '$name', '$content', '$userid', now())";
	if (($i % $bulk_inserts) == 0) {
		mysql_query("INSERT INTO blogs (id, name, content, user_id, created) VALUES $inserts");
		$inserts = "";
	} else {
		$inserts .= ", ";
	}
}

mysql_close($con);

function rand_string( $length ) {
	$chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";	
	return substr(str_shuffle($chars),0,$length);
}

?>

UPDATED:

  • Apr 2 2012: Added bulk insert feature
  • Mar 29 2012: to add a created field to note when it was created.)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: