Your Ad Here

advanced code snippet search

MySQL Dump

 / Published in: PHP
 

Creates a backup of a MySQL database in SQL format.

Expand | Embed | Plain Text 
  1. if (!function_exists('mysql_dump')) {
  2.  
  3.    function mysql_dump($database) {
  4.  
  5.       $query = '';
  6.  
  7.       $tables = @mysql_list_tables($database);
  8.       while ($row = @mysql_fetch_row($tables)) { $table_list[] = $row[0]; }
  9.  
  10.       for ($i = 0; $i < @count($table_list); $i++) {
  11.  
  12.          $results = mysql_query('DESCRIBE ' . $database . '.' . $table_list[$i]);
  13.  
  14.          $query .= 'DROP TABLE IF EXISTS `' . $database . '.' . $table_list[$i] . '`;' . lnbr;
  15.          $query .= lnbr . 'CREATE TABLE `' . $database . '.' . $table_list[$i] . '` (' . lnbr;
  16.  
  17.          $tmp = '';
  18.  
  19.          while ($row = @mysql_fetch_assoc($results)) {
  20.  
  21.             $query .= '`' . $row['Field'] . '` ' . $row['Type'];
  22.  
  23.             if ($row['Null'] != 'YES') { $query .= ' NOT NULL'; }
  24.             if ($row['Default'] != '') { $query .= ' DEFAULT \'' . $row['Default'] . '\''; }
  25.             if ($row['Extra']) { $query .= ' ' . strtoupper($row['Extra']); }
  26.             if ($row['Key'] == 'PRI') { $tmp = 'primary key(' . $row['Field'] . ')'; }
  27.  
  28.             $query .= ','. lnbr;
  29.  
  30.          }
  31.  
  32.          $query .= $tmp . lnbr . ');' . str_repeat(lnbr, 2);
  33.  
  34.          $results = mysql_query('SELECT * FROM ' . $database . '.' . $table_list[$i]);
  35.  
  36.          while ($row = @mysql_fetch_assoc($results)) {
  37.  
  38.             $query .= 'INSERT INTO `' . $database . '.' . $table_list[$i] .'` (';
  39.  
  40.             $data = Array();
  41.  
  42.             while (list($key, $value) = @each($row)) { $data['keys'][] = $key; $data['values'][] = addslashes($value); }
  43.  
  44.             $query .= join($data['keys'], ', ') . ')' . lnbr . 'VALUES (\'' . join($data['values'], '\', \'') . '\');' . lnbr;
  45.  
  46.          }
  47.  
  48.          $query .= str_repeat(lnbr, 2);
  49.  
  50.       }
  51.  
  52.       return $query;
  53.  
  54.    }
  55.  
  56. }

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: ki4ngel on July 19, 2008

Instead of using a "DESCRIBE ... " you can use "SHOW CREATE TABLE ... " which is easier.

Posted By: brucealdridge on January 29, 2009

and to be a pain mysqllisttables has been deprecated, as per php docs (http://nz2.php.net/mysqllisttables) , instead use "SHOW TABLES FROM $database"

Posted By: FederikoNavarro on September 3, 2010

It works!!! You saved my mysql project. I will recommend you web site to my friends.

You need to login to post a comment.