How do I import a sql file in mysql database using PHP

Question

I m trying to import a  sql file through PHP code  However  my code shows this error   There was an error during import  Please make sure the import file is saved in the same folder as this script and check your values   MySQL Database Name     test MySQL User Name     root MySQL Password  NOTSHOWN MySQL Host Name     localhost MySQL Import Filename   dbbackupmember sql   And this is my code    lt  php   ENTER THE RELEVANT INFO BELOW  mysqlDatabaseName   test    mysqlUserName   root    mysqlPassword       mysqlHostName   localhost    mysqlImportFilename   dbbackupmember sql     DONT EDIT BELOW THIS LINE   Export the database and output the status to the page  command  mysql -h    mysqlHostName    -u    mysqlUserName    -p    mysqlPassword        mysqlDatabaseName     lt      mysqlImportFilename  exec  command  output array    worked   switch  worked       case 0          echo  Import file  lt b gt     mysqlImportFilename    lt  b gt  successfully imported to database  lt b gt     mysqlDatabaseName    lt  b gt            break      case 1          echo  There was an error during import  Please make sure the import file is saved in the same folder as this script and check your values  lt br  gt  lt br  gt  lt table gt  lt tr gt  lt td gt MySQL Database Name  lt  td gt  lt td gt  lt b gt     mysqlDatabaseName    lt  b gt  lt  td gt  lt  tr gt  lt tr gt  lt td gt MySQL User Name  lt  td gt  lt td gt  lt b gt     mysqlUserName    lt  b gt  lt  td gt  lt  tr gt  lt tr gt  lt td gt MySQL Password  lt  td gt  lt td gt  lt b gt NOTSHOWN lt  b gt  lt  td gt  lt  tr gt  lt tr gt  lt td gt MySQL Host Name  lt  td gt  lt td gt  lt b gt     mysqlHostName    lt  b gt  lt  td gt  lt  tr gt  lt tr gt  lt td gt MySQL Import Filename  lt  td gt  lt td gt  lt b gt     mysqlImportFilename    lt  b gt  lt  td gt  lt  tr gt  lt  table gt            break      gt    What am I doing wrong  The SQL file is in the same directory

User · Answer

I Thing you can Try this Code  It s Run for my Case    x000D   x000D   lt  php x000D   x000D   con   mysqli connect  localhost    root    NOTSHOWN    test    x000D   x000D   filename    dbbackupmember sql   x000D   handle   fopen  filename   r     x000D   contents   fread  handle  filesize  filename    x000D   x000D   sql   explode       contents   x000D  foreach   sql as  query    x000D    result   mysqli query  con   query   x000D   if   result    x000D    echo   lt tr gt  lt td gt  lt br gt  lt  td gt  lt  tr gt    x000D    echo   lt tr gt  lt td gt    query   lt  td gt  lt  tr gt    x000D    echo   lt tr gt  lt td gt  lt br gt  lt  td gt  lt  tr gt    x000D     x000D    x000D   x000D  fclose  handle   x000D  echo  success   x000D   x000D   x000D    gt  x000D   x000D   x000D

User · Answer

I have Test your code  this error shows when you already have the DB imported or with some tables with the same name  also the Array error that shows is because you add in in the exec parenthesis  here is the fixed version     lt  php   ENTER THE RELEVANT INFO BELOW  mysqlDatabaseName   test    mysqlUserName   root    mysqlPassword       mysqlHostName   localhost    mysqlImportFilename   dbbackupmember sql     DONT EDIT BELOW THIS LINE   Export the database and output the status to the page  command  mysql -h    mysqlHostName    -u    mysqlUserName    -p    mysqlPassword        mysqlDatabaseName     lt      mysqlImportFilename   output array    exec  command  output  worked   switch  worked       case 0          echo  Import file  lt b gt     mysqlImportFilename    lt  b gt  successfully imported to database  lt b gt     mysqlDatabaseName    lt  b gt            break      case 1          echo  There was an error during import            break      gt

User · Answer

I use this code and RUN SUCCESS FULL    filename    apptoko-2016-12-23 sql     change to ur  sql file                              handle   fopen  filename   r                                  contents   fread  handle  filesize  filename                                  sql   explode      contents                                  foreach  sql as  query                                    result mysql query  query                                   if   result                                    echo   lt tr gt  lt td gt  lt BR gt  lt  td gt  lt  tr gt                                     echo   lt tr gt  lt td gt      query      lt b gt SUCCESS lt  b gt  lt  td gt  lt  tr gt                                     echo   lt tr gt  lt td gt  lt BR gt  lt  td gt  lt  tr gt                                                                                                fclose  handle

User · Answer

If you need a User Interface and if you want to use PDO  Here s a simple solution   lt form method  post  enctype  multipart form-data  gt       lt input type  text  name  db  placeholder  Databasename    gt       lt input type  file  name  file  gt       lt input type  submit  name  submit  value  submit  gt   lt  form gt    lt  php  if isset   POST  submit           query   file get contents   FILES  file    name          dbname     POST  db         con   new PDO  mysql host localhost dbname  dbname   root            stmt    con- gt prepare  query       if  stmt- gt execute             echo  Successfully imported to the  dbname              gt    Definitely working on my end  Worth a try

