Execute a *.sql file using php

Question

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?

[questions 6976355](http://stackoverflow.com/questions/6976355/excute-a-mysql-script-from-within-a-php-script/6976390) — nobody, Aug 12 '11 at 10:28

score 8 · Accepted Answer · answered Aug 12 '11 at 10:25

You are looking for

mysqli_multi_query - Executes one or multiple queries which are concatenated by a semicolon.

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.

score 1 · Answer 2 · answered Aug 12 '11 at 11:56

1

You can explode your queries

function batch($file){
    $queries = explode(";", $file);
    foreach($queries as $query){
        mysql_query($query);
    }
}

answered Aug 12 '11 at 11:56

genesis

50,477
20
96
125

score 0 · Answer 3 · answered Dec 31 '16 at 00:00

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.

<?php
//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;
}

Github gist

https://gist.github.com/blasto333/d5d9079c78565c97119506e3c4f5ae3e

Execute a *.sql file using php

3 Answers3

Linked