PHP function to backup and import database using MYSQLI

mysql_backup_import.php

<?php
/**
 * @author awan < nawa (at) yahoo (dot) com >
 * 
 */
 
 
/**
 * Function to build SQL /Importing SQL DATA
 *
 * @param string $args as the queries of sql data , yopu could use file get contents to read data args
 * @param string $dbhost database host
 * @param string $dbuser database user
 * @param string $dbpass database password
 * @param string $dbname database name
 *
 * @return string complete if complete
 */

function mysqli_import_sql( $args , $dbhost, $dbuser, $dbpass ,$dbname ) {

  // check mysqli extension installed
  if( ! function_exists('mysqli_connect') ) {
    die(' This scripts need mysql extension to be running properly ! please resolve!!');
  }

	$mysqli = @new mysqli( $dbhost, $dbuser, $dbpass, $dbname );
	if( $mysqli->connect_error ) {
    		print_r( $mysqli->connect_error );
    		return false;
  	}

    $querycount = 11;
    $queryerrors = '';
    $lines = (array) $args;
    if( is_string( $args ) ) {
      $lines =  array( $args ) ;
    }

    if ( ! $lines ) {
      return '' . 'cannot execute ' . $args;
    }

    $scriptfile = false;
    foreach ($lines as $line) {
      $line = trim( $line );
      // if have -- comments add enters
      if (substr( $line, 0, 2 ) == '--') {
          $line = "\n" . $line;
      }
      if (substr( $line, 0, 2 ) != '--') {
        $scriptfile .= ' ' . $line;
        continue;
      }
    }

    $queries = explode( ';', $scriptfile );
    foreach ($queries as $query) {
      $query = trim( $query );
      ++$querycount;

      if ( $query == '' ) {
        continue;
      }


      if ( ! $mysqli->query( $query ) ) {
        $queryerrors .= '' . 'Line ' . $querycount . ' - ' . $mysqli->error . '<br>';
        continue;
      }
    }


    if ( $queryerrors ) {
      return '' . 'There was an error on File: ' . $filename . '<br>' . $queryerrors;
    }
    
    if( $mysqli && ! $mysqli->error ) {
      @$mysqli->close();
    }   

    return 'complete dumping database !';
}

/**
 * MYSQL EXPORT TO GZIP 
 * exporting database to sql gzip compression data.
 * if directory writable will be make directory inside of directory if not exist, else wil be die
 *
 * @param string directory , as the directory to put file
 * @param $outname as file name just the name !, if file exist will be overide as numeric next ++ as name_1.sql.gz , name_2.sql.gz next ++
 *
 * @param string $dbhost database host
 * @param string $dbuser database user
 * @param string $dbpass database password
 * @param string $dbname database name
 *
 */
