Here is a simple but handy script that will create a test database with N number of records in two tables. The first table contains a simple ID, a CHAR field and a TEXT field. The second table contains an id field and a BLOB field. later this script may become a class for testing various database queries but for now, is a simple by useful tool to create test data.
The script uses PDO to create two tables and then fills these tables with data. The second table creates a 200x200 PNG image and inserts it as BLOB data.
<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'db_username';
/*** mysql password ***/
$password = 'db_password';
/*** database name ***/
$dbname = 'test_db';
/*** number of records ***/
$num_records = 100000;
/*** table 1 ***/
$table1 = 'table1';
$table2 = 'table2';
/*** the table1 char field text ***/
$table1_char = 'Table One Char Text.';
/*** this is the text for the TEXT field ***/
$table1_text = 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industrys standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.';
/*** blob image created by imagick ***/
$blob_data = createBlob();
function createBlob()
{
/*** a new imagick object ***/
$im = new Imagick();
$im->newImage( 200, 200, "black", "png" );
/* Trim the image. */
return $im;
}
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
/*** begin timing script ***/
$time_start = microtime_float();
try
{
$dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
/*** echo a message saying we have connected ***/
echo 'Connected to database<br />';
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** begin transaction ***/
$dbh->beginTransaction();
/*** create a table ***/
$sql = "CREATE TABLE $table1 (
table1_id INT(3) NOT NULL default '0',
table1_char char(20) NOT NULL default '',
table1_text TEXT NOT NULL default '',
PRIMARY KEY (table1_id)
)";
$dbh->exec($sql);
$sql = "CREATE TABLE $table2 (
table2_id INT(3),
table2_blob BLOB,
PRIMARY KEY (table2_id)
)";
$dbh->exec($sql);
/*** echo a message to say the table was created ***/
echo 'Table created successfully<br />';
/*** create records for the tables ***/
for( $i=1; $i<$num_records; $i++)
{
/*** table one data ***/
$stmt = $dbh->prepare("
INSERT INTO $table1
(table1_id, table1_char, table1_text)
VALUES
(:table1_id, :table1_char, :table1_text)
");
$stmt->bindParam(':table1_id', $i, PDO::PARAM_INT);
$stmt->bindParam(':table1_char', $table1_char, PDO::PARAM_STR);
$stmt->bindParam(':table1_text', $table1_text, PDO::PARAM_STR);
$stmt->execute();
/*** table two data ***/
$stmt = $dbh->prepare("
INSERT INTO $table2
(table2_id, table2_blob)
VALUES
(:table2_id, :table2_blob)
");
$stmt->bindParam(':table2_id', $i, PDO::PARAM_INT);
$stmt->bindParam(':table2_blob', $blob_data, PDO::PARAM_LOB);
$stmt->execute();
}
/*** commit the transaction ***/
$dbh->commit();
/*** end script timer ***/
$time_end = microtime_float();
/*** do the math ***/
$db_time = $time_end - $time_start;
/*** echo a message to say the records were created ***/
echo 'Successfully created '.$num_records.' records in '.$db_time.' seconds.<br />';
}
catch(PDOException $e)
{
/*** echo the sql statement and error message ***/
echo $e->getMessage();
$dbh->rollback();
}
?>