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?

@maytham-ɯɐɥıλɐɯ: I expect to run a nightly backup of the entire DB with scheduled URL runs. — lol5433
– lol5433, Commented Oct 19, 2015 at 19:57
so where is your database located, is it locally or hosted and where do you want it to backup it (distanation) local or some where else — Maytham Fahmi
– Maytham Fahmi, Commented Oct 19, 2015 at 19:59
Your example code is 7 years old. You should exercise caution in trying code examples that old. A major problem with it is the mysql_ functions he uses are deprecated and soon to be removed — Machavity
– Machavity ♦, Commented Oct 27, 2015 at 18:36

timgavin · Accepted Answer · 2015-10-24 20:57:51Z

5

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. :)

answered Oct 24, 2015 at 20:57

timgavin

5,2065 gold badges39 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

davejal Over a year ago

@Cyb3 Not sure if you tried this or not, what errors are you getting now or what is the problem with using this?

Get Off My Lawn · Accepted Answer · 2015-10-28 19:32:07Z

4

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");

answered Oct 28, 2015 at 19:32

Get Off My Lawn

36.6k47 gold badges201 silver badges381 bronze badges

2 Comments

lol5433 Over a year ago

Yes, thank you I know this already. But I'm restricted to only use PHP for this... I would rather use a cronjob with a shell script for this.

Alfabravo Over a year ago

@Cyb3 If you're not getting external input and you're restricted to PHP, run the command from PHP.

R. Vasquezq · Accepted Answer · 2015-10-19 19:25:21Z

2

I don't see $return defined anywhere before the .= 'DROP line. I've had errors involving undefined variables like that. Above the foreach() add $return = '';

answered Oct 19, 2015 at 19:25

R. Vasquezq

744 bronze badges

3 Comments

lol5433 Over a year ago

Thanks for an answer, but it didn't do the trick I'm afraid. The same result.

R. Vasquezq Over a year ago

How can I participate and build a reputation if I can't comment on posts?

Machavity Over a year ago

There are reasons for the 50 rep comment cap

davejal · Accepted Answer · 2015-10-26 22:44:46Z

2

To add to timgavin's answer you could also add compression to this script, just simply add

$compression = true;

to the top

and change the bottom from

$handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
fwrite($handle,$return);
fclose($handle);
mysql_close($link);

to

