Import SQL files using php

Import SQL files using php
Import SQL files using php

sql php

Basic Idea of importing SQL files using php

1. Open the SQL file and place it in a variable (string type ).

2. Replace the comments ("--" and "/**/") with regular expressions.

3. Use explode to split an array and remove spaces in each row

4. Use my_query () to execute the SQL statement after connecting to the database

Code

 

  

    
  

  
// +------------------------------------------------------------------------------------------
  
// | Author: longDD 
     
  
// +------------------------------------------------------------------------------------------
  
// | There is no true,no evil,no light,there is only power.
  
// +------------------------------------------------------------------------------------------
  
// | Description: import sql Dates: 2014-08-07
  
// +------------------------------------------------------------------------------------------
  

  

  
class ImportSql 
  
{
  

  
/** @ Var $ content database connection */
  
    protected $connect = null;
  
/** @ Var $ db database object */
  
    protected $db = null;
  
/** @ Var $ sqlFile SQL file */
  
    public $sqlFile = "";
  
/** @ Array @ sqlArr SQL statement array */
  
    public $sqlArr = array();
  

  
    /** 
  
* Constructor
  
     * 
  
* @ Param string $ host address
  
* @ Param string $ user Username
  
* @ Param string $ pw Password
  
* @ Param $ db_name Database Name
  
     * @return void
  
     */
  
    public function __construct($host, $user, $pw, $db_name) 
  
    {
  
/** Connect to the database */
  
        $this->connect = mysql_connect($host, $user, $pw) or die("Could not connect: " . mysql_error());
  
/** Select database */
  
        $this->db = mysql_select_db($db_name, $this->connect) or die("Yon can not select the table:" . mysql_error());
  
    }
  

  
    /** 
  
* Import an SQL File
  
     * 
  
* @ Param string $ url File Path
  
* @ Return true import to return true
  
     */
  
    public function Import($url) 
  
    {
  
        if(!file_exists($url))
  
        {
  
Exit ("the file does not exist! ");
  
        }
  

  
        $this->sqlFile = file_get_contents($url);
  

  
        if (!$this->sqlFile) 
  
        {
  
Exit ("An error occurred while opening the file! ");
  
        } 
  
        else 
  
        {
  
            $this->GetSqlArr();
  

  
            if ($this->Runsql()) 
  
            {
  
                return true;
  
            }
  
        }
  
    }
  

  
    /**
  
* Obtain the SQL statement Array
  
     * 
  
     * @return void
  
     */
  
    public function GetSqlArr() 
  
    {
  
/** Remove comments */
  
        $str = $this->sqlFile;
  
        $str = preg_replace('/--.*/i', '', $str);
  
        $str = preg_replace('/\/\*.*\*\/(\;)?/i', '', $str);
  

  
/** Remove spaces to create an array */
  
        $str = explode(";\n", $str);
  

  
        foreach ($str as $v) 
  
        {
  
            $v = trim($v);
  

  
            if (empty($v)) 
  
            {
  
                continue;
  
            } 
  
            else 
  
            {
  
                $this->sqlArr[] = $v;
  
            }
  
        }
  
    }
  

  
    /**
  
* Execute the SQL File
  
     * 
  
* @ Return true: returns true if execution is successful.
  
     */
  
    public function RunSql() 
  
    {
  
/** Start transaction */
  
        if (mysql_query('BEGIN'))
  
        {
  
            foreach ($this->sqlArr as $k => $v)
  
            {
  
                if (!mysql_query($v)) 
  
                {
  
/** Rollback */
  
                    mysql_query('ROLLBACK');
  

  
Exit ("SQL statement error:". $ k. "row". mysql_error ());
  
                }
  
            }
  

  
/** Submit the transaction */
  
            mysql_query('COMMIT');
  
            return true;
  
        }
  
        else
  
        {
  
Exit ('the transaction cannot be started! ');
  
        }
  
    }
  

  
}
  

  
// +------------------------------------------------------------------------------------------
  
// | End of ImportSql class
  
// +------------------------------------------------------------------------------------------
  

  

  
/**
  
* This is a example.
  
*/
  

  
header("Content-type:text/html;charset=utf-8");
  

  
$sql = new ReadSql("localhost", "root", "", "log_db");
  

  
$rst = $sql->Import("./log_db.sql");
  

  
if ($rst) 
  
{
  
   echo "Success！";
  
}
  

  
// +------------------------------------------------------------------------------------------
  
// | End of file ImportSql.php
  
// +------------------------------------------------------------------------------------------
  
// | Location: ./ImportSql.php
  
// +------------------------------------------------------------------------------------------