SQL Import from a file using PHP

March 4, 2009 | PHP, Time Savers, Tutorials, Wordpress | 3 comments

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: [download#8#size#nohits]

 

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);
				}
			}
		}
	}
}

A Donate Button!

3 Comments

  1. Daniel Kesberg

    exactly what i needed .. thanks šŸ™‚

    Reply
  2. Mysterio

    Thank you! To bad I didn’t found this four years before!

    Reply
  3. Wakko

    Thank you very much for this code It really helps when you need to load several tables.

    Reply

Trackbacks/Pingbacks

  1. SQL Import from a file using PHP | PHP-Blog.com - [...] more: SQL Import from a file using PHP Related ArticlesBookmarksTags PHP PHP is a computer scripting language. Originally [...]

Submit a Comment

Your email address will not be published. Required fields are marked *

CommentLuv badge

Stay in touch!

Page Builder Cloud

Page Builder Cloud

A truly universal template library for WordPress Page Builders.

Page Builder Recommendation

Elementor Banner

We are BIG fans of the Elementor page builder. Give it a look!

About this site and Sean Barton

Picture of Sean
Sean Barton is a Freelance PHP Website Developer in Crewe, Cheshire. He is a Wordpress and CMS/Framework specialist and Co-Founder of Page Builder Cloud.
This site was set up in 2008 as a tutorial and scripting resource for the PHP language and Wordpress.
Find out more about Sean on the About Me page or use the Hire Me page to get in touch. For more information about Sean's work take a look at the Portfolio

Our Services

  • Wordpress plugin/theme development
  • Divi specialist
  • Ecommerce (Woocommerce, WPSC, Shopify, Magento)
  • PSD to Wordpress theme conversion (Responsive)
  • Website design work (Banners, Logos, Full Site, etc)
  • Website analysis (security, usability, SEO)
  • API Integrations (InfusionSoft, SalesForce, Ontraport, Customer Thermometer, etc..)
  • Wordpress consultancy & expert advice
  • Crisis support
  • Website hosting

The main services offered are Wordpress based although we do a great deal of technical programming for bespoke systems. From troubleshooting, extending frameworks, finding bugs to writing them from scratch.

Find out more by looking through our past projects or get a quote.

Be the first to hear about new products/updates!

This is a mailing list for those people interested in being toldĀ when we releaseĀ a new product (Divi plugin or Theme).

We shall also use this list to let you know about product updates and releases.

You have Successfully Subscribed!