Location: PHPKode > projects > Katropine DBmanager > dbmanager/db2mysql2pro/Sql_Export.php
<?php
class Sql_Export{
	
	public static function __table($econfig){
		if(	isset($econfig['drop_table']) 	&& 
			isset($econfig['if_not_exist'])	&& 
			isset($econfig['auto_increment']))
			{
				$dump = "-- Generation Time: ". date("Y-m-d H:G:s");
				$dump .= "\n\n";		
				$dump .= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";';
				$dump .= "\n\n";
				if(empty($econfig['table'])){
					$db = new Mysql_DB();
					$tables = $db->tables;
				}else{
					$tables[] = $econfig['table'];
				}
				foreach ($tables as $table){
					if(is_object($table)){
						$tbl = $table->Name;
					}else{
						$tbl = $table;
					}
					$dump .= self::exportTableSQL(self::dropTable($econfig['drop_table'],$tbl), self::ifNotExists($econfig['if_not_exist']), $econfig['auto_increment'], $tbl);
				}
				return $dump;
			}else{
				exit('Parameter not set! Error location:'.__CLASS__);
			}
	}
	
	private static function ifNotExists($bool){
		if($bool){
			return "IF NOT EXISTS ";
		}
	}
	private static function dropTable($bool, $table){
		if($bool){
			return "DROP TABLE IF EXISTS `{$table}`; \n";
		}
	}
	private static function exportTableSQL($dt, $ine, $ai, $table){
		global $link;		
				$structure = '';// reset variable
				
				$structure .= "\n-- \n";
				$structure .= "-- Table structure for table `{$table}` \n";
				$structure .= "-- \n\n";
				// Dump Structure
				$structure .= $dt;
				$structure .= "CREATE TABLE  {$ine}`{$table}` (\n";
				$mysqlOBJ = new Mysql_Data_Handler($link, '', false);
				$result = $mysqlOBJ->__dataTable("SHOW FULL FIELDS FROM `{$table}`");
				foreach($result as $rez){
					$tblstructures[] = new Mysql_Table_Structure($rez);
				}	
				foreach($tblstructures as $row) {
			
					$structure .= "  `{$row->Field}` {$row->Type}";
					$structure .= (!empty($row->Default)) ? " DEFAULT '{$row->Default}'" : false;
					$structure .= (!empty($row->Collation)) ? " COLLATE {$row->Collation}" : false;
					$structure .= ($row->Null != "YES") ? " NOT NULL" : false;
					$structure .= (!empty($row->Extra)) ? " {$row->Extra}" : false;
					$structure .= ",\n";
			
				}
				$structure = ereg_replace(",\n$", "", $structure);
				
				// Save all Column Indexes in array
				unset($indexes);
				$result = $mysqlOBJ->__dataTable("SHOW KEYS FROM `{$table}`");
				foreach($result as $rez){
					$tblkeys[] = new Mysql_Table_Index($rez);
				}
				foreach($tblkeys as $row) {

					if (($row->Key_name == 'PRIMARY') && ($row->Index_type == 'BTREE')) {
						$indexes['PRIMARY'][$row->Key_name] = $row->Column_name;
					}

					if (($row->Key_name != 'PRIMARY') && ($row->Non_unique == '0') && ($row->Index_type == 'BTREE')) {
						$indexes['UNIQUE'][$row->Key_name] = $row->Column_name;
					}

					if (($row->Key_name != 'PRIMARY') && ($row->Non_unique == '1') && ($row->Index_type == 'BTREE')) {
						$indexes['INDEX'][$row->Key_name] = $row->Column_name;
					}

					if (($row->Key_name != 'PRIMARY') && ($row->Non_unique == '1') && ($row->Index_type == 'FULLTEXT')) {
						$indexes['FULLTEXT'][$row->Key_name] = $row->Column_name;
					}

				}
				if (is_array($indexes)) {
					foreach ($indexes as $xy => $columns) {

						$structure .= ",\n";

						$c = 0;
						foreach ($columns as $column_key => $column_name) {

							$c++;
							$structure .= ($xy == "PRIMARY") ? "  PRIMARY KEY  (`{$column_name}`)" : false;
							$structure .= ($xy == "UNIQUE") ? "  UNIQUE KEY `{$column_key}` (`{$column_name}`)" : false;
							$structure .= ($xy == "INDEX") ? "  KEY `{$column_key}` (`{$column_name}`)" : false;
							$structure .= ($xy == "FULLTEXT") ? "  FULLTEXT `{$column_key}` (`{$column_name}`)" : false;

							$structure .= ($c < (count($indexes[$xy]))) ? ",\n" : false;

						}

					}

				}
				$structure .= "\n)";
				
				$table_stats = new Mysql_Table($table);
				
				$structure .= "ENGINE=".$table_stats->Engine." ";
				$structure .= "COLLATE=".$table_stats->Collation." ";
				if($ai){
					if($table_stats->Auto_increment != 'NULL' || $table_stats->Auto_increment != NULL){
						$structure .= "AUTO_INCREMENT=".$table_stats->Auto_increment."\n";
					}
				}
				$structure .= ";\n";
				// Header
				$structure .= "\n-- \n";
				$structure .= "-- Dumping data for table `$table` \n";
				$structure .= "-- \n\n";

				// Dump data
				$data='';
				$result = $mysqlOBJ->__dataTable("SELECT * FROM {$table}");
				$num_rows   = $mysqlOBJ->__dataNum_rows();
				$num_fields = $mysqlOBJ->__dataNum_fields();
					
				if($num_rows != false){
				foreach ($result as $red){
					$data .= "INSERT INTO `$table` (";

					// Field names
					$x = 0;
					foreach ($red as $key=>$val){
						$field_name =$key;
						$data .= "`{$field_name}`";
						$data .= ($x < ($num_fields - 1)) ? ", " : false;
						$x++;
					}

					$data .= ") VALUES (";

					// Values
					$x = 0;
					foreach ($red as $key=>$val){
						$field_name = $key;
						
						foreach ($tblstructures as $tblstruct){
							if($tblstruct->Field == $field_name){
								$hex = false;
								if(self::isTextValue($tblstruct->Type)){
									 // empty blobs need to be different, but '0' is also empty :-(
									if (empty($red[$field_name]) && $red[$field_name] != '0') {
				                        $value = '\'\'';
				                        $hex = true;
				                    } else {
										$value = '0x'.bin2hex($red[$field_name]);
										$hex = true;
				                    }
								}else{
									$value = $red[$field_name];
								}
							}
						}
						if($hex){
							$data .= $value;
						}else{
							$data .= "'" . str_replace('\"', '"', htmlentities(mysql_escape_string($value))) . "'";
						}
						$data .= ($x < ($num_fields - 1)) ? ", " : false;
						$x++;
					}

					$data.= ");\n";
				}
				}else{
					$data.= "-- [!!! EMPTY TABLE] ---------------------------------------";
				}
				$data.= "\n";

				$dump .= $structure . $data;
				$dump .= "-- --------------------------------------------------------\n\n";

			

			return $dump;
				
	}
	private static function exportTableCSV(){
		
	}
	private function isTextValue($field_type) {
		switch ($field_type) {
			case "tinytext":
			case "text":
			case "mediumtext":
			case "longtext":
			case "binary":
			case "varbinary":
			case "tinyblob":
			case "blob":
			case "mediumblob":
			case "longblob":
				return True;
				break;
			default:
				return False;
		}
	}
	
}
?>
Return current item: Katropine DBmanager