Running MySQL *.sql files in PHP
. I never did get useful output, but followed some suggestions on another thread and finally got it all working. I switch to the —option=value format for the commands and used —execute=»SOURCE . » instead of < to execute the file.
Also, I never got a good explanation of the difference between shell_exec() and exec() .
The difference between shell_exec() and exec() is that shell_exec returns all of the output stream as a string. exec returns the last line of the output. via stackoverflow.com/a/7093892/557358
15 Answers 15
This question comes up from time to time. There’s no good solution for running a .sql script directly from PHP. There are edge cases where statements common in a .sql script can’t be executed as SQL statements. For example, the mysql tool has builtin commands that are not recognized by the MySQL Server, e.g. CONNECT , TEE , STATUS , and DELIMITER .
So I give +1 to @Ignacio Vazquez-Abrams’s answer. You should run your .sql script in PHP by invoking the mysql tool, for instance with shell_exec() .
$command = "mysql --user= --password='' " . "-h -D < "; $output = shell_exec($command . '/shellexec.sql'); See also my answers to these related questions:
I am trying the shell_exec() route, but I am not finding examples specifying a file to execute. This is what I have so far: shell_exec('mysql' . ' -u ' . $vals['db_user'] . ' -p ' . $vals['db_pass'] . ' -D ' . $vals['db_name']);
I thought I would offer some insight into anyone reading this who couldn't get it to work, One issue that can occur with shell_exec is that mysql is not on the path. This is easy to test by running the command mysql in the console of the affected machine. In this case, MySQL would need to be added to the path or an alternate approach would be required.
@AaronNewton, good idea. Path-related issues are pretty basic, but I forget how many people still struggle with them.
$commands = file_get_contents($location); $this->_connection->multi_query($commands); Your solution works for mysqli . I am using PDO. Your answer prompted me to do a search, and I found this: stackoverflow.com/questions/6346674/…
I'm using mysqli and can't get this solution to work. $commands just comes back as an empty string. For $location I have tried both a relative local path (the script file is in the same folder as the php file) and a fully qualified hyperlink. I get the same result either way.
This seems to fail for certain types of advanced queries, such as creating a procedure and executing it.
You'll need to create a full SQL parser for this. I recommend you use the mysql command line tool for this instead, invoking it externally from PHP.
function run_sql_file($location) < //load file $commands = file_get_contents($location); //delete comments $lines = explode("\n",$commands); $commands = ''; foreach($lines as $line)< $line = trim($line); if( $line && !startsWith($line,'--') )< $commands .= $line . "\n"; >> //convert to array $commands = explode(";", $commands); //run commands $total = $success = 0; foreach($commands as $command) < if(trim($command))< $success += (@mysql_query($command)==false ? 0 : 1); $total += 1; >> //return number of successful queries and total number of queries found return array( "success" => $success, "total" => $total ); > // Here's a startsWith function function startsWith($haystack, $needle) There are edge-cases where this will fail, and not with an error-message but (potentially) unexpected behavior. For example, multi-line string literals in your SQL statements could start with the string '--' , or string literals might contain ; characters. If you're going to go this route, you really should use a full SQL parser.
I have never had to use it but the mysqli class has a multi_query method:
I know I'm pretty late to the party but PHP Mini Admin has been a lifesaver on a couple of occasions. It's basically a "lite" PHPMyAdmin all contained in one file so no need for complicated installs, just upload it and log in. Simples!
Don't forget about phpMyAdmin. Pretty solid interface for interacting with MySQL.
I don't know if it solves your problem, since I don't know if you can interact with it directly from code, but just wanted to throw it out there.
Good suggestion too. Parsing mySQL dumps in pure PHP sucks, phpMyAdmin takes the pain out of it (is not automatable, though).
You can use this script to run MySQL script files. You'll need to set $hostName, $userName, $password, $dataBaseName, $port and $fileName of course.
2) < $delimiter = $matches[3][0]; $script = substr($script, $matches[3][1] + 1); >else < if (strlen($statement = trim(substr($script, 0, $matches[0][1])))) < $result[] = $statement; >$script = substr($script, $matches[0][1] + 1); > > return $result; > function executeScriptFile($fileName, $dbConnection) < $script = file_get_contents($scriptFleName); $statements = parseScript($script); foreach($statements as $statement) < mysqli_query($dbConnection, $statement); >> $hostName = ''; $userName = ''; $password = ''; $dataBaseName = ''; $port = ''; $fileName = ''; if ($connection = @mysqli_connect($hostName, $userName, $password, $dataBaseName, $port)) < executeScriptFile($fileName, $connection); >else
When writing an answer to your own question, could you please post an explanation as well as code. Without an explanation we still don't know why the problem was fixed without studying a lot of useless code.
It wasn't my own question. TC asked "I would like to execute these files from PHP". I gave script how to do this. If you are unable to read such small piece of code (there is not much to study, it's too small and obvious), and if you don't need solution - why you can's just skip my answer instead of being such a rude? 🙂
I'm not trying to be rude. See that down-vote above (someone else put it there in case you think it's me)? I'm trying to tell you why you are getting it and suggesting what you can do to get upvotes instead. I'm only trying to help. also it was edited edited 3 min ago and it now looks better. Unfortunately for newcomers users here expect a fantastic answer (and question) as well as working code. It's a knack. Once you have it you will be able to do good answers (and questions) and get upvotes.
I hope I did not offend, I do not mean to. As I said above I'm trying to get you some reputation by helping you improve your answers and questions.
I created a migration script with multi_query . It can process mysqldump output and phpmyadmin exports without mysql command line tool. I also made some logic to process multiple migration files based on timestamp stored in DB like Rails. I know it needs more error handling but currently does the work for me.
I think if you don't process user input with it only scripts made by developers or export tools you can use it safely.
Here is my solution and the below code explains what is does. The principle is to read the file line by line, build a query and execute each of them. I saw many solutions using the "file_get_contents" which is not a good solution because it could cause a buffer issue as it read the whole file contents to string variable. My solution takes also into account TRIGGERs' queries. There's no array allocation, comment and empty lines are stripped.
catch (PDOException $ex) < return null; >> /** * Runs SQL queries from file */ function exec_sql_queries_from_file($script_file, $db_host, $db_user, $db_password, $db_name) < // to increase the default PHP execution time set_time_limit ( 60 ); // Max time = 60 seconds // Connect to database $connection = get_db_connection($db_host, $db_user, $db_password, $db_name); // If the connection is acquired if($connection != null)< // Open sql file $f = fopen($script_file, 'r'); // sql query $query = ''; // Default delimiter for queries $delimiter = ';'; // read line by line while (!feof($f)) < $line = str_replace(PHP_EOL, '', fgets($f)); // read a line and remove the end of line character /* if the current line contains the key word 'DELIMITER'. Ex: DELIMITER ;; or DELIMITER $$ * mostly used for TRIGGERS' queries */ if(strpos($line, 'DELIMITER') !== false) < // change the delimiter and read the next line $delimiter = str_replace('DELIMITER ', '', $line); continue; >// Consider the line as part of a query if it's not empty and it's not a comment line if (!empty($line) && !starts_with($line, '/*') && !starts_with($line, '--')) < // the query hasn't reach its end: concatenate $line to $query if $line is not a delimiter $query .= $line !== $delimiter ? $line : ''; // if the current line ends with $delimiter: end of current query if (ends_with($line, $delimiter)) < // exec the query $connection->exec($query) or die($connection->errorInfo()); // start new query $query = ''; > > > fclose($f); > > /** * Starts with function */ function starts_with($haystack, $needle) < return $haystack=== $needle ? stripos($haystack, $needle) === 0 : false; > /** * Ends with function */ function ends_with($haystack, $needle) < $pos = stripos($haystack, $needle); return $pos === FALSE ? FALSE : substr($haystack, $pos) === $needle; Nice idea, but with some mistakes. Replaceing eol with nothing. Search by ; for EOL or end of command (this can also be used in comments) aso. But again, the idea is great
To execute table generation from within the application, you may want to create a php file that will do just that when you run it.
$hostname = "localhost"; $database = "databasename"; $username = "rootuser"; $UserPassword = "password"; $myconnection = mysql_pconnect($hostname, $username , $UserPassword) or trigger_error(mysql_error(),E_USER_ERROR); mysql_connect($hostname , $username , $UserPassword ) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); if ( !$myconnection ) < echo "Error connecting to database.\n";>$userstableDrop = " DROP TABLE IF EXISTS `users`"; $userstableCreate = " CREATE TABLE IF NOT EXISTS `users` ( `UserID` int(11) NOT NULL, `User_First_Name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15" ; $userstableInsert = "INSERT INTO `users` (`UserID`, `User_First_Name`) VALUES (1, 'Mathew'), (2, 'Joseph'), (3, 'James'), (4, 'Mary')"; $userstableAlter1 = "ALTER TABLE `users` ADD PRIMARY KEY (`UserID`)"; $userstableAlter2 = " ALTER TABLE `users` MODIFY `UserID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=15"; $createDb_sql = $userstableDrop; $insertSite = mysql_query($createDb_sql); $createDb_sql = $userstableCreate; $insertSite = mysql_query($createDb_sql); $createDb_sql = $userstableInsert; $insertSite = mysql_query($createDb_sql); $createDb_sql = $userstableAlter1; $insertSite = mysql_query($createDb_sql); $createDb_sql = $userstableAlter2; $insertSite = mysql_query($createDb_sql); echo "Succesful!"; mysql_close($myconnection ); Execute a *.sql file using php
I need to execute a .sql file which has about 48 tables to create. It is consisting of comments and sql commands which are ending in a ";". Is there a way to run these sql commands taking them to a single string and at once. I mean I need to run the whole file at once using php. I can execute that file line by line using mysql_query function. But I need to do is to execute all of them at once as same as in phpmyadmin. Is there a way to do it. I need to do this using php. Can anyone help me please?
3 Answers 3
An alternative would be to use the command line client:
mysql -u USERNAME -p DATABASENAME < FILE.SQL You can probably use that with exec and/or system , but then you have to provide the password after -p . The above command will prompt you for it.
You can explode your queries
I have written a very generic function that takes a $file as input and will execute it against a mysql database.
The code below was written inside codeigniter; but it would be very easy to modify for any framework. This code tries to be as portable as possible so it can work in many environments. It doesn't do any fancy string parsing or splitting; instead it relies on heavy use of mysql or php built in methods.
//Default paths $mysql_paths[] = '/Applications/MAMP/Library/bin/mysql'; //Mac Mamp $mysql_paths[] = 'c:\xampp\mysql\bin\mysql.exe';//XAMPP $mysql_paths[] = '/usr/bin/mysql'; //Linux $mysql_paths[] = '/usr/local/mysql/bin/mysql'; //Mac $mysql_paths[] = '/usr/local/bin/mysql'; //Linux $mysql_paths[] = '/usr/mysql/bin/mysql'; //Linux $database = escapeshellarg($this->db->database); $db_hostname = escapeshellarg($this->db->hostname); $db_username= escapeshellarg($this->db->username); $db_password = escapeshellarg($this->db->password); $file_to_execute = escapeshellarg($file); foreach($mysql_paths as $mysql) < if (is_executable($mysql)) < $execute_command = "\"$mysql\" --host=$db_hostname --user=$db_username --password=$db_password $database < $file_to_execute"; $status = false; system($execute_command, $status); return $status == 0; >> if ($this->db->dbdriver == 'mysqli') < //2nd method; using mysqli mysqli_multi_query($this->db->conn_id,file_get_contents($file)); //Make sure this keeps php waiting for queries to be done do<> while(mysqli_more_results($this->db->conn_id) && mysqli_next_result($this->db->conn_id)); return TRUE; > //3rd Method Use PDO as command. See http://stackoverflow.com/a/6461110/627473 //Needs php 5.3, mysqlnd driver $mysqlnd = function_exists('mysqli_fetch_all'); if ($mysqlnd && version_compare(PHP_VERSION, '5.3.0') >= 0) < $database = $this->db->database; $db_hostname = $this->db->hostname; $db_username= $this->db->username; $db_password = $this->db->password; $dsn = "mysql:dbname=$database;host=$db_hostname"; $db = new PDO($dsn, $db_username, $db_password); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0); $sql = file_get_contents($file); $db->exec($sql); return TRUE; > return FALSE; > How to Import MySQL Database from SQL File using PHP
Importing SQL script via programming will be useful when we need to create database structure dynamically. For example, if we provide APP or plugin to download and install from online, the dynamic SQL import will be used to setup the application database at the time of installing the APP or plugin.
To import SQL file in the database, you have to login to your hosting server or phpMyAdmin. Also, you can import the database from PHP script without login to your hosting server or phpMyAdmin.
Import database from PHP script is extremely helpful when you need to enable the client to import the database from your web application. A backup of the database should be taken for importing tables in MySQL database.
In this tutorial, we are going to import a SQL file into a database using PHP programming. In this example, we have a file containing SQL for creating contact table and inserting data. The PHP code parses the file line by line and extracts the query statements to execute using PHP MySQL function.
importDatabaseTables() work in PHP, you can import tables in the database from a .sql file. The following given parameters are required to import or rstore MySQL database using PHP.
- $dbHost – Required. Specifies the host name of the database.
- $dbUname – Required. Specifies the database username.
- $dbPass – Required. Specifies the database password.
- $dbName – Required. Specifies the database name in which you wants to import.
- $filePath – Required. Specifies the path of the SQL file from where the tables will be imported.