Backup mysql Database take a specific table

Question

Hello This is the php code I use to Backup a mysql DATABASE, It takes all the DATABASE and all the tables, I want to take a particular table in the database instead of all tables. May someOne please tell me How to modify the php code PLEASE in order to take a single table instead of all tables? THis is the code:

<?php

/**
* Updated: Mohammad M. AlBanna
* Website: MBanna.info
*/


//MySQL server and database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'novtech';
$tables = '*';

//Call the core function
backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables);

//Core function
function backup_tables($host, $user, $pass, $dbname, $tables = '*') {
    $link = mysqli_connect($host,$user,$pass, $dbname);

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
    }

    mysqli_query($link, "SET NAMES 'utf8'");

    //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);
    }

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

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

        //Over tables
        for ($i = 0; $i < $num_fields; $i++) 
        {   //Over rows
            while($row = mysqli_fetch_row($result))
            {   
                if($counter == 1){
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                } else{
                    $return.= '(';
                }

                //Over fields
                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.= ','; }
                }

                if($num_rows == $counter){
                    $return.= ");\n";
                } else{
                    $return.= "),\n";
                }
                ++$counter;
            }
        }
        $return.="\n\n\n";
    }

    //save file , db-backup
    //$fileName = 'novtechDB-'.time().'-'.(md5(implode(',',$tables))).'.sql';
    $fileName = 'novtechDB'.'.sql';
    $handle = fopen($fileName,'w+');
    fwrite($handle,$return);

   if(fclose($handle)){
        echo "Done, the file name is: ".$fileName;
        exit; 
    }
}

I put $tables= array('myTable') Before the loop of the tables array[foreach($tables as $table)] , i'ts working now but it's giving me a error: Notice: Undefined variable: return. Please can SomeOne help me figure out what's wrong? How to fix it?

Code should be in the question and not as an external resource. Google Drive files can be deleted at anytime. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 3, 2018 at 15:14
also see a problem in the code when you are going to use innodb and foreiyn keys.. Basically you should export the parent tables first meaning you need to query information_schema database tables to get this information. — Raymond Nijland
– Raymond Nijland, Commented Oct 3, 2018 at 15:17
Basically write a query that uses these views dev.mysql.com/doc/refman/8.0/en/tables-table.html and dev.mysql.com/doc/refman/8.0/en/… from information_schema dev.mysql.com/doc/refman/8.0/en/information-schema.html and order the result output that would but enough i geuss to the get the parent tables before the child tables with innodb engine. — Raymond Nijland
– Raymond Nijland, Commented Oct 3, 2018 at 15:22

user3720435 · Accepted Answer · 2018-10-03 16:20:46Z

1

Before the loop of the tables array, set the array to the table you want to grab.

$tables = ['tale1','table2','table3'];
foreach($tables as $table)

Remove the code block that is commented //get all of the tables

answered Oct 3, 2018 at 16:20

user3720435

1,4862 gold badges20 silver badges29 bronze badges

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

2 Comments

DoctorDroid Over a year ago

thank you for your comment it giving me: ** Syntax error: unexpected '[' **

DoctorDroid Over a year ago

Instead of $tables = ['table1','table2','table3']; I put $tables = array('myTable'); before foreach($tables as $table) Its working but it's giving me a error message: Undefined variable: return

DoctorDroid · Accepted Answer · 2018-10-03 18:58:49Z

0

Bravooooooooooooooo!!! I fixed it thank you #user3720435
Step 1: I Removed the code block that is commented //get all of the tables
Step 2:
I put $tables= array('myTable') Before the loop of the tables array[foreach($tables as $table)] , i'ts working now but it was giving me an error. Notice: Undefined variable: return.
Step 3: i fix it by putting return=null; before code block that is commented //Over tables

answered Oct 3, 2018 at 18:58

DoctorDroid

2433 silver badges12 bronze badges

1 Comment

DoctorDroid Over a year ago

I did that SIR,

Collectives™ on Stack Overflow

Backup mysql Database take a specific table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related