MySQL: automated data upload

Working on my recent project I have had to create a database with several tables and populate them with pretty large amount of data. After a few not particularly successful attempts I have finally managed to automate the whole process. But let’s start from the beginning.

It happens that for this particular project I am using MySQL, but since I like making code as reusable as possible, all queries to the database are done using the PHP Data Objects (PDO) extension. Also, I do not like using superuser account with system wide access for everyday administration of a database of a particular project, so first I am going to create an administrator account for that particular database.

try {
    $mysql = new PDO("mysql:host=localhost", $mysql_root, $mysql_root_password);
    $mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE IF NOT EXISTS $mysql_database;
    CREATE USER '$mysql_user'@'localhost' IDENTIFIED BY '$mysql_user_password';
    GRANT ALL ON $mysql_database.* TO '$mysql_user'@'localhost';
    FLUSH PRIVILEGES;";
    $mysql->exec($sql);

    print("Job done.");

} catch(PDOException $e) {
    die("DATABASE ERROR: ". $e->getMessage());
}

To make code more concise I will omit the try-catch blocks, but they are there, so remember to add them if you use following code.

OK, we have a database and its administrator/user. It is time to create a table.

$mysql = new PDO("mysql:dbname=$mysql_database;host=localhost", $mysql_user, $mysql_user_password);
$mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE IF NOT EXISTS $table_name (
id INT(9) AUTO_INCREMENT PRIMARY KEY NOT NULL,
column1 VARCHAR(5) COLLATE utf8_unicode_ci NOT NULL,
column2 VARCHAR(10) COLLATE utf8_unicode_ci NOT NULL,
column3 VARCHAR(50) COLLATE utf8_unicode_ci NOT NULL);";
$mysql->exec($sql);

And now starts the fun. A query for inserting data to the table looks like this:

$sql = "INSERT INTO $table_name (column1, column2, column3) 
VALUES ('value1', 'value2', 'value3');";

Simple, but if you have hundreds of rows of such data, for example, it is not fun any more. My first attempt to handle this situation was looping over a multidimensional array.

$data = array( array('value1', 'value2', 'value3'),
        array('value4', 'value5', 'value6'),
        array('value7', 'value8', 'value9'),
        ...
        array('valueX', 'valueY', 'valueZ'));
$sql = "INSERT INTO $table_name (column1, column2, column3) VALUES (";
foreach($data as $row) {
    $mysql->exec($sql . "'" . implode("','",$row) . "')");
}

It works but is not the most efficient solution, if I can use such euphemism. Fortunately there is something better. We can load the data into the table from a CSV file, but it requires enabling LOAD LOCAL INFILE when constructing a new database handle and can only be used in the driver_options array. Also you have to remember that the data in CSV file have to be properly formatted so they end up in the right columns of the table in the database. The code is as follows:

$mysql = new PDO("mysql:dbname=$mysql_database;host=localhost", $mysql_user, $mysql_user_password,array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));
$mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE $table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
(column1, column2, column3);";
$mysql->exec($sql);

If your CSV file was created in MS Windows environment then I would suggest change lines termination to ‘\r\n’ and if the first line contains column names then skip it adding IGNORE 1 LINES.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.