User · Answer

lt  php  host    localhost    uname    root    pass        database    demo1     Change Your Database Name  conn   new mysqli  host   uname   pass   database    filename    users sql     How to Create SQL File Step   url http   localhost phpmyadmin- gt detabase select- gt table select- gt Export In Upper Toolbar - gt Go DOWNLOAD  SQL FILE  op data        lines   file  filename   foreach   lines as  line        if  substr  line  0  2      --      line         This IF Remove Comment Inside SQL FILE               continue             op data     line      if  substr trim  line   -1  1           Breack Line Upto     NEW QUERY                conn- gt query  op data            op data               echo  Table Created Inside      database     Database            gt

User · Answer

Warning  mysql   extension is deprecated as of PHP 5 5 0  and has been removed as of PHP 7 0 0  Instead  either the mysqli or PDO MySQL extension should be used  See also the MySQL API Overview for further help while choosing a MySQL API    Whenever possible  importing a file to MySQL should be delegated to MySQL client    I have got another way to do this  try this   lt  php     Name of the file  filename    churc sql      MySQL host  mysql host    localhost      MySQL username  mysql username    root      MySQL password  mysql password          Database name  mysql database    dump       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    lt strong gt      templine            mysql error       lt br   gt  lt br   gt            Reset temp variable to empty      templine            echo  Tables imported successfully     gt    This is working for me

User · Answer

You can use the mysqli multi query function as below    sql   file get contents  mysqldump sql      mysqli   new mysqli  localhost    root    pass    testdb        execute multi query     mysqli- gt multi query  sql

User · Answer

Import data   filename    database file name sql   import tables  localhost   root      database name   filename    function import tables  host  uname  pass  database   filename  tables              connection   mysqli connect  host  uname  pass      or die  Database Connection Failed         selectdb   mysqli select db  connection   database  or die  Database could not be selected       templine        lines   file  filename      Read entire file  foreach   lines as  line          Skip it if it s a comment     if  substr  line  0  2      --      line          substr  line  0  2                    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                                mysqli query  connection   templine              or print  Error performing query    lt strong gt      templine            mysqli error  connection      lt br   gt  lt br   gt                  templine                           echo  Tables imported successfully            Backup database from php script backup tables  hostname   UserName   pass   databses name     function backup tables  host  user  pass  name  tables              link   mysqli connect  host  user  pass       if  mysqli connect errno             echo  Failed to connect to MySQL      mysqli connect error               mysqli select db  link  name         get all of the tables     if  tables                   tables   array             result   mysqli query  link  SHOW TABLES            while  row   mysqli fetch row  result                          tables      row 0                  else           tables   is array  tables     tables   explode      tables               return           foreach  tables as  table                 result   mysqli query  link  SELECT   FROM    table            num fields   mysqli num fields  result            row query   mysqli query  link  SHOW CREATE TABLE    table            row2   mysqli fetch row  row query            return     n n   row2 1     n n            for   i   0   i  lt   num fields   i                           while  row   mysqli fetch row  result                                  return    INSERT INTO    table   VALUES                    for  j 0   j  lt   num fields   j                                            row  j    addslashes  row  j                         row  j    str replace   n     n    row  j                        if  isset  row  j                                return        row  j                               else                             return                                                    if   j  lt    num fields-1      return                                             return       n                                    return    n n n                save file      handle   fopen  backup-  date  d m Y  h i s A    -   md5 implode      tables      sql   w         fwrite  handle  return       fclose  handle

User · Answer

Solution special chars    link mysql connect  dbHost   dbUser   dbPass  OR die  connecting to host     dbHost   failed    mysql error     mysql select db  dbName  OR die  select db     dbName   failed    mysql error        charset important mysql set charset  utf8   link

User · Answer

As we all know MySQL was deprecated in PHP 5 5 0  and it was removed in PHP 7 0 0 ref so I have converted accepted answer to mysqli    lt  php    Name of the file  filename    db sql      MySQL host  mysql host    localhost      MySQL username  mysql username    root      MySQL password  mysql password    123456      Database name  mysql database    mydb     connection   mysqli connect  mysql host  mysql username  mysql password  mysql database  or die mysqli error  connection        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     mysqli query  connection  templine  or print  Error performing query    lt strong gt      templine            mysqli error  connection      lt br   gt  lt br   gt            Reset temp variable to empty      templine            echo  Tables imported successfully     gt

User · Answer

function restoreDatabase  db name  file path           checking valid extension file      path parts   pathinfo  file path        ext file    path parts  extension         filename    path parts  basename         if  ext file     sql                   c   new Config               confJson    c- gt getConfig              conf   json decode  confJson              dbhost      127 0 0 1             dbuser      conf- gt db username              dbpwd       conf- gt db password            dbname      db name                dumpfile    file path             is file   file exists  file path            if  is file    TRUE                             passthru   usr bin mysqldump --opt --host  dbhost --user  dbuser --password  dbpwd  dbname  lt   dumpfile                    passthru  tail -1  dumpfile                 system  mysql --user    dbuser   --password    dbpwd      db name    lt     file path                return  Database was restored from  filename                         else                          return  Restore database was aborted due    filename   does not exist                           else               return  Invalid file format Require sql file to restore this    db name   database     filename   is not sql file format n eg  mybackupfile sql

