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?

---

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.

---

You can explode your queries

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

---

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