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
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).
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.
And also check for separate tables..
http://www.bigresource.com/PHP-Import-Excel-file-into-MySQL-database-3GCFOrrb.html#r2WzqLkg
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.
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
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.
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.
try something like this
$myfoldername = "backup";//your folders name
$handle = fopen(getcwd()./.$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');try something like this
$myfoldername = "backup";//your folders name
$handle = fopen(getcwd()./.$myfoldername.'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');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 ??
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+');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.
What about this?
$myfoldername = getcwd()."\\MyBackups";//your folders name
mkdir($myfoldername , 0777);
$handle = fopen("$myfoldername".'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');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
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);
}
?>@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.
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,
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 ;)
Indeed :P
@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. :)
Glad I was of help!
Tell your friends about Daniweb.
Don't forget to mention Naveen ;)
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);
}
?>
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.