Loading sql files from within PHP

Question

I m creating an installation script for an application that I m developing and need to create databases dynamically from within PHP  I ve got it to create the database but now I need to load in several  sql files  I had planned to open the file and mysql query it a line at a time - until I looked at the schema files and realised they aren t just one query per line   So  how do I load an sql file from within PHP  as phpMyAdmin does with its import command

User · Accepted Answer

I m getting the feeling that everyone here who s answered this question doesn t know what it s like to be a web application developer who allows people to install the application on their own servers  Shared hosting  especially  doesn t allow you to use SQL like the  LOAD DATA  query mentioned previously  Most shared hosts also don t allow you to use shell exec   Now  to answer the OP  your best bet is to just build out a PHP file that contains your queries in a variable and can just run them  If you re determined to parse  sql files  you should look into phpMyAdmin and get some ideas for getting data out of  sql files that way  Look around at other web applications that have installers and you ll see that  rather than use  sql files for their queries  they just package them up in PHP files and just run each string through mysql query or whatever it is that they need to do

User · Answer

I noticed that the PostgreSQL PDO driver does not allow you to run scripts separated by semicolons  In order to run a  sql file on any database using PDO it is necessary to split the statements in PHP code yourself  Here is a solution that seems to work quite well   https   github com diontruter migrate blob master src Diontruter Migrate SqlScriptParser php  The referenced class has done the trick for me in a database independent way  please message me if there are any issues  Here is how you could use the script after adding it to your project    pdo   new PDO  connectionString   userName   password    pdo- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION    parser   new SqlScriptParser     sqlStatements    parser- gt parse  fileName   foreach   sqlStatements as  statement         distilled    parser- gt removeComments  statement       if   empty  distilled              statement    pdo- gt prepare  sql            affectedRows    statement- gt execute

User · Answer

Since I can t comment on answer  beware to use following solution    db   new PDO  dsn   user   password     sql   file get contents  file sql      qr    db- gt exec  sql     There is a bug in PHP PDO https   bugs php net bug php id 61613  db- gt exec  SELECT 1  invalidstatement  SELECT 2      won t error out or return false  tested on PHP 5 5 14

User · Answer

Many hosts will not allow you to create your own database through PHP  but you seem to have solved that  Once the DB has been created  you can manipulate and populate it simply      mysql connect  localhost      mysql query  SOURCE file sql

User · Answer

I hope the following code will solve your problem pretty well          Empty all tables  contents   result t   mysql query  SHOW TABLES    while  row   mysql fetch assoc  result t     mysql query  TRUNCATE      row  Tables in      mysql database         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    lt strong gt      templine            mysql error       lt br   gt  lt br   gt            Reset temp variable to empty      templine              gt

User · Answer

An updated solution of Plahcinski solution  Alternatively you can use fopen and fread for bigger files    fp   file  database sql   FILE IGNORE NEW LINES   FILE SKIP EMPTY LINES    query       foreach   fp as  line        if   line        amp  amp  strpos  line   --       false             query     line          if  substr  query  -1                        mysql query  query                query

User · Answer

sql   file get contents  quot sql sql quot     Seems to be the simplest answer

User · Answer

Just to restate the problem for everyone   PHP s mysql query  automatically end-delimits each SQL commands  and additionally is very vague about doing so in its manual  Everything beyond one command will yield an error   On the other mysql query is fine with a string containing SQL-style comments   n   r    The limitation of mysql query reveals itself in that the SQL parser reports the problem to be directly at the next command e g    You have an error in your SQL syntax  check the manual that corresponds to your  MySQL server version for the right syntax to use near  INSERT INTO  outputdb      intid    entry id    definition   VALUES      Here is a quick solution   assuming well formatted SQL    sqlCmds   preg split     n  t     n  t    n  r       sqlDump

User · Answer

Unless you plan to import huge  sql files  just read the entire file into memory  and run it as a query   It s been a while since I ve used PHP  so  pseudo code   all query   read file   my file sql   con   mysql connect  localhost   con mysql select db  mydb   con mysql query all query  con close     Unless the files are huge  say  over several megabytes   there s no reason to execute it line-at-a-time  or try and split it into multiple queries  by splitting using    which as I commented on cam8001 s answer  will break if the query has semi-colons within strings

User · Answer

None of the solutions I have seen here deal with needing to change the delimiter while creating a stored procedure on a server where I can t count on having access to LOAD DATA INFILE   I was hoping to find that someone had already solved this without having to scour the phpMyAdmin code to figure it out   Like others  I too was in the process of looking for someone else s GPL ed way of doing it since I am writing GPL code myself

