PHP script creates an empty SQL file

Question

So this is new for me. When using this MySQL database PHP script by David Walsh I get an empty SQL file when running it.

<?php
backup_tables('localhost','username','password','blog');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
    $return = '';
    $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] = 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('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
    mysql_close($link);
}
?>

As I understood the code, I entered the DB name where blog is in the function backup_tables() at the top.

Why does the script create an empty SQL file?

Answer 1

I tried this script and it worked for me, albeit with some errors and some depreciation warnings.

In order to save the file, make sure the directory in which your script resides has write permissions.

chmod 0777 /home/site/dir

To prevent the Undefined variable: return notice, add the following code

$return = '';

right before this line

$link = mysql_connect($host,$user,$pass);

And - I know this seems obvious - but make sure you're connecting to the correct database, or that you don't have a typo on the database name.

backup_tables('localhost','myUsername','myPassword','myDatabase');

And finally: make sure the database has tables. I ran it with no tables in the database to see what happened - and it spit out a blank document. Just like you described. :)

Answer 2

There is a much better way to do this, which is with mysqldump. It is built to create backups of tables and it is much faster at it than php is. You just need to run the following command in your terminal:

mysqldump -h localhost -u root -p --result-file ~/backup.sql my_database

If you want to backup just a few tables, you can do this:

mysqldump -h localhost -u root -p --result-file ~/backup.sql my_database table_a table_b

There are many more options that you can use, so I won't list them but you can read about them here:

https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

If you need php to do this than you can just execute it through exec() or some other similar method:

exec("mysqldump -h $host -u $user -p$pass --result-file ~/$file $database $tables");