function backup_database( $directory, $outname , $dbhost, $dbuser, $dbpass ,$dbname ) {
  
  // check mysqli extension installed
  if( ! function_exists('mysqli_connect') ) {
    die(' This scripts need mysql extension to be running properly ! please resolve!!');
  }

	$mysqli = @new mysqli($dbhost, $dbuser, $dbpass, $dbname);
	
	if( $mysqli->connect_error ) {
		print_r( $mysqli->connect_error );
		return false;
	}

  	$dir = $directory;
  	$result = '<p> Could not create backup directory on :'.$dir.' Please Please make sure you have set Directory on 755 or 777 for a while.</p>';  
  	$res = true;
  	if( ! is_dir( $dir ) ) {
  	  if( ! @mkdir( $dir, 755 )) {
  	    $res = false;
  	  }
  	}

  $n = 1;
  if( $res ) {

    $name     = $outname;
    # counts
    if( file_exists($dir.'/'.$name.'.sql.gz' ) ) {

      for($i=1;@count( file($dir.'/'.$name.'_'.$i.'.sql.gz') );$i++){
        $name = $name;
        if( ! file_exists( $dir.'/'.$name.'_'.$i.'.sql.gz') ) {
          $name = $name.'_'.$i;
          break;
        }
      }
    }

    $fullname = $dir.'/'.$name.'.sql.gz'; # full structures

    if( ! $mysqli->error ) {
      $sql = "SHOW TABLES";
      $show = $mysqli->query($sql);
      while ( $r = $show->fetch_array() ) {
        $tables[] = $r[0];
      }

      if( ! empty( $tables ) ) {

  //cycle through
  $return = '';
  foreach( $tables as $table )
  {
    $result     = $mysqli->query('SELECT * FROM '.$table);
    $num_fields = $result->field_count;
    $row2       = $mysqli->query('SHOW CREATE TABLE '.$table );

    $row2       = $row2->fetch_row();
    $return    .= 
"\n
-- ---------------------------------------------------------
--
-- Table structure for table : `{$table}`
--
-- ---------------------------------------------------------

".$row2[1].";\n";

    for ($i = 0; $i < $num_fields; $i++) 
    {

      $n = 1 ;
      while( $row = $result->fetch_row() )
      { 
        

        if( $n++ == 1 ) { # set the first statements
          $return .= 
"
--
-- Dumping data for table `{$table}`
--

";  
        /**
         * Get structural of fields each tables
         */
        $array_field = array(); #reset ! important to resetting when loop 
         while( $field = $result->fetch_field() ) # get field
        {
          $array_field[] = '`'.$field->name.'`';
          
        }
        $array_f[$table] = $array_field;
        // $array_f = $array_f;
        # endwhile
        $array_field = implode(', ', $array_f[$table]); #implode arrays

          $return .= "INSERT INTO `{$table}` ({$array_field}) VALUES\n(";
        } else {
          $return .= '(';
        }
        for($j=0; $j<$num_fields; $j++) 
        {
          
          $row[$j] = str_replace('\'','\'\'', preg_replace("/\n/","\\n", $row[$j] ) );
          if ( isset( $row[$j] ) ) { $return .= is_numeric( $row[$j] ) ? $row[$j] : '\''.$row[$j].'\'' ; } else { $return.= '\'\''; }
          if ($j<($num_fields-1)) { $return.= ', '; }
        }
          $return.= "),\n";
      }
      # check matching
      @preg_match("/\),\n/", $return, $match, false, -3); # check match
      if( isset( $match[0] ) )
      {
        $return = substr_replace( $return, ";\n", -2);
      }

    }
    
      $return .= "\n";

  }

$return = 
"-- ---------------------------------------------------------
--
-- SIMPLE SQL Dump
-- 
-- nawa (at) yahoo (dot) com
--
-- Host Connection Info: ".$mysqli->host_info."
-- Generation Time: ".date('F d, Y \a\t H:i A ( e )')."
-- Server version: ".mysql_get_server_info()."
-- PHP Version: ".PHP_VERSION."
--
-- ---------------------------------------------------------\n\n

SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";
SET time_zone = \"+00:00\";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
".$return."
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";

# end values result

    @ini_set('zlib.output_compression','Off');
    $gzipoutput = gzencode( $return, 9);

 if(  @ file_put_contents( $fullname, $gzipoutput  ) ) { # 9 as compression levels
  
    $result = $name.'.sql.gz'; # show the name
  
  } else { # if could not put file , automaticly you will get the file as downloadable

    $result = false;   
    // various headers, those with # are mandatory
    header('Content-Type: application/x-gzip'); // change it to mimetype
    header("Content-Description: File Transfer");
    header('Content-Encoding: gzip'); #
    header('Content-Length: '.strlen( $gzipoutput ) ); #
    header('Content-Disposition: attachment; filename="'.$name.'.sql.gz'.'"');
    header('Cache-Control: no-cache, no-store, max-age=0, must-revalidate');
    header('Connection: Keep-Alive');
    header("Content-Transfer-Encoding: binary");
    header('Expires: 0');
    header('Pragma: no-cache');
    
    echo $gzipoutput;

  }

       } else {

         $result = '<p>Error when executing database query to export.</p>'.$mysqli->error;
       
       }
     }

 } else {
      $result = '<p>Wrong mysqli input</p>';
 }
 
 if( $mysqli && ! $mysqli->error ) {
      @$mysqli->close();
 }

  return $result;

}

example usage :

// backup

$dir  = dirname(__file__); // directory files
$name = 'backup'; // name sql backup
print_r( backup_database( $dir, $name, 'localhost', 'user', 'password', 'databasename') ); // execute

// import
$file = dirname(__FILE__).'/data.sql'; // sql data file
$args = file_get_contents($file); // get contents
print_r( mysqli_import_sql( $args, 'localhost',  'user', 'password', 'databasename') ); // execute

Hi. Do I need to unzip gzipped file in mysqli_import_sql() function, because all that I can get is function creates database with tables, but tables are empty? And why do i need to add .'/data.sql' string to end of filename?

Btw, backup_database() works perfectly and when I try to manually import in phpmyadmin, everything works.

do u know if this work with store procedures?.. I've written a number of these, but all fails to import store procedures.

Hi,
the 'Content-Type: application/x-download' seems not valid. It need x-gzip instead

Hi sorry just reply for along time :D
@dinzin : yap thats is true, because I create this with quick and not test it at all..
thanks for being correct it :)
@b4oshany : for store procedure , I was not test it , because I dont have implement it :)

On line 129, wouldn't it be better to use a "while(true) " loop with a counter variable, instead of the "for" loop that you are using? I was getting some warnings for the "file()" you have in the "for" loop and they got resolved with the "while" loop.

I will try to reolve that :) @acropod , because this is old way

So a customer came to me and said to update his site. He paid a little bit. His first issue, not in the loop and does not know how. He also ran off his help. His second issue, he was locked out of drupal. His third issue, database is remote on a host that forgot to delete it when the account was closed in 2011. Drupal uses a mysqli path the gain access to that obsolete hosting platform and has been running this way for years. I am now in drupal and have compared its db content with that of the localhost. I verified it really is going to the old host that should not be available anymore. My issue is that I am novice with php functions and it appears your function is the only way I am going to export this database from that old host. I need a little clarification on how to use it. please contact me directly about this at Patrick_barnes (at) midnighttech (dot) com.

Sorry All .. :)
I'm often open my gist , and gist not sent me notification via email :)

Hello and thanks!
How can i fix this error???
Fatal error: Call to a member function fetch_row() on a non-object