User · Answer

lt  php system  mysql --user USER --password PASSWORD DATABASE lt  FOLDER  sql      gt

User · Answer

If you are using PHP version 7 or higher  try below script      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- gt connect errno       echo  Failed to connect to MySQL       con- gt connect errno     echo   lt br  gt Error       con- gt 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- gt query  templine  or print  Error performing query    lt strong gt      templine             con- gt error       lt br   gt  lt br   gt            Reset temp variable to empty      templine           echo  Tables imported successfully    con- gt close  con

User · Answer

Warning  mysql   extension is deprecated as of PHP 5 5 0  and has been removed as of PHP 7 0 0  Instead  either the mysqli or PDO MySQL extension should be used  See also the MySQL API Overview for further help while choosing a MySQL API    Whenever possible  importing a file to MySQL should be delegated to MySQL client    the answer from Raj is useful  but  because of file  filename   it will fail if your mysql-dump not fits in memory   If you are on shared hosting and there are limitations like 30 MB and 12s Script runtime and you have to restore a x00MB mysql dump  you can use this script   it will walk the dumpfile query for query  if the script execution deadline is near  it saves the current fileposition in a tmp file and a automatic browser reload will continue this process again and again     If an error occurs  the reload will stop and an the error is shown      if you comeback from lunch your db will be restored  -   the noLimitDumpRestore php           your config  filename    yourGigaByteDump sql    dbHost    localhost    dbUser    user    dbPass      pass      dbName    dbname    maxRuntime   8     less then your max script execution limit    deadline   time    maxRuntime    progressFilename    filename   filepointer      tmp file for progress  errorFilename    filename   error      tmp file for erro  mysql connect  dbHost   dbUser   dbPass  OR die  connecting to host     dbHost   failed    mysql error     mysql select db  dbName  OR die  select db     dbName   failed    mysql error        fp   fopen  filename   r    OR die  failed to open file    filename       check for previous error if  file exists  errorFilename         die   lt pre gt  previous error    file get contents  errorFilename          activate automatic reload in browser echo   lt html gt  lt head gt   lt meta http-equiv  refresh  content      maxRuntime 2     gt  lt pre gt        go to previous file position  filePosition   0  if  file exists  progressFilename          filePosition   file get contents  progressFilename       fseek  fp   filePosition       queryCount   0   query       while   deadline gt time   AND   line fgets  fp  1024000          if substr  line 0 2    --  OR trim  line                 continue              query     line      if  substr trim  query  -1                  if   mysql query  query                  error    Error performing query    lt strong gt      query            mysql error                file put contents  errorFilename   error   n                exit                     query               file put contents  progressFilename  ftell  fp       save the current file position for           queryCount             if  feof  fp         echo  dump successfully restored     else      echo ftell  fp      filesize  filename       round ftell  fp  filesize  filename   2  100        n       echo  queryCount   queries processed  please reload or wait for automatic browser refresh

User · Answer

Grain Script is superb and save my day  Meanwhile mysql is depreciated and I rewrote Grain answer using PDO        server      localhost         username      root         password      your password          database    sample db           PDO connection start         conn   new PDO  mysql host  server  dbname  database    username   password        conn- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION                 conn- gt exec  SET CHARACTER SET utf8                PDO connection end            your config      filename    yourFile sql         maxRuntime   8     less then your max script execution limit        deadline   time    maxRuntime        progressFilename    filename   filepointer      tmp file for progress      errorFilename    filename   error      tmp file for erro          fp   fopen  filename   r    OR die  failed to open file    filename           check for previous error     if  file exists  errorFilename             die   lt pre gt  previous error    file get contents  errorFilename                  activate automatic reload in browser     echo   lt html gt  lt head gt   lt meta http-equiv  refresh  content      maxRuntime 2     gt  lt pre gt            go to previous file position      filePosition   0      if  file exists  progressFilename              filePosition   file get contents  progressFilename           fseek  fp   filePosition               queryCount   0       query           while   deadline gt time   AND   line fgets  fp  1024000              if substr  line 0 2    --  OR trim  line                     continue                      query     line          if  substr trim  query  -1                        igweze prep   conn- gt prepare  query                if    igweze prep- gt execute                        error    Error performing query    lt strong gt      query            print r  conn- gt errorInfo                     file put contents  errorFilename   error   n                    exit                             query                   file put contents  progressFilename  ftell  fp       save the current file position for               queryCount                         if  feof  fp             echo  dump successfully restored         else          echo ftell  fp      filesize  filename       round ftell  fp  filesize  filename   2  100        n           echo  queryCount   queries processed  please reload or wait for automatic browser refresh

[php] How do I import a .sql file in mysql database using PHP?

Examples related to php

Examples related to mysql