Export MySQL database using PHP [closed]

Question

I've build a php/mysql (wamp) application and deployed on a local workstation. My customer wants to save db and restore it when he likes.

I've found this code for saving:

<?php   $DB_HOST = "localhost"; $DB_USER = "root"; $DB_PASS = "admin"; $DB_NAME = "dbname";  $con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);     $tables = array();  $result = mysqli_query($con,"SHOW TABLES"); while ($row = mysqli_fetch_row($result)) {     $tables[] = $row[0]; }  $return = '';  foreach ($tables as $table) {     $result = mysqli_query($con, "SELECT * FROM ".$table);     $num_fields = mysqli_num_fields($result);      $return .= 'DROP TABLE '.$table.';';     $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));     $return .= "\n\n".$row2[1].";\n\n";      for ($i=0; $i < $num_fields; $i++) {          while ($row = mysqli_fetch_row($result)) {             $return .= 'INSERT INTO '.$table.' VALUES(';             for ($j=0; $j < $num_fields; $j++) {                  $row[$j] = addslashes($row[$j]);                 if (isset($row[$j])) {                     $return .= '"'.$row[$j].'"';} else { $return .= '""';}                     if($j<$num_fields-1){ $return .= ','; }                 }                 $return .= ");\n";             }         }         $return .= "\n\n\n";      }   $handle = fopen('backup.sql', 'w+'); fwrite($handle, $return); fclose($handle);  echo "success";  ?> 

This code saves file in a default folder. What I need is to let user to decide where to save backup file or simply download it through browser. On the other hand user needs to restore from the file he wants so I need a 'browse' button to let him choose the file in any of his folder.

My database is utf8_general_ci and has english, french and italian language I don't need complex codes because I wouldn't know how to manage them :-(

Thanks in advance.

Answer 1

Best way to export database using php script.

Or add 5th parameter(array) of specific tables: array("mytable1","mytable2","mytable3") for multiple tables

<?php      //ENTER THE RELEVANT INFO BELOW     $mysqlUserName      = "Your Username";     $mysqlPassword      = "Your Password";     $mysqlHostName      = "Your Host";     $DbName             = "Your Database Name here";     $backup_name        = "mybackup.sql";     $tables             = "Your tables";     //or add 5th parameter(array) of specific tables:    array("mytable1","mytable2","mytable3") for multiple tables      Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName,  $tables=false, $backup_name=false );      function Export_Database($host,$user,$pass,$name,  $tables=false, $backup_name=false )     {         $mysqli = new mysqli($host,$user,$pass,$name);          $mysqli->select_db($name);          $mysqli->query("SET NAMES 'utf8'");          $queryTables    = $mysqli->query('SHOW TABLES');          while($row = $queryTables->fetch_row())          {              $target_tables[] = $row[0];          }            if($tables !== false)          {              $target_tables = array_intersect( $target_tables, $tables);          }         foreach($target_tables as $table)         {             $result         =   $mysqli->query('SELECT * FROM '.$table);               $fields_amount  =   $result->field_count;               $rows_num=$mysqli->affected_rows;                  $res            =   $mysqli->query('SHOW CREATE TABLE '.$table);              $TableMLine     =   $res->fetch_row();             $content        = (!isset($content) ?  '' : $content) . "\n\n".$TableMLine[1].";\n\n";              for ($i = 0, $st_counter = 0; $i < $fields_amount;   $i++, $st_counter=0)              {                 while($row = $result->fetch_row())                   { //when started (and every after 100 command cycle):                     if ($st_counter%100 == 0 || $st_counter == 0 )                       {                             $content .= "\nINSERT INTO ".$table." VALUES";                     }                     $content .= "\n(";                     for($j=0; $j<$fields_amount; $j++)                       {                          $row[$j] = str_replace("\n","\\n", addslashes($row[$j]) );                          if (isset($row[$j]))                         {                             $content .= '"'.$row[$j].'"' ;                          }                         else                          {                                $content .= '""';                         }                              if ($j<($fields_amount-1))                         {                                 $content.= ',';                         }                           }                     $content .=")";                     //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler                     if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num)                      {                            $content .= ";";                     }                      else                      {                         $content .= ",";                     }                      $st_counter=$st_counter+1;                 }             } $content .="\n\n\n";         }         //$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";         $backup_name = $backup_name ? $backup_name : $name.".sql";         header('Content-Type: application/octet-stream');            header("Content-Transfer-Encoding: Binary");          header("Content-disposition: attachment; filename=\"".$backup_name."\"");           echo $content; exit;     } ?> 

Answer 2

This tool might be useful, it's a pure PHP based export utility: https://github.com/2createStudio/shuttle-export