Jump to content

Backing up mysql database help


jcac

Recommended Posts

I'm trying to create a page that allows people to backup their database on a web page but I'm having trouble with the ending of it.

 

<?php

 

require("../include/config.php");

 

    $tables = array();

    $qTables = mysql_query("SHOW TABLES");

    while($row = mysql_fetch_row($qTables))

    {

      $tables[] = $row[0];

    }

 

  foreach($tables as $tab1)

  {

 

    $return.= "DROP TABLE IF EXISTS `".$tab1."`;";

    $row2 = mysql_fetch_row(mysql_query("SHOW CREATE TABLE `" . $tab1 . "`"));

    $return.= "\n\n".$row2[1].";\n\n";

 

 

    $result = mysql_query("SELECT * FROM ".$tab1) or die(mysql_error());

    $num_fields = mysql_num_fields($result);

   

    $return.= "INSERT INTO `".$tab1."`";

$col = mysql_query('SELECT * FROM '.$tab1);

$a = 0;

$return.= " (";

while ($a < mysql_num_fields($col)) {

$meta = mysql_fetch_field($col, $a);

 

$return.= "`" . "$meta->name";

$a++;

if ($a < mysql_num_fields($col)) {

$return.= "`,";

} else {

$return.= "`";

}

}

$return.= ")";

$return.=" VALUES\n(";

   

for ($i = 0; $i < $num_fields; $i++){

while($row = mysql_fetch_row($result)){

 

        for($j=0; $j<$num_fields; $j++){

          if (isset($row[$j])) {

  $return.= "'".$row[$j]."'" ;

  } else {

  $return.= "''";

  }

 

          if ($j < ($num_fields-1)) {

  $return.= ",";

  }

 

if (j < ($num_fields-1)){

$return.= "),\n(";

} else {

$return.= ");\n";

}

 

}

}  

}

}

 

$handle = fopen("db-backup-".time()."-".(md5(implode(",",$tables))).".sql","w+");

fwrite($handle,$return);

 

  ?>

 

At this part:

 

if (j < ($num_fields-1)){

$return.= "),\n(";

} else {

$return.= ");\n";

}

 

If it has finished going through all of the values it will put ); at the end and if it hasn't it will put ), and then continue with the next one. The problem I'm having is it's only doing the ),

 

Can someone help me please?

 

Link to comment
Share on other sites

<?php

####################################################################
# WIZECHO Database Backup - Copyright © 2010 Wizecho Software. All Rights #
# Reserved. #
# This file may be redistributed in whole or significant part with this copy write attached #
# http://www.wizecho.com/nav=php&s=php_mysql_backup #
####################################################################

//*************** config ***************//
$mysql_host='localhost';
$mysql_database='name'; // database name
$mysql_username='user'; // user name
$mysql_password='password'; // password

$storing_dir = "/home/user/html/backups/"; // directory path with an ending slash

//************* end config ************//
$file_name = $mysql_database."_".date('YmdHis').".sql";

$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link){
  fwrite($fh, 'Could not connect: ' . mysql_error());
  exit();
}

$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected){
  fwrite($fh, 'Can\'t use $mysql_database : ' . mysql_error());
  exit();
}

$myFile = $storing_dir . $file_name;
$fh = fopen($myFile, 'w') or die("can't open file");
_mysqldump($mysql_database);
fclose($fh);

function _mysqldump($mysql_database){
global $fh;
_mysqldump_schema_structure($mysql_database);
$sql="show tables;";
$result= mysql_query($sql);
if( $result){

    while( $row= mysql_fetch_row($result)){
    _mysqldump_table_structure($row[0]);
    _mysqldump_table_data($row[0]);
    }
}else{
    $content = "/* no tables in $mysql_database */\n";
    fwrite($fh, $content);
}
mysql_free_result($result);
}
function _mysqldump_schema_structure($schema){
global $fh;
fwrite($fh, "/* database : `$schema` */\n");
$sql="show create schema `$schema`; ";
$result=mysql_query($sql);
if( $result){
    if($row= mysql_fetch_assoc($result)){
    fwrite($fh, $row['Create Database'].";\n\n");
    }
}
fwrite($fh, "USE `$schema`; \n\n");
mysql_free_result($result);
}

function _mysqldump_table_structure($table){
global $fh;
fwrite($fh, "/* Table structure for table `$table` */\n");
fwrite($fh, "DROP TABLE IF EXISTS `$table`;\n\n");
$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result){
    if($row= mysql_fetch_assoc($result)){
    fwrite($fh, $row['Create Table'].";\n\n");
    }
}
mysql_free_result($result);
}

function _mysqldump_table_data($table){
global $fh;
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result){
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

if( $num_rows > 0){
fwrite($fh, "/* dumping data for table `$table` */\n");

$field_type=array();
$i=0;
while( $i < $num_fields){
$meta= mysql_fetch_field($result, $i);
array_push($field_type, $meta->type);
$i++;
}

//print_r( $field_type);
fwrite($fh, "insert into `$table` values\n");
$index=0;
while( $row= mysql_fetch_row($result)){
fwrite($fh, "(");
for( $i=0; $i < $num_fields; $i++){
if( is_null( $row[$i])){
fwrite($fh, "null");
}else{
switch( $field_type[$i]){
case 'int':
fwrite($fh, $row[$i]);
break;
case 'string':
case 'blob' :
default:
fwrite($fh, "'".mysql_real_escape_string($row[$i])."'");

}
}
if( $i < $num_fields-1)
fwrite($fh, ",");
}
fwrite($fh, ")");

if( $index < $num_rows-1)
fwrite($fh, ",");
else
fwrite($fh, ";");
fwrite($fh, "\n");

$index++;
}
}
}
mysql_free_result($result);
fwrite($fh, "\n");
}

echo 'All Done!!';

?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.