I am trying to create a form that exports my mysql database , and can also reimport the mysql database using like a form. I am trying to do this in PHP any ideas on how it can be done or if it''s already been done? I know there is phpmyadmin and what not, but I really just need a simple form to export the database and reimport it.
thanks
export the data into what csv file
Are you talking about the entire database?? What exactly are you trying to achieve?
Does this help?
I figured it out myself this is what I did
This is the restore part
<?php // mysql_backup_restore.php
error_reporting(E_ALL);
// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";
// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');
// IF NOTHING POSTED YET
if (empty($_POST))
{
// ASK CLIENT TO CHOOSE ONE FOR RESTORE
$x = FALSE;
echo "<form method=\"post\">\n";
echo "RESTORE A DATA BASE:<br/>";
foreach ($my_dir as $my_file)
{
if (!ereg('^mysql', $my_file)) continue;
echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
$x = TRUE;
}
if (!$x) die('NO BACKUP FILES FOUND');
echo "<input type=\"submit\" />\n";
echo "</form>\n";
die();
}
// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");
// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];
// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$", '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
}
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// LOAD THE BACKUP DATA QUERIES
$sqls = file('backups/' . $fname);
// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
if (!$res = mysql_query($sql))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>QUERY FAIL: ";
echo "<br/>$sql <br/>";
die($errmsg);
}
}And this is the backup part
<?php // mysql_backup_dump.php
error_reporting(E_ALL);
// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB CONNECTION: ";
echo "<br/> $errmsg <br/>";
die();
}
// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB LIST: ";
echo "<br/> $errmsg <br/>";
die();
}
// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
$db_names[] = $row->Database ;
}
// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
// NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
echo "<form method=\"post\">\n";
echo "BACK UP A DATA BASE:<br/>";
foreach ($db_names as $db_name)
{
echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
}
echo "<input type=\"submit\" />\n";
echo "</form>\n";
die();
}
// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];
// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
$errmsg = mysql_errno() . ' ' . mysql_error();
echo "<br/>NO DB SELECTION: ";
echo "<br/> $errmsg <br/>";
die('NO DATA BASE');
}
// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp = fopen($bkup, "w");
// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
$tables[] = $s[0];
}
// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{
// WRITE THE DROP TABLE STATEMENT
fwrite($fp,"DROP TABLE `$table`;\n");
// GET THE CREATE TABLE STATEMENT
$res = mysql_query("SHOW CREATE TABLE `$table`");
if (!$res) die( mysql_error() );
$cre = mysql_fetch_array($res);
$cre[1] .= ";";
$txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
fwrite($fp, $txt . "\n");
// GET THE TABLE DATA
$data = mysql_query("SELECT * FROM `$table`");
$num = mysql_num_fields($data);
while ($row = mysql_fetch_array($data))
{
// MAKE INSERT STATEMENTS FOR ALL THE VALUES
$txt = "INSERT INTO `$table` VALUES(";
for ($i=0; $i < $num; $i++)
{
$txt .= "'".mysql_real_escape_string($row[$i])."', ";
}
$txt = substr($txt, 0, -2);
fwrite($fp, $txt . ");\n");
}
}
// ALL DONE
fclose($fp);
// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";
hi, thanks alex. I've tried and succeeded. But if there is data blank / null, then the files you have backed up cannot be imported again or error. is there a solution ? thanks.
The solution to the blank/null value problem is as follows. You need to determine the field type to determine whether or not the value should have quotes or not, and if the value is supposed to be NULL then insert NULL without the quotes. Like so:
// MAKE INSERT STATEMENTS FOR ALL THE VALUES
$script = "INSERT INTO `$table` VALUES(";
for ($i=0; $i < $columncount; $i++)
{
$fieldtype = mysql_field_type($result,$i);
$value = mysql_real_escape_string($row[$i]);
if($fieldtype == "time" or $fieldtype == "date" or $fieldtype == "datetime" or $fieldtype == "timestamp"){
if($value == ""){
$script .= "NULL, ";
}else{
$script .= "'".$value."', ";
}
}elseif($fieldtype == "string" or $fieldtype == "blob"){
$script .= "'".$value."', ";
}else{
if($value == "") $value = "NULL";
$script .= $value.", ";
}
}
$script = substr($script, 0, -2);
$script = $script.");";Oh, by the way. I renamed $txt as $script. $num becomes $columncount and $result is $data in the original script.
** f*ck I hate necroposters **
It's for the benefit of anybody else coming to look for a backup script in the future.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.