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 Perform MySQL backup using PHP.
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();
if(!empty($_POST["table"]) && count($_POST["table"]) > 0) {
$databaseBackup->backupDatabase($_POST["table"]);
}
$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 = "";
private $database = "travel_fares";
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);
}
}
?>