On seeing the title of this post your first uncontrollable outburst may be ‘Why on earth would I want to do that?‘. On further reading, you may decide that PHP is the way to get tables created from within an automated install process (WordPress plugin activation or PHP based software ‘installation’). It may trigger another outburst when you see that the code I am about to paste in is more than a single line long (which would be all it would take using the PHP exec() command).

Why not use the Exec command?

Alot of developers cringe at the thought of the exec command in PHP, others have disabled it due to ‘security issues’. My reason for not using it is because my script doesnt just import SQL. It can do two very important things for me:

• Prioritise the import
• Prefix the table names

Prioritising the import isn’t essential but it means that your SQL file can be a mess of random statements and no matter where the INSERTs are, they will be grouped and run last to allow the CREATE statements a chance to work.

Prefixing the table names is almost self explanatory. Wordress, which I wrote this script for, allows Database prefixes to be used when setting up a blog. It only makes sense that to avoid conflict when more than one blog is in the same database to prefix my tables with the same string.

How does it all work?

For those interested the code above simply does the following:

• Imports the file from an absolute path passed to the function into an array split by line
• Loops through the lines looking for starting words defined as SQL statement keywords in an array and generates a list of complete statements
• Removes all comments as it goes (comment defined as any line starting with ‘–’
• Looks for the first backtick character (`) which MUST be present (at least around the table name) and inserts the WordPress Database Prefix
• Inserts the completed query into an array grouped by those that are INSERT queries and those that aren’t
• Sorts the new array by a priority figure (new numbers can be added to mark muti-level priority if you like)
• Runs the queries

Integration

To integrate it into your WordPress plugin then simply copy/paste the code into yours and pass it an absolute path on plugin activation. I also use it to drop tables on a ‘hard’ deactivation by passing it an SQL file full of DROP commands. To integrate it into your NON WordPress site then simply remove all references to $wpdb and substitute ‘$wpdb->prefix’ with your own string or variable name.

The Code

Here is the code although I have also provided it in download form: MySQL Import Script (1.14 kB)

function mysql_import($filename) {
	global $wpdb;

	$return = false;
	$sql_start = array('INSERT', 'UPDATE', 'DELETE', 'DROP', 'GRANT', 'REVOKE', 'CREATE', 'ALTER');
	$sql_run_last = array('INSERT');

	if (file_exists($filename)) {
		$lines = file($filename);
		$queries = array();
		$query = '';

		if (is_array($lines)) {
			foreach ($lines as $line) {
				$line = trim($line);

				if(!preg_match("'^--'", $line)) {
					if (!trim($line)) {
						if ($query != '') {
							$first_word = trim(strtoupper(substr($query, 0, strpos($query, ' '))));
							if (in_array($first_word, $sql_start)) {
								$pos = strpos($query, '`')+1;
								$query = substr($query, 0, $pos) . $wpdb->prefix . substr($query, $pos);
							}

							$priority = 1;
							if (in_array($first_word, $sql_run_last)) {
								$priority = 10;
							} 

							$queries[$priority][] = $query;
							$query = '';
						}
					} else {
						$query .= $line;
					}
				}
			}

			ksort($queries);

			foreach ($queries as $priority=>$to_run) {
				foreach ($to_run as $i=>$sql) {
					$wpdb->query($sql);
				}
			}
		}
	}
}