User · Answer

I have an environment where no mysql tool or phpmyadmin just my php application connecting to a mysql server on a different host but I need to run scripts exported by mysqldump or myadmin  To solve the problem I created a script multi query as I mentioned here  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   Check it out  https   github com kepes php-migration  It s pure php and don t need any other tools  If you don t process user input with it only scripts made by developers or export tools you can use it safely

User · Answer

Works on Navicat dumps  Might need to dump the first       comment navicat puts in    file content   file  myfile sql     query       foreach  file content as  sql line     if trim  sql line         amp  amp  strpos  sql line   --       false        query     sql line      if  substr rtrim  query   -1                 echo  query         result   mysql query  query or die mysql error            query

User · Answer

mysqli can run multiple queries separated by a     you could read in the whole file and run it all at once using mysqli multi query    But  I ll be the first to say that this isn t the most elegant solution

User · Answer

I use this all the time    sql   explode     file get contents   your dump file  sql         foreach  sql as  query   mysql query  query

User · Answer

Some guys  Plahcinski  suggested this code    file content   file  myfile sql     query       foreach  file content as  sql line     if trim  sql line         amp  amp  strpos  sql line   --       false        query     sql line      if  substr rtrim  query   -1                 echo  query         result   mysql query  query or die mysql error            query                      but I would update it with the one which worked for me      selecting my database      database    databaseTitleInFile        selectDatabase   mysql select db  database   con       if    selectDatabase               die  Could not select the database      mysql error               echo  The database      database     selected successfully n     reading the file      file path     yourPath to File       if  file exists  file path            echo  File Not Exists              file content   file get contents  file path        array   explode   n    file content    making queries      query               foreach  array as  sql line    sql line trim  sql line             if  sql line        amp  amp  substr  sql line  0  2       --   amp  amp  strpos  sql line            false                query     sql line              if  substr rtrim  query   -1                          result   mysql query  query or die mysql error                    query                                              because it is more comprehensive   -

User · Answer

The easiest and fastest way to load  amp  parse phpmyadmin dump or mysql dump file      mysql -u username -p -h localhost dbname  lt  dumpfile sql

User · Answer

Are you sure that its not one query per line  Your text editor may be wrapping lines  but in reality  each query may be on a single line   At any rate  olle s method seems best  If you have reasons to run queries one at time  you should be able to read in your file line by line  then use the semicolon at the end of each query to delimit  You re much better off reading in a file line by line than trying to split an enormous string  as it will be much kinder to your server s memory  Example    query         handle    fopen   sqlfile sql    r     if   handle        while   feof  handle              query   fgets  handle  4096            if  substr rtrim  query   -1                               run your query  then unset the string              query                            fclose  handle       Obviously  you ll need to consider transactions and the rest if you re running a  whole lot of queries in a batch  but it s probably not a big deal for a new-install script

User · Answer

This may be helpful --   More or less what it does is to first take the string given to the function  the file get contents   value of your file sql  and remove all the line breaks  Then it splits the data by the     character  Next it goes into a while loop  looking at each line of the array that is created  If the line contains the       character  it will know it is a query and execture the myquery   function for the given line data   Code   function myquery  query     mysql connect dbhost  dbuser  dbpass    mysql select db dbname     result   mysql query  query    if   mysql errno    amp  amp   mysql num rows  result   gt  0       else     result  not     mysql close     return  result        function mybatchquery   str      sql   str replace   n      str    sql   explode      str     x 0   while  isset  str  x       if  preg match     w  W     w  W       myquery  str  x         x       return TRUE         function myrows  result      rows    mysql num rows  result    return  rows        function myarray  result      array   mysql fetch array  result    return  array        function myescape  query      escape   mysql escape string  query    return  escape        str   file get contents  foo sql    mybatchquery  str

User · Answer

Some PHP libraries can parse a SQL file made of multiple SQL statements  explode it properly  not using a simple     explode  naturally   and the execute them    For instance  check Phing s PDOSQLExecTask

User · Answer

This The Best Code For restore sql by php  can use 100  Goooood   Thank A lot      file content   file  myfile sql     query       foreach  file content as  sql line   if trim  sql line         amp  amp  strpos  sql line   --       false     query     sql line   if  substr rtrim  query   -1              echo  query      result   mysql query  query or die mysql error         query

User · Answer

db   new PDO  dsn   user   password     sql   file get contents  file sql      qr    db- gt exec  sql

