PHP Database Backup Client for MySQL

There are various database clients available in the market, for example, SQLyog, HeidiSQL, phpMyAdmin and more. These are popular database clients. In this tutorial, we are going to create our own client interface for taking database backup.

This will be a series of articles and in future, I will be enhancing it and eventually building a full-fledged database client using PHP. Don’t worry about reinventing the wheel, its all for educational purposes only ;-)

In the last tutorial, we saw how to export the database to a file and download the backup file. In this client interface, I listed all the tables from the database as specified in the configuration.

I have added checkboxes for each table of the list to select the tables. On submitting the selected tables, the PHP code will process the database export by writing the database table schema and the data in form of SQL.

On successful export, the backup file will be downloaded to the user’s browser.

Client Interface with Database Table Checklist

This code is for getting all the tables from the database and to list them with the checkbox. The checkboxes are used to select the tables to be exported into a backup file.

I have added a checkbox in the header to check/uncheck all the tables. After selecting the tables by using this checkbox, the submit action will validate the count of the selected table.

If no tables are selected, then the validation function will return false and display the error message.

<?php
require_once "DatabaseBackup.php";

$databaseBackup = new DatabaseBackup();

$tables = $databaseBackup->getAllTable();

?>

<form name="frmDatabaseBackup" method="post" action="" onSubmit="return validateCheckbox();">
<div class="checkbox-row-header"><div style="
    float: left;
    vertical-align: text-top;
    padding-top: 6px;
"><input type="checkbox" name="" id="checkbox-header"> Check/Uncheck All</div><input type="submit" name="submit" id="btnAction" value="Export"></div>
<?php 
if(!empty($tables))
{
    foreach($tables as $table) 
    {
?>
<div class="checkbox-row">
<input type="checkbox" class="checkbox-input" data-table-name="<?php echo $table; ?>"> <?php echo $table; ?>
<input type="hidden" name="table[]" vaule="<?php echo $table; ?>" class="checkbox-tablename">
</div>
<?php
    }
}
?>
</form>

The jQuery script to validate the checkbox input on the submit event is,

<script src="jquery-3.2.1.min.js" type="text/javascript"></script>
<script>
$(document).ready(function () {
$("#checkbox-header").on("change", function() {
    if ($(this).is(':checked')) {
       $(".checkbox-input").prop("checked",true);
    } else {
    	   $(".checkbox-input").prop("checked",false);
    }
});

});
function validateCheckbox() {
    var valid = true;
    var checkedLength = $(".checkbox-input:checked").length;
    if(parseInt(checkedLength) <= 0) {
        $("#error-message").show();
        $("#error-message").html("Check atleast 1 Table to Export");
        valid= false;
    }
    if(valid) {
        $(".checkbox-input").each(function(){
            if($(this).prop("checked") == true) {
            	    var tableName = $(this).data("table-name");
                $(this).next(".checkbox-tablename").val(tableName);
            } else {
                $(this).next(".checkbox-tablename").val("");
            }
        });
    }
    return valid;
}
</script>

PHP Class DatabaseBackup to Create and Download SQL Backup

This PHP class contains all functions for processing through the database backup operation. On submitting the selected tables, the table array is posted to the PHP.

In PHP code, it calls the backupDatabase() with the selected tables array as its argument. In this function, I iterate this array to get the SQL script for each table schema/data by calling getSQLScript() function.

After creating the backup SQL script, it will be written to a .sql file which is downloaded to the user’s browser.

<?php

class DatabaseBackup
{

    private $host = "localhost";

    private $user = "root";

    private $password = "test";

    private $database = "phpsamples";

    private static $conn;

    function __construct()
    {
        $this->conn = mysqli_connect($this->host, $this->user, $this->password, $this->database);
        $this->conn->set_charset("utf8");
    }

    public static function getConnection()
    {
        if (empty($this->conn)) {
            new Database();
        }
    }
    
    function getAllTable()
    {
        $tables = array();
        
        $sql = "SHOW TABLES";
        $result = mysqli_query($this->conn, $sql);
        
        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
        }
        return $tables;
    }

    /*
     * To backup multiple tables
     * param: $tables is a string containing more than one table names seperated with comma
     */
    function backupDatabase($tables)
    {
        $fileString = "";
        foreach ($tables as $table) {
            if(!empty($table)) {
                $fileString .= $this->getSQLScript($table);
            }
        }
        
        if(strlen($fileString) > 0) {
            $this->saveToBackupFile($fileString);
        }
    }

    function getSQLScript($tableName)
    {
        // Shows the CREATE TABLE statement that created the given table.
        $sqlShowQuery = "SHOW CREATE TABLE $tableName";
        $result = mysqli_query($this->conn, $sqlShowQuery);
        $row = mysqli_fetch_row($result);
        
        $fileString .= "\n\n" . $row[1] . ";\n\n";
        
        $sqlQuery = "SELECT * FROM " . $tableName;
        $result = mysqli_query($this->conn, $sqlQuery);
        
        // Return the number of fields (columns)
       $fieldCount = mysqli_num_fields($result);
        
        
        for ($i = 0; $i < $fieldCount; $i ++) {
            while ($row = mysqli_fetch_row($result)) {
                $fileString .= "INSERT INTO $tableName VALUES(";
                for ($j = 0; $j < $fieldCount; $j ++) {
                    $row[$j] = $row[$j];
                    
                    if (isset($row[$j])) {
                        $fileString .= '"' . $row[$j] . '"';
                    } else {
                        $fileString .= '""';
                    }
                    if ($j < ($fieldCount - 1)) {
                        $fileString .= ',';
                    }
                }
                $fileString .= ");\n";
            }
        }
        
        $fileString .= "\n";
        return $fileString;
    }

    function saveToBackupFile($fileString)
    {
        // Save the SQL script to a backup file
        $backup_file_name = $this->database . '_backup_' . time() . '.sql';
        $fileHandler = fopen($backup_file_name, 'w+');
        fwrite($fileHandler, $fileString);
        fclose($fileHandler);
        
        // Download the SQL backup file to the browser
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($backup_file_name));
        ob_clean();
        flush();
        readfile($backup_file_name);
        exec('rm ' . $backup_file_name); 
    }
}
?>

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js