Programmatically creating a dump of MYSQL through PHP

PHP
1

I have created a PDO connection successfully, and now I am trying to create an MYSQL table dump in .sql format.

I am able to generate an array with all the entries in an associative Click Here format

The first step that I did is to download a database dump manually, and I am posting it in the next post so that this post remains readable.

There were a lot of parts in that database, first one is →

CREATE TABLE `cars` (
  `car_id` int(10) UNSIGNED NOT NULL,
  `make_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  `yearmade` year(4) NOT NULL,
  `mileage` mediumint(8) UNSIGNED NOT NULL,
  `transmission` enum('automatic','manual') NOT NULL DEFAULT 'automatic',
  `price` decimal(8,2) NOT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have to generate this part programmatically

Next will be pulling data in a format which when restored should be accountable for inserting the value, including the creation of the table.

INSERT INTO `cars` (`car_id`, `make_id`, `yearmade`, `mileage`, `transmission`, `price`, `description`) VALUES
(1, 5, 2007, 113688, 'automatic', 13550.00, 'Green Chrysler 300. Only one owner, very carefully maintained. Top of the line, and beautifully styled, this is an outstanding ride with great performance.'),
(2, 2, 2007, 126570, 'automatic', 7545.00, 'Red Ford Focus. \"Great bargain as a family car.\"'),
(3, 12, 2012, 517, 'automatic', 20755.00, 'Demo model that\'s hardly been out on the road, this red Chevrolet Cruze is just a dream, with just about every option you could ask for. Great fuel economy, too.'),
(4, 16, 2010, 116626, 'automatic', 10554.00, 'Red Camry in good running condition. Sound electrics and bodywork. Clean interior, appears never to have been smoked in.'),
(5, 7, 2011, 24694, 'automatic', 26951.00, 'Space grey BMW 3 series with beige leather interior. BMW Factory Certified with a 6 year/100,000 mile warranty from in-service date.'),
(6, 11, 2005, 95496, 'automatic', 8554.00, 'Black Jaguar S-Type in perfect working condition. Good electrics and bodywork. Low mileage. New tires recently fitted.'),
(7, 5, 2004, 75500, 'automatic', 6005.00, 'Black Sebring LX convertible. Very low mileage. Excellent ride. A must-see bargain.'),
(8, 4, 2001, 100145, 'automatic', 9550.00, 'Black SLK-Class convertible. Immaculate interior. Power top and power seats. Runs like new.'),
(9, 2, 1999, 102500, 'automatic', 4550.00, 'Metallic red Mustang convertible. Economy car, very easy on fuel. No negative history. No rust or damage on paintwork.'),
(10, 16, 2002, 173658, 'automatic', 6550.00, 'So much to like about this Silver Toyota 4Runner. Runs well, good paint, tires, nice sound system.'),
(11, 3, 2005, 122250, 'automatic', 11550.00, 'Black Cadillac SRX. Only one owner. Beautiful SUV.'),
(12, 17, 2002, 155500, 'automatic', 4305.00, 'Silver Passat. Only one owner. Leather interior. Rare bargain.'),
(13, 1, 1952, 46383, 'manual', 22055.00, 'Burgundy Studebaker Roadster with newly rebuilt engine and wide whitewall tires. Three-speed manual transmission. Runs and drives amazingly.'),
(14, 10, 2006, 124209, 'automatic', 9120.00, 'White Santa Fe. Only one owner. Leather interior and bodywork are in great shape.'),
(15, 10, 2012, 9811, 'automatic', 24554.00, 'Silver Genesis with beige leather and wood trim interior. Great handling and comfort. Low, low mileage. Luxury at an affordable price.'),
(16, 14, 2005, 130500, 'manual', 7505.00, 'Five-speed manual black Civic. Super clean, with 6 CD changer. This one, you must see!'),
(17, 15, 2007, 84947, 'automatic', 14554.00, 'Audi A4 Quattro. Gray with gray leather interior, and glass roof. Excellent value.'),
(18, 6, 1972, 77600, 'manual', 28055.00, 'Citroen D Super with 5-speed manual transmission in fantastic shape. Extremely well maintained, and has obviously been treasured by its owner. A real European classic.'),
(19, 8, 2012, 19361, 'automatic', 14553.00, 'Yellow Fiat 500 POP. Immaculate interior and bodywork. Electrics in perfect order. Tires only slightly worn.'),
(20, 12, 2002, 160550, 'automatic', 4545.00, 'Blue Impala LS with gray interior. Ideal economical vehicle with good gas mileage. Dependable engine, new tires. Price includes 6 month/8,500 mile warranty.'),
(21, 9, 2005, 94995, 'automatic', 8150.00, 'Gold Pontiac Bonneville with low mileage. Great condition.'),
(22, 5, 2006, 102300, 'automatic', 7105.00, 'Green Town & Country sports van.'),
(23, 8, 2012, 5238, 'automatic', 16050.00, 'Pearl white Fiat 500 sport hatchback. Power glass sunroof and power windows. Only one owner.'),
(24, 17, 2005, 70388, 'automatic', 14055.00, 'Shadow blue Touareg in excellent condition. Heated leather seats, sun roof, and navigation. Really low mileage. '),
(25, 13, 2012, 35000, 'automatic', 15550.00, 'Tan Altima. Low mileage. Excellent condition.'),
(26, 2, 2004, 108694, 'automatic', 10980.00, 'Top of the line black Expedition XLT 5.4 liter 4WD with every conceivable option. A truly exceptional SUV.'),
(27, 2, 2005, 123059, 'automatic', 8000.00, 'Blue Ford Escape. Excellent condition. A real bargain.'),
(28, 13, 2010, 32791, 'automatic', 20505.00, 'Red Pathfinder 4WD. Only one owner. Nicely equipped with just about every feature you could want, including third-row seats.'),
(29, 14, 2002, 124334, 'automatic', 6004.00, 'Silver Accord with sunroof, CD player, and all new tires. Excellent condition.'),
(30, 14, 2011, 27345, 'automatic', 14000.00, 'Dark gray Civic. Only one owner, very low mileage. Great fuel economy.'),
(31, 12, 2011, 34256, 'automatic', 12000.00, 'Dark gray Malibu. Interior and bodywork in good condition. Low mileage.'),
(32, 15, 2003, 93494, 'automatic', 10000.00, 'Silver Audi A6 with tan interior. Two previous owners. Mechanically sound and good bodywork. New tires.'),
(33, 3, 2005, 139534, 'automatic', 11505.00, 'Pearl white Cadillac SRX. Electrics, engine, and bodywork all in excellent condition. Only one owner. Tires have about 3/4 of their life span left.'),
(34, 7, 2011, 33784, 'automatic', 25904.00, 'White 3 Series 328i. Low mileage. Bodywork in mint condition. AM/FM stereo, a trip computer, power sunroof.'),
(35, 5, 2012, 7834, 'automatic', 16560.00, 'White Chrysler 200 with black interior. Exceptionally low mileage ');

I have done a course so I have some Idea how to backup in terms of headers, but .sql seems to be very tough. Headers will be used. Like this →

 header('Content-Type: text/plain');
  header('Content-Disposition: attachment;filename=cars.csv');
  header('Cache-Control: no-cache, no-store, must-revalidate');
  header('Pragma: no-cache');
  header('Expires: 0');
  $csvoutput = fopen('php://output', 'w');
  $row = getRow($result);
  $headers = array_keys($row);
  fputcsv($csvoutput, $headers);
  fputcsv($csvoutput, $row);
  while ($row = getRow($result)) {
    fputcsv($csvoutput, $row);
  }  
  fclose($csvoutput);  
  exit;

Ley say if i want to get this part first in .sql file are there any functions in PDO or we have to rely on core PHP?

CREATE TABLE `cars` (
  `car_id` int(10) UNSIGNED NOT NULL,
  `make_id` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  `yearmade` year(4) NOT NULL,
  `mileage` mediumint(8) UNSIGNED NOT NULL,
  `transmission` enum('automatic','manual') NOT NULL DEFAULT 'automatic',
  `price` decimal(8,2) NOT NULL,
  `description` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There doesnt seems to be any function similar to this →
fputcsv($csvoutput, $row); for .sql

3

Hi thank you for your advices and suggestions.

I use sublime text with SFTP plugin, which immediately upload my work on hostgatpr shraed hosting. The advantage I get working live is I can share links for discussions.

Can I used console method on shared hosting or that is possible only on localhost/wamp settings? I have never used console for PHP. Although based on your many advice I know it is fast, and more efficient for professional develeopers.

4

What is the real problem you are trying to solve? Are you just wanting to make backups the database? What is the high level overview of the problem?

5

Yes, Programatically. I was able to backup in .csv and excel format, but the course I did on linkedIn doesnt had .sql backup so I was trying to accomplish that.

6

No need to over complicate this. Just run this script from CRON. There are other options for adding a datetime to the filename. As is, this will just overwrite the last backup each time it is run

#!/bin/bash

mysqldump --user=DBUSER --password=DBPASSWORD --host=YOURHOST DBNAME > /backup/path/backups/mysql_backup.sql
1 Like
7

Hi there, I want to backup w/o accesing any tool in Cpanel or PHPMYADMIN.

8

Then this is what you want. Once you set it up it will automatically do backups at whatever interval you set CRON to run it. Set it and forget it.

1 Like
9

Can this be done through .php file say backup.php which is accesible to me? The solution is intended when PHPMYAADMIN and cpanel is never accesible, but code folder is as in case of wordpress plugin.

11

You can always just run the script at will from the command line.

12

If you absolutely need to do this in a PHP file, you can use the exec() function to run console code in PHP.

1 Like
13

Hi, Can you shower more light on this path →
/backup/path/backups/mysql_backup.sql

how to select this path?

14

The path is wherever you want to save your backups.

16

Compact is good if you understand the concepts.

It’s like maths. (Math for you Americans.)
Your teacher wouldn’t teach you the short method first, they taught you the long method. Then when you understood how and why it worked, they taught you the short way to do it.

So it’s a ternary on the end of an assignment. If you properly extract it into its normal form, it would be:

if(is_array($tables)) {
   $tables = $tables;
} else {
   $tables = explode(",",$tables);
}

So, given that short sentence description of the string-to-array conversion function ‘explode’, what is the purpose of the above code block?

What do you know for certain about $tables at the end of the code block, and what do you suspect (or at least, what is implied) about $tables before the code block?

17

So True.

18 
    $mysql = 'mysql:host=localhost;dbname=toolcula_apps';
    try {
      $pdo = new PDO($mysql, 'toolcula_apps', '123456');
    } catch (PDOException $e) {
      $error = $e->getMessage();
    }
    // file header stuff
    $output = "";
    $table = 'cars';    
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt   = $pdo->query("SHOW CREATE TABLE $table");
    $row    = $stmt->fetch();
    $output.= $row[1].";\n\n";
    if (isset($_POST['download'])){
      // Output to Browser
      header('Content-Description: File Transfer');
      header('Content-type: application/octet-stream');
      header('Content-Disposition: attachment; filename=' . rand() . '.sql');
      header('Content-Transfer-Encoding: binary');
      header('Content-Length: ' . strlen($output));
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Expires: 0');
      header('Pragma: public');
    } 
?>
<html>
<head>
<meta charset="utf-8">
<title>Used Cars</title>
<link href="styles/styles.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="wrapper">
  <form method="post">
    <fieldset>
      <legend>Download Results in SQL Format</legend>
      <p>
        <input type="submit" name="download" id="download" value="Download File">
      </p>
    </fieldset>
  </form>
</div>
</body>
</html>

Above is the code, and it is working fine till the point of execution of the download button function, but the download doesn’t have an element of data available from this array:

Array
(
    [Table] => cars
    [0] => cars
    [Create Table] => CREATE TABLE `cars` (
  `car_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `make_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `yearmade` year(4) NOT NULL,
  `mileage` mediumint(8) unsigned NOT NULL,
  `transmission` enum('automatic','manual') NOT NULL DEFAULT 'automatic',
  `price` decimal(8,2) NOT NULL,
  `description` text,
  PRIMARY KEY (`car_id`),
  KEY `yearmade` (`yearmade`),
  KEY `make_id` (`make_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=latin1
    [1] => CREATE TABLE `cars` (
  `car_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `make_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `yearmade` year(4) NOT NULL,
  `mileage` mediumint(8) unsigned NOT NULL,
  `transmission` enum('automatic','manual') NOT NULL DEFAULT 'automatic',
  `price` decimal(8,2) NOT NULL,
  `description` text,
  PRIMARY KEY (`car_id`),
  KEY `yearmade` (`yearmade`),
  KEY `make_id` (`make_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=latin1
)

But it is generating HTML as output, which is not even included in the output.

image
image1308×672 67 KB

19

You create a variable called $output, but you don’t output it anywhere. Then you don’t exit() before the html is shown again, hence it appears in your download.

1 Like
20

Yes, Thanks

image
image857×536 63.4 KB

I did the abive ↑, and now I got this in the final downlaoded file →

image
image1108×496 56.3 KB

Live Link → Part 1

22

I was able to successfully create the MYSQL Dump for one table. Initially, I set the $table = ‘cars’ manually when creating one table dump. I did it deliberately so that in the future when I try to mass-download dump of more than one table based on the checkbox selected.

Now,

    if (isset($_POST['download'])) {
      foreach ($_POST['table'] as $table) {
        //  The place where I placed the succesful code for one table
      } //End For Each        
    } //End Isset IF

Codel logic of one table dump extraction →

while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
  
 }

The above loop will run until all rows are exhausted.
Inside it there are two for each loop: One will print column for example:
INSERT INTO lnames (id, name, meaning, gender) VALUES → But this needs to be done once hence $insertfield = “”; is assigned so that only if it is empty thta line will be printed even when while loop runs again and again till all values rows are exhausted.

One more for each loop will generate values for all rows.

The Code for single table generation is here:

//Start
          // file header stuff
          // $table = 'cars';  
          $insertfield = "";
          $output = "-- PHP MySQL Dump\n--\n";
          $output .= "-- Host: $dbhost\n";
          $output .= "-- Generated: " . date("r", time()) . "\n";
          $output .= "-- PHP Version: " . phpversion() . "\n\n";
          $output .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n\n";
          $output .= "--\n-- Database: `$dbname`\n--\n";
            
          $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
          $stmt   = $pdo->query("SHOW CREATE TABLE $table");
          $row    = $stmt->fetch();
          $output.= $row[1].";\n\n";  

          // get table data
          $output .= "--\n-- Dumping data for table `$table`\n--\n\n";
          $stmt = $pdo->query("SELECT * FROM $table");
          $row = $stmt->fetch(PDO::FETCH_OBJ);
          // echo "<pre>". print_r($row) . "</pre>";

          while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
            // Runs Once Per table -- INSERT CLAUSE
            if ($insertfield == "") {
              $insertfield = "INSERT INTO `$table` (";
              //commasep initially set to no comma value as 1st entry doesnt needs to be comma seperated.
              $commasep = ""; 
              //Grabbing Each column/field Name
              foreach ($row as $key => $value) {
                $insertfield .= $commasep . "`$key`";
                $commasep = ", "; 
              } //End of For Each Loop
              $insertfield .= ") VALUES";
              $output .= $insertfield . "\n";
            }//End if

            //Grabbing Values for correspoding Column Names 
              $commasep = "";
              $output .= $commasep . "(";
              foreach($row as $key => $val){
                $output .= $commasep . "`$val`"; 
                $commasep = ",";           
              } //End For Each     
              // terminate row data
              $output .= "),";
              $output .= "\n";
            } //End While

Issue: Live Link
Irrespective of the number of tables selected in checkbox it downloads dump only for the last item checked(last from top to bottom the selection order).

Full Code →


    if (isset($_POST['download'])) {
      foreach ($_POST['table'] as $table) {
        //Start
          // file header stuff
          // $table = 'cars';  
          $insertfield = "";
          $output = "-- PHP MySQL Dump\n--\n";
          $output .= "-- Host: $dbhost\n";
          $output .= "-- Generated: " . date("r", time()) . "\n";
          $output .= "-- PHP Version: " . phpversion() . "\n\n";
          $output .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\n\n";
          $output .= "--\n-- Database: `$dbname`\n--\n";
            
          $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
          $stmt   = $pdo->query("SHOW CREATE TABLE $table");
          $row    = $stmt->fetch();
          $output.= $row[1].";\n\n";  

          // get table data
          $output .= "--\n-- Dumping data for table `$table`\n--\n\n";
          $stmt = $pdo->query("SELECT * FROM $table");
          $row = $stmt->fetch(PDO::FETCH_OBJ);
          // echo "<pre>". print_r($row) . "</pre>";

          while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
            // Runs Once Per table -- INSERT CLAUSE
            if ($insertfield == "") {
              $insertfield = "INSERT INTO `$table` (";
              //commasep initially set to no comma value as 1st entry doesnt needs to be comma seperated.
              $commasep = ""; 
              //Grabbing Each column/field Name
              foreach ($row as $key => $value) {
                $insertfield .= $commasep . "`$key`";
                $commasep = ", "; 
              } //End of For Each Loop
              $insertfield .= ") VALUES";
              $output .= $insertfield . "\n";
            }//End if

            //Grabbing Values for correspoding Column Names 
              $commasep = "";
              $output .= $commasep . "(";
              foreach($row as $key => $val){
                $output .= $commasep . "`$val`"; 
                $commasep = ",";           
              } //End For Each     
              // terminate row data
              $output .= "),";
              $output .= "\n";
            } //End While        
      } //End For Each 
      header('Content-Description: File Transfer');
      header('Content-type: application/octet-stream');
      header('Content-Disposition: attachment; filename=' . rand() . '.sql');
      header('Content-Transfer-Encoding: binary');
      header('Content-Length: ' . strlen($output));
      header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
      header('Expires: 0');
      header('Pragma: public');
      echo $output;  
      exit();     
    } //End Isset IF    
?>
<html>
<head>
<meta charset="utf-8">
<title>Used Cars</title>
<link href="styles/styles.css" rel="stylesheet" type="text/css">
</head>

<body>
<div id="wrapper">
  <form method="post">
    <?php 
    $alltable = "SHOW TABLES";
    $statement = $pdo->prepare($alltable);
    $statement->execute();
    $result = $statement->fetchAll(PDO::FETCH_COLUMN);
    echo "<pre>" . print_r($result, true) . "</pre>"; 

    foreach($result as $key => $table) {
    ?>
    <div class="checkbox">
    <label><input type="checkbox" class="checkbox_table" name="table[]" value="<?php echo $table; ?>" /> <?php echo $table; ?></label>
    </div> 
    <?php  } ?>
    <br>
    <br>
    <br>
    <fieldset>
      <legend>Download Results in SQL Format</legend>
      <p>
        <input type="submit" name="download" id="download" value="Download File">
      </p>
    </fieldset>
  </form>
</div>
</body>
</html>
24

The above line is the reason you are only getting the last table’s result. It is re-initializing $output inside the loop. You should initialize output to an empty string before the start of all the looping, then change the above line to $output .= … to concatenate the new output, rather than starting over.

Next, you have some issues in the current code -

  1. Every place you use $table in a query should have back-ticks `` around it. In this code, the table name can be anything and must be surrounded by back-ticks to prevent breaking the sql query syntax.
  2. The $pdo->setAttribute… line should be part of your connection code. It should not be inside your code trying execute and use the result from queries.
  3. The $row = $stmt->fetch(PDO::FETCH_OBJ); line, immediately following the SELECT query, is fetching and discarding the first row of data, i.e. you are missing a row of data in the result.
  4. By fetching the data as an object, you are missing out on using php array functions to dynamically operate on the data as a set, e.g. the foreach code to build the list of columns can be replaced with the following if you fetched the data as an array - $insertfield .= implode(', ',array_keys($row)); (note: there’s some back-ticks in the actual code.)
  5. You are putting back-ticks `` around the values. This is incorrect. String values should have single-quotes around them and numbers should have nothing around them. To do this, you would parse and use the column data type information from the SHOW CREATE TABLE… query. You should also properly escape string data so that it won’t break the sql query syntax when you import the .sql dump.
  6. Again, if you fetch data as an array, you can build the VALUES terms much simpler.
  7. Since you will/should be fetching data as an associative array most of the time, set this as the default fetch mode when you make the database connection, so that you don’t need to specify it in each fetch statement.
1 Like
25

Hi @mabismad,

Thanks, I will read and brainstorm and learn. I will write back to you in case of any difficulty in understanding. Thank you for taking the time to share your knowledge and helping me to correct, evolve, and learn faster.