Export MySQL database using PHP
Welcome back to shortlearner.com, today we will see how to export database with the help of php script.
It is always a good practice to take backup of database regularly. There are usually three way to take backup of
database.
1.using SQL Command through PHP.
2.Using phpmyadmin interface.
3.Using mysqldump through php.
Today we will right a php script that will help to export database.
Also Read :
How to create a facebook like chat system using PHP ,AJAX and MySQL.
How to Install PHP on CentOS.
How to integrate Razorpay Payment Gateway using PHP.
Send Mail Without SMTP Authentication in PHP.Create a dynamic progress-bar using PHP.
Generate Enrollment-number using PHP.
Decode JSON format using PHP function
all you should need to provide database credentials to the function, as well as an array of tables you’d like to backup.
after making a connection we will run some MySQL command in our script that will help us to export our data.
<?php
// Database configuration
$host = "localhost";
$username = "root";
$password = "";
$database_name = "find";
// Get connection object and set the charset
$conn = mysqli_connect($host, $username, $password, $database_name);
$conn->set_charset("utf8");
// Get All Table Names From the Database
$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
$sqlScript = "";
foreach ($tables as $table) {
// Prepare SQLscript for creating table structure
$query = "SHOW CREATE TABLE $table";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($result);
$sqlScript .= "\n\n" . $row[1] . ";\n\n";
$query = "SELECT * FROM $table";
$result = mysqli_query($conn, $query);
$columnCount = mysqli_num_fields($result);
// Prepare SQLscript for dumping data for each table
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 .= '"' . $row[$j] . '"';
} else {
$sqlScript .= '""';
}
if ($j < ($columnCount - 1)) {
$sqlScript .= ',';
}
}
$sqlScript .= ");\n";
}
}
$sqlScript .= "\n";
}
if(!empty($sqlScript))
{
// Save the SQL script to a backup file
$backup_file_name = $database_name . '_backup_' . time() . '.sql';
$fileHandler = fopen($backup_file_name, 'w+');
$number_of_lines = fwrite($fileHandler, $sqlScript);
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);
}
?>
Also Read : Shutdown system using PHP script.
Print numbers from 1 to N without using loop in PHP.
Import data from text file to MySQL using PHP.