User · Answer

This is from a project I am working on   Basically takes any text file and extracts the SQL statements while ignoring comments and gratuitous line breaks    lt  php            ingestSql string    string       Read the contents of a SQL batch file  stripping away comments and      joining statements that are broken over multiple lines with the goal      of producing lines of sql statements that can be successfully executed      by PDO exec   or execute   functions        For example         -- My SQL Batch        CREATE TABLE foo           bar VARCHAR 80            baz INT NOT NULL         Becomes         CREATE TABLE foo bar VARCHAR 80   baz INT NOT NULL           function ingestSql  sqlFilePath   DIR       create-db sql          sqlFile   file  sqlFilePath        ingestedSql             statement           foreach  sqlFile as  line              Ignore anything between a double-dash and the end of the line         commentStart   strpos  line   --          if   commentStart     false             line   substr  line  0   commentStart                     Only process non-blank lines        if  strlen  line                 Remove any leading and trailing whitespace and append what s            left of the line to the current statement           line   trim  line            statement     line              A semi-colon ends the current statement   Otherwise what was a            newline becomes a single space          if  substr  statement  -1                       ingestedSql     statement             statement     n                     else              statement                                      return  ingestedSql         gt

User · Answer

My suggestion would be to look at the sourcecode of PHPMyBackup  It s an automated PHP SQL loader  You will find that mysql query only loads one query at a time  and projects like PHPMyAdmin and PHPMyBackup have already done the hard work for you of parsing the SQL the correct way  Please don t re-invent that wheel  P

User · Answer

The simplest solution is to use shell exec   to run the mysql client with the SQL script as input   This might run a little slower because it has to fork  but you can write the code in a couple of minutes and then get back to working on something useful   Writing a PHP script to run any SQL script could take you weeks    Supporting SQL scripts is more complex than what people are describing here  unless you re certain that your script contains only a subset of the functionality of scripts   Below are some examples of things that may appear in an ordinary SQL script that make it complex to code a script to interpret it line by line   -- Comment lines cannot be prepared as statements -- This is a MySQL client tool builtin command    -- It cannot be prepared or executed by server  USE testdb   -- This is a multi-line statement  CREATE TABLE foo     string VARCHAR 100      -- This statement is not supported as a prepared statement  LOAD DATA INFILE  datafile txt  INTO TABLE foo   -- This statement is not terminated with a semicolon  DELIMITER     -- This multi-line statement contains a semicolon  -- but not as the statement terminator  CREATE PROCEDURE simpleproc  OUT param1 INT  BEGIN   SELECT COUNT    INTO param1 FROM foo  END       If you only support a subset of SQL scripts  excluding some corner cases such as those above  it s relatively easy to write a PHP script that reads a file and executes the SQL statements within the file   But if you want to support any valid SQL script  that s much more complex     See also my answers to these related questions    Running MySQL   sql files in PHP is it possible to call a sql script from a stored procedure in another sql script  PHP  multiple SQL queries in one mysql query statement

User · Answer

this actually worked for me      load sql-commands from a sql file    function loadSQLFromFile  url           ini set    memory limit    512M            set time limit   0         global  settings database name      global  mysqli object  global  worked   worked   false        sql query               read line by line      lines   file  url        count   count  lines        for  i   0  i lt  count  i                   line    lines  i            cmd3   substr  line  0  3            cmd4   substr  line  0  4            cmd6   substr  line  0  6           if  cmd3     USE                            cut away USE                  settings database name   substr  line  5  -3                     else if  cmd4     DROP                          mysqli object- gt query  line      execute this line                   else if   cmd6     INSERT        cmd6     CREATE                             sum all lines up until   is detected              multiline    line              while  strstr  line                                       i                     line    lines  i                    multiline     line                             multiline   str replace   n        multiline      remove newlines linebreaks              mysqli object- gt query  multiline      execute this line                             return  worked      gt

User · Answer

