PHP Script to take a database backup

PHP
,
1

I was trying to learn how to make a Database backup through PHP scripting. I landed upon various articles, but this one was quite simple →

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
	
   $table_name = "employee";
   $backup_file  = "/tmp/employee.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";
   
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not take data backup: ' . mysql_error());
   }
   
   echo "Backup  data successfully\n";
   
   mysql_close($conn);
?>

I am looking for a few amendments.

#1 The tables may vary in real-life situations. So I first want to Populate all tables and store it in a variable - I believe array will do the job and then take a full backup of MySQL.

Please guide me on how can I store all table details in an array first. Thanks.

P.S. End goal will be to try this on Wordpress DB.

2

Its in general a bad idea to use a PHP script to make backup from a database. At one point when the table reach a specific size, it will fail.

One thing you need to add at the top, is the command to make the script run until it is complete (or depending on the php settings, when the internal max time for these script run out).

set_time_limit(0);

For getting the tables of a database, you can use the INFORMATION_SCHEMA:

SELECT 
	TABLE_NAME 
FROM 
	INFORMATION_SCHEMA.TABLES 
WHERE 
	TABLE_TYPE='BASE TABLE' 
    AND TABLE_SCHEMA='[database name]'
1 Like
3

I was following a tutorial to accomplish this but through understanding each step and learning →

This is the foundation code →


$dsn = 'mysql:host=localhost;dbname=swisskni_testpod';
$db = new PDO($dsn,'swisskni_testpod','somepassword');
$get_all_table = "SHOW TABLES";
$statement = $db->prepare($get_all_table);
$statement->execute();
$result = $statement->fetchAll();
echo ("<pre>");
print_r($result);
echo ("</pre>");

But here

if(isset($_POST['table']))

Is the table a keyword predefined in MYSQL?

Or may be we are setting it here→

 <label>
  <input type="checkbox" class="checkbox_table" name="table[]"  value="<?php echo $table["Tables_in_swisskni_testpod"]; ?>" /> <?php echo  $table["Tables_in_swisskni_testpod"]; ?>
 </label>
4

$_POST tells you you’re looking for form data from the user.

Tells you your form data may send a field called ‘table’.
Based on the construction, it may send multiple values of table, and so $_POST[‘table’], if it is set, is an array.

1 Like
5

Sor array is not by accident, but since we are setting an array:

If this would have been:

name="table"
Then there wouldn’t have been an array?

6

Correct, it would be a string.

1 Like
7

I have one more confusion:

if(isset($_POST['table'])){
  $output = '';
  foreach($_POST["table"] as $table) {
    echo "$table <br>";
  }
}

since the array is generated based on user input of checkbox how can i generate it as echo?

8

The code you have there will echo out each value that has been sent.

1 Like
9

I tried this:
echo '$_POST["table"]';

but it deosnt show an array?

if(isset($_POST['table'])){
  $output = '';
  foreach($_POST["table"] as $table) {
    echo "$table <br>";
    echo '$_POST["table"]';
  }
}
10

Take the quotes off of the outside of it, and you’ll see the word “Array” echoed as many times as you have entries checked.

1 Like
11

Yes, so it is used just as a counter here?

12

echoing an array has no meaning.

“Tell me the name of that group of racehorses”
“The group doesnt have a name, the horses each have a name.”

That’s why your code foreach’s the array - to access each element in turn, and echo out that element.

1 Like
13

sir,

This is delivering some meaning:

echo ("<pre>");
print_r($_POST['table']);
echo ("</pre>");
14

print_r is not echo.

print_r is a different function, which is designed to take and output an array in a predefined format.

1 Like
15

Yes,

so the values are getting saved as array:


Array
(
    [0] => cars
    [1] => makes
    [2] => names
    [3] => savings
)
16

Fine. Yes. They are in an array.

print_r, for the record, could also be written as:

function print_r($array) {
  echo "Array\n(";
  foreach($array as $key => $value) {
    echo "\n\t[".$key."] => ".$value;
  }
  echo ")";
}

:stuck_out_tongue:

1 Like
17

I think that will throw error.

18

Well it would throw an error because you’d be trying to redefine a predefined function. But my point is that that is what print_r does, even though you cant see it. That is ‘the code’ for print_r. (It will have more than that, obviously, because it does things like check type etc. But at its root, that’s what it does.)

1 Like
19

Hi there,

Array
(
    [0] => Array
        (
            [Tables_in_swisskni_testpod] => cars
            [0] => cars
        )

    [1] => Array
        (
            [Tables_in_swisskni_testpod] => makes
            [0] => makes
        )

    [2] => Array
        (
            [Tables_in_swisskni_testpod] => names
            [0] => names
        )

    [3] => Array
        (
            [Tables_in_swisskni_testpod] => savings
            [0] => savings
        )

)

image
image631×568 16.9 KB

I am unable to understand this array. The live code is here:
http://html.trafficopedia.com/all/dbbackup/

This code is bringing it live here:

$get_all_table = "SHOW TABLES";
$statement = $connect->prepare($get_all_table);
$statement->execute();
$result = $statement->fetchAll();
echo ("<pre>");
print_r($result);
echo ("</pre>");
20

You havent told fetchAll what kind of array you want back (numeric or associative), so it’s gone with its default - Both. The 0th column of your row is “table_in_swisskni_testpod”.

1 Like