Load and execute SQL files with PHP
How to load a SQL query from a file instead of having an inline query. This is done to help with formatting and easier understanding of the code.
All that is needed is the simple file_get_contents() in the place of the query string.
fetch_all_colors.sql in the sql/ directory
SELECT `color` FROM `objects` ORDER BY `color` DESC;
PHP file running the PDO MySQL query:
$db = new PDO("mysql:host=127.0.0.1;dbname=test;charset=utf8mb4", 'root', ''); $select = $db->query(file_get_contents('sql/fetch_all_colors.sql')); while ($row = $select->fetch(PDO::FETCH_ASSOC)) < echo $row['color'].'
'; > This loads the fetch_all_colors.sql and executes it.
Prevent index and viewing of the SQL files
By using .htaccess and deny from all on SQL only files this prevents the viewing and indexing for the SQL files.
Order allow,deny Deny from all
Better use case:
The example uses a small SQL query were putting it inline would make perfect sense. Loading the SQL query from a file would be more beneficial for large join queries like this query taken from here:
SELECT country.country_name_eng, SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls, AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_difference FROM country LEFT JOIN city ON city.country_id = country.id LEFT JOIN customer ON city.id = customer.city_id LEFT JOIN call ON call.customer_id = customer.id GROUP BY country.id, country.country_name_eng HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call) ORDER BY calls DESC, country.id ASC;
How To Import SQL File With PHP
This post was first published on How To Import SQL File With PHP Sometimes there are times when you can not use PhpMyAdmin or any database program, and you have to use other options to import the Sql file into your database. Here, I’ll tell you how you can import with both the old mysql_query and the new mysqli_query in Php. You can also access the codes from the my Github project. You need to do followings on both methods; upload Sql file and php file at the same place and run the php file through the site address. You can also run console commands through the server. Update $filename with the name of sql. Update the $mysql_host variable with the database server. If the database server is not different, you can leave it as it is. $mysql_username and $mysql_password are the username and password for your database. $mysql_database is the database name, update it with your database name.
With mysql_query Function
// Name of the file $filename = 'sql.sql'; // MySQL host $mysql_host = 'localhost'; // MySQL username $mysql_username = 'username'; // MySQL password $mysql_password = 'password'; // Database name $mysql_database = 'database'; // Connect to MySQL server mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error()); // Select database mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error()); // Temporary variable, used to store current query $templine = ''; // Read in entire file $lines = file($filename); // Loop through each line foreach ($lines as $line) // Skip it if it's a comment if (substr($line, 0, 2) == '--' || $line == '') continue; // Add this line to the current segment $templine .= $line; // If it has a semicolon at the end, it's the end of the query if (substr(trim($line), -1, 1) == ';') // Perform the query mysql_query($templine) or print('Error performing query \'' . $templine . '\': ' . mysql_error() . '
'); // Reset temp variable to empty $templine = ''; > > echo "Tables imported successfully"; With mysqli_query class
// Name of the file $filename = 'sql.sql'; // MySQL host $mysql_host = 'localhost'; // MySQL username $mysql_username = 'username'; // MySQL password $mysql_password = 'password'; // Database name $mysql_database = 'database'; // Connect to MySQL server $con = @new mysqli($mysql_host,$mysql_username,$mysql_password,$mysql_database); // Check connection if ($con->connect_errno) echo "Failed to connect to MySQL: " . $con->connect_errno; echo "
Error: " . $con->connect_error; > // Temporary variable, used to store current query $templine = ''; // Read in entire file $lines = file($filename); // Loop through each line foreach ($lines as $line) // Skip it if it's a comment if (substr($line, 0, 2) == '--' || $line == '') continue; // Add this line to the current segment $templine .= $line; // If it has a semicolon at the end, it's the end of the query if (substr(trim($line), -1, 1) == ';') // Perform the query $con->query($templine) or print('Error performing query \'' . $templine . '\': ' . $con->error() . '
'); // Reset temp variable to empty $templine = ''; > > echo "Tables imported successfully"; $con->close($con); blasto333 / execute_sql.php
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters
| //This function will take a given $file and execute it directly in php. |
| //This code is for use within a codeigntier framework application |
| //It tries three methods so it should almost allways work. |
| //method 1: Directly via cli using mysql CLI interface. (Best choice) |
| //method 2: use mysqli_multi_query |
| //method 3: use PDO exec |
| //It tries them in that order and checks to make sure they WILL work based on various requirements of those options |
| public function execute_sql ( $ file ) |
| //1st method; directly via mysql |
| $ mysql_paths = array (); |
| //use mysql location from `which` command. |
| $ mysql = trim(`which mysql`); |
| if (is_executable( $ mysql )) |
| array_unshift( $ mysql_paths , $ mysql ); |
| > |
| //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.