Help with database backup

[lukeurtnowski]

I'm trying to use php to packup a mysql database, here's the script

<?php
$db_host = "localhost";
$db_name = "medivas";
$db_user = "root";
$db_pass = "medivas1";

mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name) or die("Unable to select database.");
?> 

<?php
function datadump ($table) {

$result .= "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";

$query = mysql_query("select * from $table");
$num_fields = @mysql_num_fields($query);
$numrow = mysql_num_rows($query);

for ($i =0; $i<$numrow; $i++) {
  $result .= "INSERT INTO ".$table." VALUES(";
    for($j=0; $j<$num_fields; $j++) {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
    if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .= "\"\"";
    if ($j<($num_fields-1)) $result .= ",";
   }    

      $result .= ");\n";
     }
     return $result . "\n\n\n";
  }
?>

<?php
$table1 = datadump ("company");
$table2 = datadump ("activity");
$content = $table1 . $table2;

$file_name = "MySQL_Database_Backup.sql";
Header("Content-type: application/octet-stream"); 
Header("Content-Disposition: attachment; filename=$file_name");
echo $content; 
exit;
?>

But whenever I call the page, this is what I get;

Dump of company

Dump DATE : 19-Aug-2005

INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");
INSERT INTO company VALUES("","","","","","","","","","","","","","","","","");

Dump of activity

Dump DATE : 19-Aug-2005

INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");
INSERT INTO activity VALUES("","","","","");

Thats the correct number of records for each table, but the info seems to be blank? Why is this?
Thanks

[sneakyimp]

EDIT: my bad...i didn't read very closely

the problem is that you are never fetching any rows from your query result. $row is not defined before you try and access its constituent values. you might want to change your outer for loop to this:

while($row = mysql_fetch_row($query)) {
...

[lukeurtnowski]

Sorry (I want to make sure I understand correctly)
Is the outer (for) loop. this?
for ($i =0; $i<$numrow; $i++)
or this?
for($j=0; $j<$num_fields; $j++)

[sneakyimp]

like this:

EDIT: i edited the code...being very sloppy today. should have drunk more this weekend.

function datadump ($table) { 

$result .= "# Dump of $table \n"; 
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n"; 

$query = mysql_query("select * from $table"); 
$num_fields = @mysql_num_fields($query); 
$numrow = mysql_num_rows($query); 

while ($row = mysql_fetch_row($query)) { 
	$result .= "INSERT INTO ".$table." VALUES("; 
	for($j=0; $j<$num_fields; $j++) { 
		$row[$j] = addslashes($row[$j]); 
		$row[$j] = ereg_replace("\n","\\n",$row[$j]); 
		if (isset($row[$j])) $result .= "\"$row[$j]\"" ; else $result .= "\"\""; 
		if ($j<($num_fields-1)) $result .= ","; 

	} 
	$result .= ");\n"; 
}
return $result . "\n\n\n"; 
} // datadump()

i haven't tested it, but you get the idea.

[lukeurtnowski]

i see, is it ok if I were to put
$file_name = "Database_backup_".date("F_j_Y").".txt";
in place of
$file_name = "MySQL_Database_Backup.sql";
(does a sql extension ending matter, does txt work just as well?)
Also, I was wondering if there was a way that only 1 person could execute this script for security)

[sneakyimp]

changing the filename is probably fine. As far as i know, all that will do is change the suggested filename when you download the file.

yes, you can make it only executable by one person. you could put code like this at the very top of the page

if ($user_is_not_logged_in) {
  die('you must be authorized to execute this function');
}

Obviously, you would have to write some code to determine if $user_is_not_logged_in is true or false.

[lukeurtnowski]

ok, I was thinking thiis, if I put all this backup stuff in a backup folder (then can that folder be password protected) Do know of anywhere I can refer to in order to make the folder passsword protected?
Thanks

[sneakyimp]

if your server uses apache and is configured to support it, you can password protect a folder with an .htaccess file. talk to your system admin about doing that. i don't have much experience there.

another possibility is that you could write PHP to handle sessions, login, etc. try googling for tutorials on php and sessions and authorization.

[lukeurtnowski]

great, thanks
youdaman