Forkerte SQL im/export

Hey folkens.

Jeg sidder og roder lidt med noget mysql export.

Men jeg har haft nogle problemer. Den opretter helt fint backup'en osv, men den giver nogle fejl, hvis jeg bruger sql-promten til udføre sql-kommandoen.

Source: http://www.phpclasses.org/browse/package/1931.html

Script:

 <?php 
 
 /*************************************************************** 
 * SQL_Export class 
 * By Adam Globus-Hoenich, 2004 (adam@phenaproxima.net) 
 * Use this class as freely as you like. It is 100% free and 
 * modifiable :) 
 ***************************************************************/ 
 
 class SQL_Export 
 { 
     var $cnx; 
     var $db; 
     var $server; 
     var $port; 
     var $user; 
     var $password; 
     var $table; 
     var $tables; 
     var $exported; 
 
     function SQL_Export($server, $user, $password, $db, $tables) 
     { 
         $this->db = $db; 
         $this->user = $user; 
         $this->password = $password; 
          
         $sa = explode(":", $server); 
         $this->server = $sa[0]; 
         $this->port = $sa[1]; 
         unset($sa); 
 
         $this->tables = $tables; 
 
         $this->cnx = mysql_connect($this->server, $this->user, $this->password) or $this->error(mysql_error()); 
         mysql_select_db($this->db, $this->cnx) or $this->error(mysql_error()); 
     } 
 
 
     function export() 
     { 
         foreach($this->tables as $t) 
         { 
             $this->table = $t; 
             $header = $this->create_header(); 
             $data = $this->get_data(); 
             $this->exported .= "###################\n# Dumping table $t\n###################\n\n$header" . $data . "\n"; 
         } 
          
         return($this->exported); 
     } 
 
     function create_header() 
     { 
         $fields = mysql_list_fields($this->db, $this->table, $this->cnx); 
         $h = "CREATE TABLE `" . $this->table . "` ("; 
          
         for($i=0; $i<mysql_num_fields($fields); $i++) 
         { 
             $name = mysql_field_name($fields, $i); 
             $flags = mysql_field_flags($fields, $i); 
             $len = mysql_field_len($fields, $i); 
             $type = mysql_field_type($fields, $i); 
 
             $h .= "`$name` $type($len) $flags,"; 
 
             if(strpos($flags, "primary_key")) { 
                 $pkey = " PRIMARY KEY (`$name`)"; 
             } 
         } 
          
         $h = substr($h, 0, strlen($d) - 1); 
         $h .= "$pkey) TYPE=MyISAM;\n\n"; 
         return($h); 
     } 
 
     function get_data() 
     { 
         $d = null; 
         $data = mysql_query("SELECT * FROM `" . $this->table . "` WHERE 1", $this->cnx) or $this->error(mysql_error()); 
          
         while($cr = mysql_fetch_array($data, MYSQL_NUM)) 
         { 
             $d .= "INSERT INTO `" . $this->table . "` VALUES ("; 
 
             for($i=0; $i<sizeof($cr); $i++) 
             { 
                 if($cr[$i] == '') { 
                     $d .= 'NULL,'; 
                 } else { 
                     $d .= "'$cr[$i]',"; 
                 } 
             } 
 
             $d = substr($d, 0, strlen($d) - 1); 
             $d .= ");\n"; 
         } 
 
         return($d); 
     } 
 
     function error($err) 
     { 
         die($err); 
     } 
 } 
 
 ?>

Og så til at udskrive export:

 <?
     //Connect to DB the old fashioned way and get the names of the tables on the server 
     $cnx = mysql_connect($server, $username, $password) or die(mysql_error()); 
     mysql_select_db($db, $cnx) or die(mysql_error()); 
     $tables = mysql_list_tables($db) or die(mysql_error()); 
 
     //Create a list of tables to be exported 
     $table_list = array(); 
     while($t = mysql_fetch_array($tables)) 
     { 
         array_push($table_list, $t[0]); 
     } 
 
     //Instantiate the SQL_Export class 
     require("SQL_Export.php");
 
     $e = new SQL_Export($server, $username, $password, $db, $table_list); 
     //Run the export 
 
 $backup = $e->export(); 
 
 echo $backup;
 ?>

Og et eksempel på output den giver:

 ###################
 # Dumping table cms_backups
 ###################
 
 CREATE TABLE `cms_backups` (`id` int(11) not_null primary_key auto_increment,`dato` string(255) not_null,`backuplink` blob(-1) not_null blob PRIMARY KEY (`id`)) TYPE=MyISAM;
 
 INSERT INTO `cms_backups` VALUES ('1','09-07-2008 14:14:32','test.txt');

Men altså, når jeg så indsætter den overstående sql kommando, siger den følgende:

 SQL-forespørgsel:
 
 CREATE TABLE  `cms_backups` (
 `id` INT( 11 ) not_null primary_key AUTO_INCREMENT ,
 `dato` STRING ( 255 ) not_null,
 `backuplink` BLOB( -1 ) not_null BLOB PRIMARY KEY (  `id` )
 ) TYPE = MYISAM ;
 
 
 MySQL returnerede: 
 
 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not_null primary_key auto_increment,`dato` string(255) not_null,`backuplink` blo' at line 1

Og jeg kan jo godt se at den laver fejl så som:
`dato` STRING ( 255 ) STRING skal jo f.eks være VARCHAR og BLOB skal være LONGTEXT.

Det må være her omkring i koden vi skal rette:

 <?
         $h = "CREATE TABLE `" . $this->table . "` ("; 
          
         for($i=0; $i<mysql_num_fields($fields); $i++) 
         { 
             $name = mysql_field_name($fields, $i); 
             $flags = mysql_field_flags($fields, $i); 
             $len = mysql_field_len($fields, $i); 
             $type = mysql_field_type($fields, $i); 
 
             $h .= "`$name` $type($len) $flags,"; 
 
             if(strpos($flags, "primary_key")) { 
                 $pkey = " PRIMARY KEY (`$name`)"; 
             } 
         } 
          
         $h = substr($h, 0, strlen($d) - 1); 
         $h .= "$pkey) TYPE=MyISAM;\n\n"; 
         return($h); 
     } 
 ?>

Her kommer så et eksempel på en normal export via phpmyadmin:

 CREATE TABLE `cms_backups` (
   `id` int(11) NOT NULL auto_increment,
   `dato` varchar(255) NOT NULL,
   `backuplink` longtext NOT NULL,
   UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
 
 -- 
 -- Data dump for tabellen `cms_backups`
 -- 
 
 INSERT INTO `cms_backups` VALUES (1, '09-07-2008 14:14:32', 'test.txt');

Håber i kan hjælpe mig, det ville nemlig være super ;-).