PHP script to make a backup copy of a MySQL table

Yesterday I looked at how to copy a table with MySQL using some SQL queries. This post looks at how to automate the process with a PHP script saving you having to type in and adjust queries each time you want to back up a table.

The script below connects to a MySQL database server on "localhost" using the login name "test" and password "123456" and then connects to the database "test". It then copies the table structure and data from the table "products" to a new table "products_bak". If the target table already exists the function returns false.

The table_exists() function comes from an earlier post on this blog titled PHP function to check if a MySQL table exists and published in June 2008. Read that post for more information about that particular function.

mysql_connect('localhost', 'test', '123456');
mysql_select_db('test');

if(copy_table('products', 'products_bak')) {
    echo "successn";
}
else {
    echo "failuren";
}

function copy_table($from, $to) {

    if(table_exists($to)) {
        $success = false;
    }
    else {
        mysql_query("CREATE TABLE $to LIKE $from");
        mysql_query("INSERT INTO $to SELECT * FROM $from");
        $success = true;
    }
   
    return $success;
   
}

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }
   
    $res = mysql_query("
        SELECT COUNT(*) AS count
        FROM information_schema.tables
        WHERE table_schema = '$database'
        AND table_name = '$tablename'
    ");
   
    return mysql_result($res, 0) == 1;
}

Note that there is no error checking after the calls to mysql_query so you’d want to add this in yourself and/or change the mysql_* calls to use whatever database abstraction library you use in your own projects.

Note also that the copy_table() function could also call table_exists() on the $from table as well to make sure that exists too, in order to prevent errors in the query calls.

To copy multiple tables you can either call the copy_table function multiple times, or put the to and from tablenames into an associative array and loop through them like so:

foreach($tables as $from => $to) {

    echo "copying $from to $to: ";
   
    if(copy_table($from, $to)) {
        echo "successn";
    }
    else {
        echo "failuren";
    }

}

In Thursday’s PHP post I’ll look at how to enhance this script to run it from the command line passing the from and to table names from the command line, and will also add in error checking.