PHP file to automatically import .sql file to database

Im looking to create a simple PHP file that I can run as a cron job that will take a local .sql file and erase everything in an existing local DB and import the contents of the .sql file.

Check out this thread to see what I mean:
http://forums.digitalpoint.com/showthread.php?t=1105674

If anyone could help me out on this, Id really appreciate it.

 

So you like want to restore some default tables every so often.

What part of the answer you got in the thread did you not understand? What is your level of experience with php and mysql?

 

Yes, a locally stored .sql file is updated every day via a cron job from another server, overwritten with the same filename everyday. I just want to import that locally stored .sql file into a local database everyday to keep it updated.

 

just create a php file that uses sql commands to do it and just have the sql read and execute...
If you dont know the sql commands let me know

 

Yeah thats the prob. I dont know how to write the sql commands in the php file. If I get a good start, I can figure it out.

 

This code uses http://adodb.sourceforge.net
Unfortunately it can't handle every type of sql statement; UPDATE is not supported..

function executeSqlFile ($dbConn, $sqlFile) {
	$fncn = "lib_mediaBeez.php::executeSqlFile";
	$db_error = false;
	if (file_exists($sqlFile)) {
		$fd = fopen($sqlFile, 'rb');
		$restore_query = fread($fd, filesize($sqlFile));
		fclose($fd);
	} else {
		return "$fncn : file does not exist: $sqlFile";
	}

	$sql_array = array();
	$sql_length = strlen($restore_query);
	$pos = strpos($restore_query, ';');
	for ($i=$pos; $i<$sql_length; $i++) {
		if ($restore_query[0] == '#') {
			$restore_query = ltrim(substr($restore_query, strpos($restore_query, "\n")));
			$sql_length = strlen($restore_query);
			$i = strpos($restore_query, ';')-1;
			continue;
		}
		if ($restore_query[($i+1)] == "\n") {
			for ($j=($i+2); $j<$sql_length; $j++) {
				if (trim($restore_query[$j]) != '') {
					$next = substr($restore_query, $j, 6);
					if ($next[0] == '#') {

						// find out where the break position is so we can remove this line (#comment line)
						for ($k=$j; $k<$sql_length; $k++) {
							if ($restore_query[$k] == "\n") break;
						}
						$query = substr($restore_query, 0, $i+1);
						$restore_query = substr($restore_query, $k);
						// join the query before the comment appeared, with the rest of the dump
						$restore_query = $query . $restore_query;
						$sql_length = strlen($restore_query);
						$i = strpos($restore_query, ';')-1;
						continue 2;
					}
					if ($next[0].$next[1] == "/*") {
						// find out where the break position is so we can remove this line (#comment line)
						for ($k=$j; $k<$sql_length; $k++) {
							if ($restore_query[$k].$restore_query[$k+1] == "*/") break;
						}
						$query = substr($restore_query, 0, $i+1);
						$restore_query = substr($restore_query, $k+2);
						// join the query before the comment appeared, with the rest of the dump
						$restore_query = $query . $restore_query;
						$sql_length = strlen($restore_query);
						$i = strpos($restore_query, ';')-1;
						continue 2;
					}
					break;
				}
			}
			if ($next == '') { // get the last insert query
				$next = 'insert';
			}
			if ( (eregi('delete', $next)) || (eregi('create', $next)) || (eregi('insert', $next)) || (eregi('drop t', $next)) || (eregi('alter', $next)) ) {
				$next = '';
				$sql_array[] = substr($restore_query, 0, $i);
				$restore_query = ltrim(substr($restore_query, $i+1));
				$sql_length = strlen($restore_query);
				$i = strpos($restore_query, ';')-1;
			}
		}
	}

	for ($i=0; $i < count($sql_array); $i++) {
		$q = $dbConn->execute ($sql_array[$i]);
		if (!$q) {
			$e = $dbConn->ErrorMsg();
			return (array(
					"culprit" => $sql_array[$i],
					"errorMsg" => $e
			));
		}

	}
	return true;
}

function getADODBconnection ($fetchMode, $debug) {
	$ADODB_FETCH_MODE = $fetchMode;
	$ADODB_COUNTRECS = false;
	$ADODB_CACHE_DIR = MB_CODE_PATH."cache/adodb/";
			
	$r = ADONewConnection (MB_DB_SERVER_TYPE); 
	$r->debug = $debug;
	if ($debug) $r->LogSQL();
	if (!$r->connect (MB_DB_SERVER, MB_DB_USERNAME, MB_DB_PASSWORD, MB_DB_DBNAME)) return false;
	$r->SetFetchMode ($fetchMode);
	return $r;
}

executeSqlFile (getADODBconnection(), '/some/path/somefile.sql');

Code (markup):

 

// connect to the DB

// If exists Drop each existing table

// create new tables

//insert the new data into those tables


Depending on the number of tables I would guess you are looking at a few hundred lines of code. Most of which would be repeating for each step.

I wouldn't stand a chance of writing all that.

Can you not secure a copy of the script that updates the actual servere?

 

Man, I had no clue it would be so complicated.

If exists Drop each existing table, create new tables and insert the new data into those tables is all included in the .sql file. All I need to do is

//connect to DB
//import SQL file

 

dude then my function will probably work for you.. no UPDATEs after DROPs
you just have to plug-in adodb

 

A couple of questions.

1. Is there any chance of the local file being switched to CSV instead of SQL ?
2. Is the MySQL server on the same machine as Apache/PHP. If you enter "localhost" when specifying a database server for programs the answer is yes, if you enter an address such as "secureserver.databasehost.com" the answer is no.
3. How large is the SQL file you're importing daily, and will that size increase ?

 

I am not sure if it can be updated automatically, but I was trying the same thing and never found a way to have it run the update on its own. So I set it to run the update every time a certain page loaded. It sounds like alot but really not much code at all. You need to drop the table create the table the insert the data into the newly created table.

Its a bit sloppy and there is probably a better way to do it, but try this.

<?php

  $dbhost = 'localhost';
  $dbuser = 'username';
  $dbpass = 'password';
  $dbname = 'database';
  $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db($dbname);
  
  // Dump table.
  $query = "DROP TABLE table_name";
  $result = @mysql_query ($query);  //runs the query
  
  if (mysql_affected_rows() == 1) {  //if it ran ok.
    
	echo '<p>The database was updated</p>';
	exit();
		
	} else {  // if did not run ok.
	  $message = '<p>The database was not updated</p>';
	}  // end table dump.
	
  // Create table call_in.
  $query = "CREATE TABLE table_name (column_names) PRIMARY KEY (`column_name`)";
  $result = @mysql_query ($query);  //runs the query
  $query = "LOAD DATA LOCAL INFILE 'c:/file.sql' INTO TABLE table_name FIELDS TERMINATED BY ','";
  $result = @mysql_query ($query);  //runs the query
	
  mysql_close();  // close the database connection.
?>

Code (markup):

 

is it a line delimited sql file?