Try This      SQL File  SQLFile    YourSQLFile sql       Server Name  hostname    localhost       User Name  db user    root       User Password  db password           DBName  database name    YourDBName       Connect MySQL  link   mysql connect  hostname   db user   db password    if    link    die  MySQL Connection error          Select MySQL DB mysql select db  database name   link  or die  Wrong MySQL Database        Function For Run Multiple Query From  SQL File function MultiQuery  sqlfile   sqldelimiter          set time limit 0    if  is file  sqlfile      true     sqlfile   fopen  sqlfile   r     if  is resource  sqlfile      true     query   array    echo   lt table cellspacing  3  cellpadding  3  border  0  gt     while  feof  sqlfile      false     query     fgets  sqlfile    if  preg match       preg quote  sqldelimiter           s   iS   end  query       1     query   trim implode      query     if  mysql query  query      false    echo   lt tr gt  lt td gt ERROR  lt  td gt  lt td gt       query     lt  td gt  lt  tr gt      else   echo   lt tr gt  lt td gt SUCCESS  lt  td gt  lt td gt      query     lt  td gt  lt  tr gt       while  ob get level    amp gt  0    ob end flush       flush       if  is string  query      true     query   array        echo   lt  table gt     return fclose  sqlfile        return false            Use Function Like This         MultiQuery  SQLFile

User · Answer

Briefly  the way I have done this is    Read the file  a db dump eg   mysqldump db  gt  db sql    sql   file get contents db sql    Import it using mysqli  multi query  if   mysqli- gt multi query  sql          mysqli- gt close      else       throw new Exception   mysqli- gt error        Watch out mysqli query supports async queries  More here  http   php net manual en mysqli multi-query php and here https   stackoverflow com a 6652908 2002493

User · Answer

mysql query  LOAD DATA LOCAL INFILE   path to file  INTO TABLE mytable

User · Answer

In my projects I ve used next solution    lt  php         Import SQL from file        param string path to sql file     function sqlImport  file          delimiter             file   fopen  file   r         isFirstRow   true       isMultiLineComment   false       sql            while   feof  file               row   fgets  file               remove BOM for utf-8 encoded file         if   isFirstRow                 row   preg replace     x EF  x BB  x BF          row                isFirstRow   false                        1  ignore empty string and comment row         if  trim  row           preg match     s    -- s  sUi    row                 continue                        2  clear comments          row   trim clearSQL  row   isMultiLineComment                3  parse delimiter row         if  preg match    DELIMITER s       sUi    row                  delimiter   preg replace    DELIMITER s          sUi     1    row               continue                        4  separate sql queries by delimiter          offset   0          while  strpos  row   delimiter   offset      false                 delimiterOffset   strpos  row   delimiter   offset               if  isQuoted  delimiterOffset   row                      offset    delimiterOffset   strlen  delimiter                 else                    sql   trim  sql         trim substr  row  0   delimiterOffset                     query  sql                     row   substr  row   delimiterOffset   strlen  delimiter                     offset   0                   sql                                        sql   trim  sql          row             if  strlen  sql   gt  0            query  row              fclose  file             Remove comments from sql        param string sql     param boolean is multicomment line     return string     function clearSQL  sql   amp  isMultiComment        if   isMultiComment            if  preg match       sUi    sql                  sql   preg replace          s  sUi        sql                isMultiComment   false            else                sql                         if trim  sql                      return  sql                        offset   0      while  preg match   -- s           sUi    sql   matched  PREG OFFSET CAPTURE   offset             list  comment   foundOn     matched 0           if  isQuoted  foundOn   sql                  offset    foundOn   strlen  comment             else               if  substr  comment  0  2                              closedOn   strpos  sql         foundOn                   if   closedOn     false                         sql   substr  sql  0   foundOn    substr  sql   closedOn   2                     else                        sql   substr  sql  0   foundOn                        isMultiComment   true                                  else                    sql   substr  sql  0   foundOn                   break                                    return  sql            Check if  offset  position is quoted        param int  offset     param string  text     return boolean     function isQuoted  offset   text        if   offset  gt  strlen  text            offset   strlen  text         isQuoted   false      for   i   0   i  lt   offset   i              if   text  i                       isQuoted     isQuoted          if   text  i           amp  amp   isQuoted               i              return  isQuoted     function query  sql        global  mysqli        echo    lt strong gt SQL CODE TO RUN  lt  strong gt  lt br gt     htmlspecialchars  sql       lt br gt  lt br gt        if    query    mysqli- gt query  sql             throw new Exception  Cannot execute request to the database   sql        mysqli- gt error            set time limit 0     mysqli   new mysqli  localhost    root        test     mysqli- gt set charset  utf8     header  Content-Type  text html charset utf-8    sqlImport  import sql     echo  Peak MB     memory get peak usage true  1024 1024    On test sql file  41Mb  memory peak usage  3 25Mb

User · Answer

