Database Backup not giving data

[acetutor]

Through courtesy of http://www.developertutorials.com/tutorials/php/backup-mysql-database-php-050409-1300/ I have been attempting to create a backup of a clients site.

 

Here is my script

 

<?php

$db_host = "localhost";
$db_name = "client_data";
$db_user = "root";
$db_pass = "password";

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

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";
  }
        
$pages = datadump ("pages");
$navigation = datadump ("navigation");
$users = datadump ("users");

$content = $pages . $navigation . $users;

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

 

All runs well until I view the Downloaded file - all the tables and rows have been gathered but no cell information has been recorded. Here is the result.

 

# Dump of pages 
# Dump DATE : 29-Nov-2010

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

# Dump of navigation 
# Dump DATE : 29-Nov-2010

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

# Dump of users 
# Dump DATE : 29-Nov-2010

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

 

 

As I interpret the results all connections etc. are correct for indeed the tables called do have these numbers of records - just no cell details in backup!

 

Can anyone help identify what is amiss? I am a newbie here and would appreciate what may be obvious being pointed out to me.

 

My system is Windows 7 | Apache 2 | PHP Version 5.2.11| MySQL 5.0.51a

 

Thanks heaps

 

AceTutor

[ManiacDan]

1)  You never put any data into $row.

 

2)  mysqldump will do all this for you.

 

-Dan

[acetutor]

Thank you for the prompt reply.

 

Yes I had worked out that no data was being put into $row. What I don't have is the php skill to see what to modify in my script to solve that. How do I extract that from the query? I am using someone elses script as stated. Can you advise further with an example?

 

Cheers

 

 

[ManiacDan]

Per my #2, the answer is "don't do this at all."  Mysqldump is a standalone program that comes with mysql and performs this exact operation for you.

 

If you really MUST have a function that does this, this one is untested but should work for you:

function datadump ($table) {

  $result .= "# Dump of $table \n";
  $result .= "# Dump DATE : " . date("Y-m-d H:i:s") ."\n\n";
  $result .= "INSERT INTO `{$table}` VALUES ";
  
  $rs = mysql_query("SELECT * FROM `{$table}`");
  
  while ( $row = mysql_fetch_array( $rs ) ) {
    $values = array();
    foreach ( $row as $value ) {
      if ( preg_match("/-?\d*(\.\d+)?/", $value) ) {
        $values[] = $value;
      } else {
        $values[] = "'" . mysql_real_escape_string(str_replace("\n", "\\n", $value)) . "'";
      }
    }
    $result .= '(' . implode(',', $values) . '),';
  }
  
  return trim($result, ',');
}

Note that I didn't muck around with mysql_num_rows, mysql_num_fields, or addslashes.  None of those were necessary.

 

-Dan

[acetutor]

Thank you so much for your patience and for the sample text - muchly appreciated.

 

You should have a different alias - not ManiacDan but rather MaestroDan! You are the best.

 

Cheers

 

 

AceTutor