Help with php script to export mysql data and import data

Question

alexgv14 0 Light Poster

14 Years Ago

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

php

5 Contributors
7 Replies
176 Views
2 Years Discussion Span
Latest Post 12 Years Ago Latest Post by vincentwansink

All 7 Replies

HITMANOF44th 4 Posting Whiz in Training

14 Years Ago

export the data into what csv file

diafol

14 Years Ago

Are you talking about the entire database?? What exactly are you trying to achieve?

Does this help?

http://davidwalsh.name/backup-mysql-database-php

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

alexgv14 0 Light Poster · Answer 1 · 2009-06-09T04:40:54+00:00

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";

ivanichi 0 Light Poster · Answer 2 · 2012-02-26T13:21:23+00:00

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.

vincentwansink 0 Newbie Poster · Answer 3 · 2012-03-17T23:55:31+00:00

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.

diafol · Answer 4 · 2012-03-18T03:45:01+00:00

diafol

12 Years Ago

** f*ck I hate necroposters **

vincentwansink 0 Newbie Poster · Answer 5 · 2012-03-18T04:44:41+00:00

It's for the benefit of anybody else coming to look for a backup script in the future.

Help with php script to export mysql data and import data

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers