I was going to go through your code but thought I may as well just give you one I made earlier from one of my sites as it's identical to what your asking for. I can tell you at a quick glance though that the reason why your DB was emptying on click is because you were dropping the database in your exec command using --add-drop-table:
How to drop and re-populate mysql databases?
This is the code extracted from one of my backend admin page sites to do random MySQL dumps and backups via broswer when needed. It's very simple but extremly effective if secured properly. The form is set using radio buttons for either download or backup and then executes the local mysqldump from cmdline. Backup will output if it was a success by testing to see if the file has actually backed up and download will download the file which is pretty much put together using the example from the PHP site here. I've shaped it a little so you can add it to your own project
<?php
$username = "root";
$password = "root";
$host = "localhost";
$dbname = "new";
$path = "/Applications/MAMP/htdocs/html/test123.sql";
$backup = exec('/Applications/MAMP/Library/bin/mysqldump --user=' . $username . ' --password='. $password .' --host=' . $host . ' ' . $dbname . ' > ' . $path . '');
if (isset($_POST['backup'])) {
if (file_exists($path)) {
echo "Backup Success";
echo "<br>";
} else {
echo "Backup failed!";
echo "<br>";
echo "Backup of " . $dbname . " does not exist in " . $path;
}
}
if (isset($_POST['download'])) {
if (file_exists($path)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.basename($path).'"');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($path)); //Whoops...Forgot to change variable!
readfile($path); //Whoops...Forgot to change variable!
exit;
} else {
echo "File does not exist!";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Backup Page</title>
</head>
<body>
<form method="post">
<input type="radio" name="backup"> Backup <br>
<input type="radio" name="download"> Download <br>
<input type="submit" value="MySQL Backup">
</form>
</body>
</html>
