I have here a piece of code which works quite well. What it does is backup your tables by creating INSERT statements and writing them to a file. In fact, I do not mind people knicking this piece of code if they wish. The only problem I have is when you have a form to enter text into a textarea and that the text contains carriage returns. When you write it to a file the carriage returns show up in the file mid command which messes everything up. As you can see from the code below I have made some attempts to solve this but with no sucess. They still appear in the file.
Just so that you know. The solution is generic and can be used for any database.
Bascially
- it does a 'show tables' to get all tablenames and loads them into an array
- then it drives off this array to do:
(a) a desc table so that you get the field names
(b) then reads the table to get the field values.
Any help/suggestions gratefully received as it is so frsutrating to be so near and yet so far.
Thank you
antoine
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Back up</title>
</head>
<body class="admin">
<?
#build a meaningful name for the backup file
$l_filename=date("Y-m-d-h-i");
#open file which will contain data
$data_file = fopen("$DOCUMENT_ROOT/bk/$l_filename.bk","w",1);
#open file which will contain backup stats
$results_file = fopen("$DOCUMENT_ROOT/bk/$l_filename.rs","w",1);
if ((!$data_file) or (!$results_file))
{
echo "<p>File could not be opened</p> ";
exit;
}
load the list of tables into an array for processing
$query = "SHOW tables";
include("inc_run_query.inc");
$l_table_total = $num_results;
#load table list into an array
for ($i=0; $i <$l_table_total; $i++)
{
$row = mysql_fetch_array($result);
$tablenamearray[$i] = $row[0];
}
echo "<table border=1 > \n";
echo "<tr> \n";
echo "<td>Table Name</td> \n";
echo "<td>Rows Backed-up</td> \n";
echo "</tr> \n";
for ($i=0; $i <$l_table_total; $i++)
{
#initialise variables
$l_table_name = $tablenamearray[$i];
$l_field_list = "";
$l_number_of_fields = 0;
$query = "DESCRIBE ".$l_table_name;
include("inc_run_query.inc");
$l_number_of_fields = $num_results;
#get all the field names
for ($j=0; $j <$l_number_of_fields; $j++)
{
$row = mysql_fetch_array($result);
$l_field_name="field".$j+1;
$$l_field_name = $row[0];
if ($j == 0)
{$l_field_list = $$l_field_name;}
else
{ $l_field_list = $l_field_list.",".$$l_field_name;}
}
#add brackets around field lists
$l_field_list = "(".$l_field_list.")";
$l_pre_insert_statement = "INSERT INTO ".$l_table_name." ".$l_field_list."VALUES (";
#build the INSERT file that is to say read the actual data and create INSERT STATEMENTS
$query = "SELECT * FROM ".$l_table_name;
include("inc_run_query.inc");
for ($k=0; $k <$num_results; $k++)
{
$l_insert_statement = "";
$l_value_list = "";
$row = mysql_fetch_array($result);
for ($l=0; $l <$l_number_of_fields; $l++)
{
if ($l==0)
{ $l_value_list = "\"".$row[$l]."\"";
}
else
{ $new_addition = $row[$l];
#now tidy up
$new_addition = addslashes($new_addition); #escape all the quotes, double quotes etc..
$new_addition = nl2br($new_addition);
$new_addition = str_replace('<br>','~~',$new_addition);
$new_addition = str_replace(chr(13),'**',$new_addition);
$l_value_list = $l_value_list.",\"".$new_addition."\"";
}
}
$l_insert_statement = $l_pre_insert_statement.$l_value_list."); \n";
#write to file
fwrite($data_file,$l_insert_statement);
}
#put status message on screen
echo "<tr> \n";
echo "<td>".strtoupper($l_table_name)."</td>\n";
echo "<td>".$num_results."</td> \n";
echo "</tr> \n";
fwrite($results_file,"Table : ".$l_table_name."\n");
fwrite($results_file,"Rows Backed-up : ".$num_results."\n");
}
echo "</table>";
#close files
fclose($data_file);
fclose($results_file);
?>
</body>
</html>
