• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

MySQL Dump/Back-up with PHP?

SPeedY_B

I may actually be insane.
#1
Topic says it all really, basically I want to back-up a MySQL table (the whole thing) to a standardised .tar.gz file which I can re-upload into PHPMyAdmin (or make my own form to do it :D).. and basically haven't a clue how to do so, any pointer in the right direction appreciated :D
 

Kush

High On Life!
#2
ok that was in chinese to me, btw thats like the second time i see speedy ask for help ;) wow long time since u did! go speedy go speeedy!
 

SPeedY_B

I may actually be insane.
#3
hehe :p

I know of mysqldump from the command line and that I could just shell_exec(); it, I just wondered if there was a method from within PHP to do it more 'natively' so to speak.
 
#6
It's been a while since I played with this, so you have to look for yourself how this works. Also, I didn't create the functions myself, I modified them a bit to fit my needs, but I forgot which website I got it from (badly documented :eek:).

Code:
<?php
include 'sql.php';
    define("dbserver", $server);
    define("dbuser", $user);
    define("dbpass", $pass);
    define("dbname", $db);

function get_structure()
{
    mysql_connect(dbserver, dbuser, dbpass);
    mysql_select_db(dbname);
    $tables = mysql_list_tables(dbname);
    while ($td = mysql_fetch_array($tables))
    {
        $table =  $td[0];
        $r = mysql_query("SHOW CREATE TABLE `$table`");
        if ($r)
        {
            $insert_sql = "";
            $d = mysql_fetch_array($r);
            $d[1] .= ";";
            $SQL[] = str_replace("\n", "", $d[1]);
            $table_query = mysql_query("SELECT * FROM `$table`");
            $num_fields = mysql_num_fields($table_query);
            while ($fetch_row = mysql_fetch_array($table_query)){
              $insert_sql .= "INSERT INTO $table VALUES(";
              for ($n=1;$n<=$num_fields;$n++){
                $m = $n - 1;
                $insert_sql .= "'".$fetch_row[$m]."', ";
              }
              $insert_sql = substr($insert_sql,0,-2);
              $insert_sql .= ");\n";
            }
            if ($insert_sql != ""){
              $SQL[] = $insert_sql;
            }
        }
    }
    return $SQL;
}

function save_structure($filename)
{
    $sql = get_structure();
    header('Content-type: plain/text');
    header('Content-Disposition: attachment; filename="'.$filename.'"');
    $sql = implode("\r", $sql);
    print $sql;
    exit;
}

function create_structure ($filename)
{
    mysql_connect(dbserver, dbuser, dbpass);
    mysql_select_db(dbname);
    $lines = explode("\n", file_get_contents($filename));
    $result = true;
    foreach ($lines as $line)
        mysql_query($line) OR DIE("Error while importing: ".mysql_error());
        //$result = $result && mysql_query($line);
    return $result;
}
if ($_GET['action'] == "export"){
  save_structure("backup.sql");
}elseif ($_GET['action'] == "import"){
  create_structure($_FILES['filename']['tmp_name']);
  print "Import succeeded!<br>";
}
?>

<script type="text/javascript">
function checkForm(){
  if (document.backupform.filename.value == ""){
    alert('Please select a file first');
    return false;
  }else{
    return true;
  }
}
function toggle_display(idname){
  obj = fetch_object(idname);
  if (obj){
    if (obj.style.display == "none"){
      obj.style.display = "";
    }else{
      obj.style.display = "none";
    }
  }
  return false;
}
</script>
<center>
<div id="export_text" style="display : none;">Export Succeeded!</div>
<form method="post" action="?action=import" enctype="multipart/form-data" onSubmit="return checkForm();" name="backupform">
 <input type="file" name="filename">
 <input type="submit" value="Import tables">
</form>

<input type="button" value="Backup tables" onClick="javascript:location.href='backup.php?action=export'"></center>
 

Members online

No members online now.

Latest posts

Latest profile posts

Hello, is there anybody in there? Just nod if you can hear me ...
Xie
What a long strange trip it's been. =)

Forum statistics

Threads
61,961
Messages
673,239
Members
89,014
Latest member
sanoravies