PHP mysql database backup

Question

rajeesh_rsn 0 Posting Whiz in Training

15 Years Ago

Hi i have created a website for my client using PHP and mysql . The client need to backup a table in database as excel file every day. Well he had no idea about coding and all ... He need to backup using a simple way. Please anyone let me know how can i do it using php script or any easy software for that.....

Please help me
Rajeesh

php

8 Contributors
22 Replies
771 Views
4 Years Discussion Span
Latest Post 10 Years Ago Latest Post by arslanqamar

All 22 Replies

nav33n 472 Purple hazed!

15 Years Ago

http://davidwalsh.name/backup-mysql-database-php A good script to backup the whole database or just a few selected tables.
Note: While generating the backup file, he uses the command drop tablename. You better ignore (comment) that line :)

Edit: I somehow missed 'excel' in your post. This takes the backup on a .txt file (or .sql).

cwarn23 387 Occupation: Genius

15 Years Ago

I came across a similar problem 21 hours ago but have now found that a library has been made for excel files. Note that this library can only write to excel files an not read excel files. You can check it out at http://pear.php.net/package/Spreadsheet_Excel_Writer/docs
At least that appears to be the solution for my problem with excel and php.
Edit:
Actually, if this extension is what you are looking for how would you install it in php-gtk? If this edit does not relate to this topic then I shall post my own topic.

Shanti C 106 Posting Virtuoso

15 Years Ago

And also check for separate tables..
http://www.bigresource.com/PHP-Import-Excel-file-into-MySQL-database-3GCFOrrb.html#r2WzqLkg

cwarn23 387 Occupation: Genius

15 Years Ago

And also check for separate tables..
http://www.bigresource.com/PHP-Import-Excel-file-into-MySQL-database-3GCFOrrb.html#r2WzqLkg

The only thing wrong with that recourse for my situation is that it requires headers and php-gtk doesn't support the type of headers used in that script. But for the question in post #1 it might do the job. However, to help clear things up, I found an oop project for writing to excel documents located at http://phpexcel.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=10715 I am investigating that oop project/source to see if it would be better than using the pear library I shared ealier. For some reason pear libraries just aren't working due to unmatched compilations. But hopefully the link above should help.

Stefano Mtangoo 455 Senior Poster

14 Years Ago

Glad I was of help!
Tell your friends about Daniweb.
Don't forget to mention Naveen ;)

nav33n commented: haha :D +6

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.

futhonguy 0 Junior Poster in Training · Answer 1 · 2009-12-02T10:19:11+00:00

http://davidwalsh.name/backup-mysql-database-php A good script to backup the whole database or just a few selected tables.
Note: While generating the backup file, he uses the command drop tablename. You better ignore (comment) that line :)
Edit: I somehow missed 'excel' in your post. This takes the backup on a .txt file (or .sql).

Hi Nav33n,
I happened to use the script as suggested by you ref to the website you posted. When i executed it, i got a "Deprecated: Function ereg_replace() is deprecated in C:\Program Files\EasyPHP5.3.0\www\backup.php on line 46" which is at this line

$row[$j] = ereg_replace("\n","\\n",$row[$j]);

and also a "Undefined variable: return " for

$return.= 'DROP TABLE '.$table.';';

will you be able to assist? Thanks

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 2 · 2009-12-02T10:42:14+00:00

Hey, Hi.. Instead of ereg_replace, you an use str_replace. Both serve the same purpose. Replace \n with \\n so it don't get escaped. Anyways, the second 'error' is not an error but a notice. You can declare and initialize a variable to avoid getting notices. [or] you can make use of error reporting

error_reporting(E_ALL ^ E_NOTICE);

in your script.

futhonguy 0 Junior Poster in Training · Answer 3 · 2009-12-02T12:14:50+00:00

Hey, Hi.. Instead of ereg_replace, you an use str_replace. Both serve the same purpose. Replace \n with \\n so it don't get escaped. Anyways, the second 'error' is not an error but a notice. You can declare and initialize a variable to avoid getting notices. [or] you can make use of error reporting
error_reporting(E_ALL ^ E_NOTICE);
in your script.

Thanks nav33,

How i can save the file to another folder?. I notice this was backup on the same folder as where i place all my execute file on the www folder.

