The database is one of the main layers of any project we build. And if you’re not using an ORM to manage your databases, it becomes a bit difficult to manage databases. Especially while working on a project from multiple devices. For example, you can use GitHub to sync your file changes across multiple devices. But when it comes to the database, we have to go through multiple steps to get the job done. Such as manually exporting it from one device, moving the exported backup to another one, and importing it there. Now, what if you can create your own database backup and restore system? So, you can sync your database using GitHub and all other files! That would be awesome, right? Let’s drive then!
Backup Idea
Okay, now, the idea is pretty simple. We will create a PHP file that we will use to complete the task. Like, before committing our changes to Github, we will simply visit the file and click a button called backup. This will create a backup of our database and save it in the current project directory. You just have to include this backup file in your git commit before your subsequent push request!
– woa woa woa! But I don’t use GitHub. What about me?
Well, the principle is still the same for you. You can still include the file in your project, click the backup button, and move the backup file with your other files.
Restore Idea
Once you have the backup on the Github or somewhere else, you can pull or download your project on a different device and click the restore button on the same file. This will search for the backup file on our current directory. And if it finds one, It will restore the database backup file into the active database connection.
Page Design
Before developing the database backup and restore system, we first need to create an interface with just two buttons. One that says backup, and another one will say restore. The buttons will act accordingly if we click on them. Please create a PHP file and use the code below to get started, or you can create your own design.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Database Backup and Restore system using PHP</title>
<style>
.blogdesire-form{
margin: 0;
padding: 0;
position: fixed;
top: 0;
left:0;
display: flex;
flex-direction: column;
justify-content: center;
align-items: center;
width: 100vw;
height: 100vh;
}
.blogdesire-button{
margin: 10px;
border: none;
padding: 10px 20px;
color: white;
background: green;
}
</style>
</head>
<body>
<form method="POST" class="blogdesire-form">
<button name="backup" class="blogdesire-button">
Backup
</button>
<button name="restore" class="blogdesire-button">
Restore
</button>
<?php if(@$message): ?>
<p><?php echo $message;?></p>
<?php endif; ?>
</form>
</body>
</html>Database Backup and Restore Function
Once you have the design ready, it’s time to make the buttons functional. So they can do their job perfectly. Place the code below at the very beginning of your PHP file that you just created in the last section of the article. Please change the database configuration before using the file in your projects.
<?php
$con = mysqli_connect("localhost","DB_USER","DB_PASSWORD","DB_NAME");
if(isset($_POST['backup'])){
$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($con, $sql);
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
$sqlScript = "";
foreach ($tables as $table) {
$query = "SHOW CREATE TABLE $table";
$result = mysqli_query($con, $query);
$row = mysqli_fetch_row($result);
$sqlScript .= "\n\n" . $row[1] . ";\n\n";
$query = "SELECT * FROM $table";
$result = mysqli_query($con, $query);
$columnCount = mysqli_num_fields($result);
for ($i = 0; $i < $columnCount; $i ++) {
while ($row = mysqli_fetch_row($result)) {
$sqlScript .= "INSERT INTO $table VALUES(";
for ($j = 0; $j < $columnCount; $j ++) {
$row[$j] = $row[$j];
if (isset($row[$j])) {
$sqlScript .= '"' . mysqli_real_escape_string($con,$row[$j]) . '"';
} else {
$sqlScript .= '""';
}
if ($j < ($columnCount - 1)) {
$sqlScript .= ',';
}
}
$sqlScript .= ");\n";
}
}
$sqlScript .= "\n";
}
if(!empty($sqlScript))
{
$backup_file_name = __DIR__.'/_backup_.sql';
$fileHandler = fopen($backup_file_name, 'w+');
$number_of_lines = fwrite($fileHandler, $sqlScript);
fclose($fileHandler);
$message = "Backup Created Successfully";
}
}
if(isset($_POST['restore'])){
$sql = '';
$error = '';
if (file_exists(__DIR__.'/_backup_.sql')) {
// Deleting starts here
$query_disable_checks = 'SET foreign_key_checks = 0';
mysqli_query($con, $query_disable_checks);
$show_query = 'Show tables';
$query_result = mysqli_query($con, $show_query);
$row = mysqli_fetch_array($query_result);
while ($row) {
$query = 'DROP TABLE IF EXISTS ' . $row[0];
$query_result = mysqli_query($con, $query);
$show_query = 'Show tables';
$query_result = mysqli_query($con, $show_query);
$row = mysqli_fetch_array($query_result);
}
$query_enable_checks = 'SET foreign_key_checks = 1';
mysqli_query($con, $query_enable_checks);
// Deleting ends here
$lines = file(__DIR__.'/_backup_.sql');
foreach ($lines as $line) {
if (substr($line, 0, 2) == '--' || $line == '') {
continue;
}
$sql .= $line;
if (substr(trim($line), - 1, 1) == ';') {
$result = mysqli_query($con, $sql);
if (! $result) {
$error .= mysqli_error($con) . "\n";
}
$sql = '';
}
}
if ($error) {
$message = $error;
} else {
$message = "Database restored successfully";
}
}else{
$message = "Uh Oh! No backup file found on the current directory!";
}
}
?>This restoring system first deletes all existing data to avoid conflict errors. So, make sure you’re restoring a complete database before pressing the restore button. Otherwise, remove the code that handles the deleting part. I have marked that part inside // Deleting starts here and // Deleting ends here comment block.
