PHP mysql database backup

[rajeesh_rsn]

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

[nav33n]

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]

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]

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

[cwarn23]

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.

[futhonguy]

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]

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]

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.

[evstevemd]

try something like this

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

[evstevemd]

try something like this

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

[futhonguy]

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 ??

[evstevemd]

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]

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.

[evstevemd]

What about this?

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

[futhonguy]

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]

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]

@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]

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.

[evstevemd]

@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]

Indeed :P