Stefano Mtangoo 455 Senior Poster · Answer 4 · 2009-12-02T13:23:45+00:00

try something like this

$myfoldername = "backup";//your folders name 
$handle = fopen(getcwd()./.$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

Stefano Mtangoo 455 Senior Poster · Answer 5 · 2009-12-02T13:24:00+00:00

try something like this

$myfoldername = "backup";//your folders name 
$handle = fopen(getcwd()./.$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

futhonguy 0 Junior Poster in Training · Answer 6 · 2009-12-02T13:52:47+00:00

I got a parse error on line 2.
Had tried and tried.. not sure where the punctuation mark has gone wrong.

Can i change $myfoldername = "backup"; into a directory folder as well, for instance to C:\Document\Backup ??

Stefano Mtangoo 455 Senior Poster · Answer 7 · 2009-12-02T14:50:02+00:00

What error does it give (whole error)?
I'm not having any server to try so I just user a theoretical. I guess I needed to put \\ instead of \
Try this:

$myfoldername = getcwd()."\\MyBackups";//your folders name 
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

futhonguy 0 Junior Poster in Training · Answer 8 · 2009-12-03T06:44:37+00:00

What error does it give (whole error)?
I'm not having any server to try so I just user a theoretical. I guess I needed to put \\ instead of \
Try this:
$myfoldername = getcwd()."\\MyBackups";//your folders name 
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

hi thanks. it works fine now.. but still no folder is created. The file is still backup at the original folder.

Stefano Mtangoo 455 Senior Poster · Answer 9 · 2009-12-03T12:45:24+00:00

What about this?

$myfoldername = getcwd()."\\MyBackups";//your folders name 
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

futhonguy 0 Junior Poster in Training · Answer 10 · 2009-12-03T13:20:38+00:00

What about this?

$myfoldername = getcwd()."\\MyBackups";//your folders name 
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

Hi evstevemd,

mkdir actually create a new folder but this does not actually backup the database into the new folder. The backup file still backup in the main folder.

cheers

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 11 · 2009-12-03T15:01:27+00:00

You have to mention the path where you want the .sql file to be created.
This will create a folder called backup [if it doesn't exist and then create the .sql file in that folder].

<?php
backup_tables('localhost','root','','test2');


/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
	
	$link = mysql_connect($host,$user,$pass);
	mysql_select_db($name,$link);
	
	//get all of the tables
	if($tables == '*') {
		$tables = array();
		$result = mysql_query('SHOW TABLES');
		while($row = mysql_fetch_row($result)) {
			$tables[] = $row[0];
		}
	} else {
		$tables = is_array($tables) ? $tables : explode(',',$tables);
	}	
	//cycle through
	foreach($tables as $table) {
		$result = mysql_query('SELECT * FROM '.$table);
		$num_fields = mysql_num_fields($result);		
		//$return.= 'DROP TABLE '.$table.';';
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
		$return.= "\n\n".$row2[1].";\n\n";
		
		for ($i = 0; $i < $num_fields; $i++) {
			while($row = mysql_fetch_row($result)) {
				$return.= 'INSERT INTO '.$table.' VALUES(';
				for($j=0; $j<$num_fields; $j++) {
					$row[$j] = addslashes($row[$j]);
					$row[$j] = str_replace("\n","\\n",$row[$j]);
					if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
					if ($j<($num_fields-1)) { $return.= ','; }
				}
				$return.= ");\n";
			}
		}
		$return.="\n\n\n";
	}
	
	//save file
	if(!is_dir("backup")) {
		mkdir("backup",0777);
	}
	$handle = fopen('./backup/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);
}
?>

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 12 · 2009-12-03T15:04:43+00:00

@evstevemd,
You missed "/" in myfoldername variable. So it will create the sql file as,

MyBackupsdb-backup-1259831393-....... .sql

@futhonguy,
You can use evstevemd's code.

$myfoldername = getcwd()."\\MyBackups/";//your folders name 
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');

Notice the "/" in $myfoldername.

rajabhaskar525 1 Junior Poster · Answer 13 · 2009-12-03T16:27:25+00:00

Hi i have created a website for my client using PHP and mysql . The client need to backup a table in database as excel file every day. Well he had no idea about coding and all ... He need to backup using a simple way. Please anyone let me know how can i do it using php script or any easy software for that.....
Please help me
Rajeesh

hi all, i am backup onley one table data in excel. but must create another table in database
downloadtables.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form name="formx" action="" method="post" onsubmit="">
<table>
<tr>
<td>
<a href="csvexcel.php?table=ok" >download</a>
</td>
</tr>
</table>
</form>
</body>
</html>

csvexcel.php

<? include("includes/database.php"); 
if($_REQUEST['table']=='ok')
{
$table="flower_excel_product";
mysql_query("TRUNCATE TABLE `flower_excel_product`");

$sqlcat=mysql_query("select * from  product");
$numrows2=mysql_num_rows($sqlcat);

while($fetchcat=mysql_fetch_array($sqlcat))
{
mysql_query("INSERT INTO `flower_excel_product` ( `auto_id` , `productname` , `productid` , `productprice`,productquantity,  	status,productimage,departname ) 
VALUES ('$fetchcat[auto_no]', '$fetchcat[productname]', '$fetchcat[productid]', '$fetchcat[productprice]','$fetchcat[productquantity]','$fetchcat[status]','$fetchcat[productimage]','$fetchcat[departname]')");
}

}
$select = "SELECT * FROM $table";



$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );
$numrows=mysql_num_rows($export);
//echo $numrows;exit;
$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $temp = ucwords(str_replace("_"," ",mysql_field_name( $export , $i )));
	if($temp == "User Username"){
		$header .= "User EmailID" . "\t";
	}else{
		$header .= $temp ."\t";
	}
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
			$value = str_replace( '|' , ', ' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}
header("Content-type: application/octet-stream");
$name=str_replace("flower_","","$table");
header("Content-Disposition: attachment; filename=$name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
?>

i think it's helps you.

Stefano Mtangoo 455 Senior Poster · Answer 14 · 2009-12-03T16:41:24+00:00

@evstevemd,
You missed "/" in myfoldername variable. So it will create the sql file as,
@futhonguy,
You can use evstevemd's code.
$myfoldername = getcwd()."\\MyBackups/";//your folders name 
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
Notice the "/" in $myfoldername.

Thanks Naveen
It is very subtle error ;)

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 15 · 2009-12-03T16:53:58+00:00

nav33n 472 Purple hazed!

14 Years Ago

Indeed :P

futhonguy 0 Junior Poster in Training · Answer 16 · 2009-12-04T08:25:19+00:00

@evstevemd,
You missed "/" in myfoldername variable. So it will create the sql file as,
@futhonguy,
You can use evstevemd's code.
$myfoldername = getcwd()."\\MyBackups/";//your folders name 
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
Notice the "/" in $myfoldername.

Thanks evstevemd and nav33n.. all worked well now..
Appreciate both of your help. :)

arslanqamar 13 Unverified User · Answer 17 · 2014-02-13T12:25:17+00:00

I have modified the script. if some one want to create backup on limited tables. just replace the code and add own table names instead  table_1,table_2 etc.

<?php
backup_tables('localhost','root','root','testdb');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == '*') {
$tables = array();
$result = mysql_query('SHOW TABLES');
$row = mysql_fetch_row($result);
$tables[] ='talble_1';
$tables[] ='talble_2';
$tables[] ='talble_3';
$tables[] ='talble_4';
$tables[] ='talble_5';
} else {
$tables = is_array($tables) ? $tables : explode(',',$tables);
}   
    //cycle through
    foreach($tables as $table) {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);    
    //$return.= 'DROP TABLE '.$table.';';
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= "\n\n".$row2[1].";\n\n";
    for ($i = 0; $i < $num_fields; $i++) {
    while($row = mysql_fetch_row($result)) {
    $return.= 'INSERT INTO '.$table.' VALUES(';
    for($j=0; $j<$num_fields; $j++) {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = str_replace("\n","\\n",$row[$j]);
    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
    if ($j<($num_fields-1)) { $return.= ','; }
    }
    $return.= ");\n";
    }
    }
    $return.="\n\n\n";
    }
    //save file
    if(!is_dir("backup")) {
    mkdir("backup",0777);
    }
    $handle = fopen('backup/db-backup-'.date('d-m-Y-H-i-s-a', time()).'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    }
    ?>

PHP mysql database backup

Recommended Answers Collapse Answers

All 22 Replies

Recommended Answers