Great but drop table is missing
Works fine but I need to add a drop table if exists option to the whole thing. Any advice? Thank you.
<?php function backDb($host, $user, $pass, $dbname, $tables = '*'){ //make db connection $conn = new mysqli($host, $user, $pass, $dbname); if ($conn->connect_error) { } //get all of the tables if($tables == '*'){ $sql = "SHOW TABLES"; $query = $conn->query($sql); while($row = $query->fetch_row()){ $tables[] = $row[0]; } } else{ } //getting table structures $outsql = ''; foreach ($tables as $table) { // Prepare SQLscript for creating table structure $sql = "SHOW CREATE TABLE $table"; $query = $conn->query($sql); $row = $query->fetch_row(); $outsql .= "\n\n" . $row[1] . ";\n\n"; $sql = "SELECT * FROM $table"; $query = $conn->query($sql); $columnCount = $query->field_count; // Prepare SQLscript for dumping data for each table for ($i = 0; $i < $columnCount; $i ++) { while ($row = $query->fetch_row()) { $outsql .= "INSERT INTO $table VALUES("; for ($j = 0; $j < $columnCount; $j ++) { $row[$j] = $row[$j]; $outsql .= '"' . $row[$j] . '"'; } else { $outsql .= '""'; } if ($j < ($columnCount - 1)) { $outsql .= ','; } } $outsql .= ");\n"; } } $outsql .= "\n"; } // Save the SQL script to a backup file $backup_file_name = $dbname . '_backup.sql'; // Download the SQL backup file to the browser } ?>
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <link rel="stylesheet" type="text/css" href="bootstrap4/css/bootstrap.min.css"> </head> <body> <div class="container"> <hr> <div class="row justify-content-center"> <div class="col-sm-6"> <div class="card"> <div class="card-body"> <br> <form method="POST" action="backup.php"> <div class="form-group row"> <div class="col-sm-9"> <input type="text" class="form-control" id="server" name="server" placeholder="ex 'localhost'" required> </div> </div> <div class="form-group row"> <div class="col-sm-9"> <input type="text" class="form-control" id="username" name="username" placeholder="ex 'root'" required> </div> </div> <div class="form-group row"> <div class="col-sm-9"> <input type="text" class="form-control" id="password" name="password" placeholder="db password"> </div> </div> <div class="form-group row"> <div class="col-sm-9"> <input type="text" class="form-control" id="dbname" name="dbname" placeholder="database name to backup" required> </div> </div> </form> </div> </div> </div> </div> </div> </body> </html>
<?php //include our function include 'function.php'; //get credentails via post $server = $_POST['server']; $username = $_POST['username']; $password = $_POST['password']; $dbname = $_POST['dbname']; //backup and dl using our function backDb($server, $username, $password, $dbname); } else{ echo 'Fill up database credentail and name'; } ?>