phpBB uses a few functions to parse their files  They are rather well-commented  what an exception   so you can easily know what they do  I got this solution from http   www frihost com forums vt-8194 html   here is the solution an I ve used it a lot    lt php ini set  memory limit    5120M    set time limit   0                                                                                                               sql parse php                                -------------------       begin                  Thu May 31  2001       copyright               C  2001 The phpBB Group       email                  support phpbb com          Id  sql parse php v 1 8 2002 03 18 23 53 12 psotfx Exp                                                                                                                                                                         This program is free software  you can redistribute it and or modify      it under the terms of the GNU General Public License as published by      the Free Software Foundation  either version 2 of the License  or       at your option  any later version                                                                                                                                                                       These functions are mainly for use in the db utilities under the admin     however in order to make these functions available elsewhere  specifically     in the installation phase of phpBB I have seperated out a couple of     functions into this file   JLH                                                                                        remove comments will strip the sql comment lines out of an uploaded sql file    specifically for mssql and postgres type files in the install        function remove comments  amp  output        lines   explode   n    output       output              try to keep mem  use down     linecount   count  lines        in comment   false     for  i   0   i  amp lt   linecount   i               if  preg match            preg quote  lines  i                        in comment   true                 if    in comment                     output     lines  i      n                  if  preg match            preg quote  lines  i                        in comment   false                   unset  lines      return  output           remove remarks will strip the sql comment lines out of an uploaded sql file    function remove remarks  sql        lines   explode   n    sql          try to keep mem  use down     sql            linecount   count  lines       output           for   i   0   i  amp lt   linecount   i               if    i      linecount - 1       strlen  lines  i    gt  0                    if  isset  lines  i  0    amp  amp   lines  i  0                                  output     lines  i      n                       else                         output      n                          Trading a bit of speed for lower mem  use here            lines  i                         return  output            split sql file will split an uploaded sql file into single sql statements     Note  expects trim   to have already been run on  sql     function split sql file  sql   delimiter          Split up our string into  possible  SQL statements      tokens   explode  delimiter   sql          try to save mem      sql           output   array           we don t actually care about the matches preg gives us      matches   array           this is faster than calling count  oktens  every time thru the loop      token count   count  tokens      for   i   0   i  amp lt   token count   i                  Don t wanna add an empty string as the last thing in the array        if    i      token count - 1       strlen  tokens  i   gt  0                        This is the total number of single quotes in the token            total quotes   preg match all         tokens  i    matches               Counts single quotes that are preceded by an odd number of backslashes              which means they re escaped quotes            escaped quotes   preg match all      amp lt                            tokens  i    matches              unescaped quotes    total quotes -  escaped quotes               If the number of unescaped quotes is even  then the delimiter did NOT occur inside a string literal           if    unescaped quotes   2     0                            It s a complete sql statement               output      tokens  i                  save memory               tokens  i                            else                           incomplete sql statement  keep adding tokens until we have a complete one                  temp will hold what we have so far               temp    tokens  i     delimiter                 save memory                tokens  i                        Do we have a complete statement yet               complete stmt   false               for   j    i   1     complete stmt  amp  amp    j  amp lt   token count     j                                    This is the total number of single quotes in the token                  total quotes   preg match all         tokens  j    matches                     Counts single quotes that are preceded by an odd number of backslashes                    which means they re escaped quotes                  escaped quotes   preg match all      amp lt                            tokens  j    matches                    unescaped quotes    total quotes -  escaped quotes                  if    unescaped quotes   2     1                                        odd number of unescaped quotes  In combination with the previous incomplete                      statement s   we now have a complete statement   2 odds always make an even                     output      temp    tokens  j                         save memory                     tokens  j                           temp                             exit the loop                     complete stmt   true                       make sure the outer loop continues at the right point                     i    j                                  else                                       even number of unescaped quotes  We still don t have a complete statement                        1 odd and 1 even always make an odd                     temp     tokens  j     delimiter                       save memory                     tokens  j                                           for                 else                  return  output      dbms schema    yourfile sql     sql query    fread  fopen  dbms schema   r     filesize  dbms schema   or die  problem      sql query   remove remarks  sql query    sql query   split sql file  sql query          host    localhost    user    user    pass    pass    db    database name      In case mysql is deprecated use mysqli functions   mysqli connect  host  user  pass  or die  error connection    mysqli select db  db  or die  error database selection      i 1  foreach  sql query as  sql   echo  i    echo   lt br   gt    mysql query  sql  or die  error in query         gt

[php] Loading .sql files from within PHP

Examples related to php

Examples related to sql

Examples related to mysql

Examples related to import

Examples related to scripting