Backup Your SQL Data

Backing up your database need not be that complicated.

Introduction

It’s always a good idea to backup your data at regular intervals.  And backing up your database need not be that complicated.

So how do you do it?

It’s easy! Check out this PHP class.

class DatabaseBackupHelper {
    public function buildBackupSql($host, $user, $pass, $databaseName, $tables = '*') {
        $headerTables = ! is_array ( $tables ) ? $tables : implode ( ', ', $tables );
        $data = "\n/*---------------------------------------------------------------\n";
        $data .= "  SQL DB BACKUP " . date ( "d.m.Y H:i" ) . " " . "\n";
        $data .= "  HOST: {$host}\n";
        $data .= "  DATABASE: {$databaseName}\n";
        $data .= "  TABLES: {$headerTables}\n";
        $data .= "---------------------------------------------------------------*/\n";
        $data .= "\n/*\n";
        $data .= "CREATE DATABASE IF NOT EXISTS `{$databaseName}` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;\n";
        $data .= "USE `{$databaseName}`;\n";
        $data .= "*/\n";
        
        $link = mysqli_connect ( $host, $user, $pass );
        mysqli_select_db ( $link, $databaseName );
        mysqli_query ( $link,"SET NAMES `utf8` COLLATE `utf8_general_ci`"); // Unicode
        
        if ($tables == '*') { // get all of the 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 );
        }
        
        foreach ( $tables as $table ) {
            $data .= "\n/*---------------------------------------------------------------" . "\n  TABLE: `{$table}`" . "\n  ---------------------------------------------------------------*/\n";
            $data .= "DROP TABLE IF EXISTS `{$table}`;\n";
            $res = mysqli_query ( $link, "SHOW CREATE TABLE `{$table}`" );
            $row = mysqli_fetch_row ( $res );
            $data .= $row [1] . ";\n";
            
            $result = mysqli_query ( $link, "SELECT * FROM `{$table}`");
            $num_rows = mysqli_num_rows ( $result );
            
            if ($num_rows > 0) {
                $vals = Array ();
                $z = 0;
                for($i = 0; $i < $num_rows; $i ++) {
                    $items = mysqli_fetch_row ( $result );
                    $vals [$z] = "(";
                    for($j = 0; $j < count ( $items ); $j ++) {
                        if (isset ( $items [$j] )) {
                            $vals [$z] .= "'" . mysqli_real_escape_string (  $link, $items [$j] ) . "'";
                        } else {
                            $vals [$z] .= "NULL";
                        }
                        if ($j < (count ( $items ) - 1)) {
                            $vals [$z] .= ",";
                        }
                    }
                    $vals [$z] .= ")";
                    $z ++;
                    
                    if (($i + 1) % 200 == 0) {
                        $data .= "INSERT INTO `{$table}` VALUES\n    ";
                        $data .= implode ( ",\n    ", $vals ) . ";\n";
                        $vals = array ();
                        $z = 0;
                    }
                }
                $data .= "INSERT INTO `{$table}` VALUES\n    ";
                $data .= implode ( ",\n    ", $vals ) . ";\n";
            }
        }
        mysqli_close ( $link );
        return $data;
    }
}

This class creates all the  SQL you need to recreate your database tables and data.