if ($compression) {
    $zp = gzopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql.gz', "w9");
    gzwrite($zp, $return);
    gzclose($zp);
} else {
    $handle = fopen($BACKUP_PATH . 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Another option is explained by mohd_anas on his blog and also on github

answered Oct 26, 2015 at 22:44

davejal

6,18110 gold badges45 silver badges85 bronze badges

Comments

Maytham Fahmi · Accepted Answer · 2015-10-31 18:44:33Z

1

+50

I have been following on your question from beginning, I did my research and found your question any way interesting.

Most of the solutions for backuping mysql via php code are out of date and have problems with nowadays environment. Therefore I was curious to find if there was any updated and useful once. My plan was if I could not find one then I wanted to develop one.

But I found a solution and it works. I have test it by exporting and re-importing tables. And I think you will like it.

Before going to the code, I have following following remarks:

With respect to David Walsh the code and tutorial you are following is from 2008 using MySQL which is deprecated now. The code below uses perhaps MySQLi.
When you run the code, it will deliver you a downloadable backup file. You can pass the file to a safe remote destination like server, cloud storage (dropbox, etc).
To run the backup function periodically, and depending on your OS, environemnt, strategy and how you want to take that approach. In most cases CRON is the solution to schedule it to run every evening once or some thing like that, there is a punch for tutorial and helpful information regarding that online.
Be a ware of this function will only make backup on localhost, as most host provider has blocked for remote mysql access (so this function can not take backup of remote mysql tables).
In the beginning and for testing it is fine to use root username, but when that works, then I suggest you create a username and password specific access to the table you want take backup from.

Note: the code is found on this page with respect to the code owner, I added extra line of code just to make it work for the OP.

Very important note: Depending on your server resources & capacity and your database size, executing such code might take time, specially and typically if you have a shared hosting, therefore you need to extend your execution time up to 300 seconds (5 minutes) and the backup should be taken in low load hours. And worst case you might need to ask your host provider to extend execution time if that part is prevented by code.

The working code

<?php

define("MAX_EXECUTION_TIME", 100); // seconds

$timeline = time() + MAX_EXECUTION_TIME;

EXPORT_TABLES('localhost', 'root', '', 'blog');

function EXPORT_TABLES($host, $user, $pass, $name, $tables = false, $backup_name = false)
{
    $mysqli = new mysqli($host, $user, $pass, $name);
    $mysqli->select_db($name);
    $mysqli->query("SET NAMES 'utf8'");
    $queryTables = $mysqli->query('SHOW TABLES');
    while ($row = $queryTables->fetch_row())
    {
        $target_tables[] = $row[0];
    }
    if ($tables !== false)
    {
        $target_tables = array_intersect($target_tables, $tables);
    }
    try
    {
        foreach ($target_tables as $table)
        {
            $result = $mysqli->query('SELECT * FROM ' . $table);
            $fields_amount = $result->field_count;
            $rows_num = $mysqli->affected_rows;
            $res = $mysqli->query('SHOW CREATE TABLE ' . $table);
            $TableMLine = $res->fetch_row();
            $content = (!isset($content) ? '' : $content) . "\n\n" . $TableMLine[1] . ";\n\n";
            for ($i = 0, $st_counter = 0; $i < $fields_amount; $i ++, $st_counter = 0)
            {
                while ($row = $result->fetch_row())
                { //when started (and every after 100 command cycle):
                    if ($st_counter % 100 == 0 || $st_counter == 0)
                    {
                        $content .= "\nINSERT INTO " . $table . " VALUES";
                    }
                    $content .= "\n(";
                    for ($j = 0; $j < $fields_amount; $j ++)
                    {
                        $row[$j] = str_replace("\n", "\\n", addslashes($row[$j]));
                        if (isset($row[$j]))
                        {
                            $content .= '"' . $row[$j] . '"';
                        } else
                        {
                            $content .= '""';
                        }
                        if ($j < ($fields_amount - 1))
                        {
                            $content .= ',';
                        }
                    }
                    $content .= ")";
                    //every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
                    if ((($st_counter + 1) % 100 == 0 && $st_counter != 0) || $st_counter + 1 == $rows_num)
                    {
                        $content .= ";";
                    } else
                    {
                        $content .= ",";
                    }
                    $st_counter = $st_counter + 1;
                }
            }
            $content .= "\n\n\n";
        }
    } catch (Exception $e)
    {
        echo 'Caught exception: ', $e->getMessage(), "\n";
    }
    $backup_name = $backup_name ? $backup_name : $name . "___(" . date('H-i-s') . "_" . date('d-m-Y') . ")__rand" . rand(1, 11111111) . ".sql";
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary");
    header("Content-disposition: attachment; filename=\"" . $backup_name . "\"");
    echo $content;
    exit;
}

?>

edited Oct 31, 2015 at 18:44

answered Oct 31, 2015 at 13:38

Maytham Fahmi

33.2k17 gold badges133 silver badges159 bronze badges

7 Comments

lol5433 Over a year ago

Thank you for the contribution. I tested it with my DB and it gave me a 500 Server Error after some minutes. Is it because of the size? The Database has 680 tables FYI.

Maytham Fahmi Over a year ago

You are welcome, Ok i suggest you start with small database just to see if it works? then after that we can take some conslusiotns.

lol5433 Over a year ago

It worked perfectly with my local database with 11 tables.

Maytham Fahmi Over a year ago

the only thing I could think of is that, the script which is normal thing that is overloads your server (host server) and if it is a shared host then you have limited resources and that might put your server responding down for short while, I will if it can be fixed with out putting overloading server and getback to you

Maytham Fahmi Over a year ago

And you might end up with contacting your provider asking them for support, but let me get back to you

|

fatihn · Accepted Answer · 2015-10-26 20:15:03Z

1

if you run your script with a table name, it works correctly. You should add $return = ''; to line 26 ofcourse. backup_tables('localhost','root','arcd','unilicadb', 'fa_university');

If you run backup_tables('localhost','root','arcd','unilicadb', '*'); with wildcard you can get a memory error in case you have a large db.

"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 34399640 bytes)"

Try adding error_reporting(E_ALL);

answered Oct 26, 2015 at 20:15

fatihn

1471 silver badge8 bronze badges

Comments

Mitul · Accepted Answer · 2015-10-30 09:13:35Z

backup_tables('localhost','root','','dbname');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
    $return = '';
    $link = mysqli_connect($host,$user,$pass, $name);
    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysqli_query($link, 'SHOW TABLES');
        while($row = mysqli_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    //cycle through
    foreach($tables as $table)
    {
        $result = mysqli_query($link, 'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);

        $return.= 'DROP TABLE '.$table.';';
        $tableResult = mysqli_query($link, 'SHOW CREATE TABLE '.$table);
        $row2 = mysqli_fetch_row($tableResult);
        $return.= "\n\n".$row2[1].";\n\n";

        for ($i = 0; $i < $num_fields; $i++) 
        {
            while($row = mysqli_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_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);
    mysqli_close($link);
}

Collectives™ on Stack Overflow

PHP script creates an empty SQL file

7 Answers 7

1 Comment

2 Comments

3 Comments

Comments

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

2 Comments

3 Comments

Comments

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related