table back-up either in CSV or SQL Format incomplete.

Question

I have that script that downloads all the contents of my table in sql format and save a local copy. My problem is the content of my table is so huge, the actual data is about 50MB and the data being generated of my script is just 3.5MB. What's wrong with my script? why all data are not written in sql file?

$table = "Downloads";

$result = $db->query('SELECT * FROM '.$table);
$num_fields = $db->num_fields($result);

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] = ereg_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
$handle = fopen(UPLOAD_XML_PATH_ABSOLUTE.'downloads_backup'.'.sql','w+');
if(fwrite($handle,$return)){
    fclose($handle);
    //$ret = true;
} else {
    //$ret = false;
}

Answer 1

Not sure if I understand you correctly, but one small note: Why do you store all these values in a variable in the first place and not just write them directly to the file? Doesn't make any sense to me, because you only get your available memory filled up.

Update

A couple more notes: Maybe mysql_real_escape_string() should be used and why do repeat all those INSERTS? One INSERT followed by many (),(); should be enough. How do you differentiate between integer, string, boolean and NULL in your code? This could lead to a problem if you try to use your sql file later.

Answer 2

Filesize might not be the best way to determine whether the script is working properly. Have you checked over the data the script produces?

1. Use the saved sql file to create a temp duplicate of the table you are trying to export.
2. Run your script on this temp table.
3. Use a utility such as WinMerge to see if the two files are identical or not.

It's not terribly elegant, but it is quick, doesn't require any costly tools, and if WinMerge does pickup any differences between the two, that should also indicate where the problem lies.