Location: PHPKode > projects > Pligg > pligg/libs/backup/mysql_backup/mysql_backup.class.php
<?php
////////////////////////////////////////////////////////////////////////////////
//
//		Class name: mysql_backup
//		PHP version: 4.0.2
//		Function: Back up/Restore a MySql db.
//		Written by: Peyman Hooshmandi Raad
//		Date: Jan 9, 2003
//		Contact: hide@address.com
//=============================================================================
//
//		Methods:
//=============================================================================
//		1) Backup this method will back up the whole database.
//		$output: text file name and path(the one that holds backup)
//		$structure_only: (true / false)
//				true : backup method will only make backup from tables NOT the data.
//				false : backup method will make backup from tables and the data(WHOLE DB).
//		
//		2) Restor This method will create table with data from a txt file.
//		
//		Copyright: this class is free for non commercial uses.		
//
////////////////////////////////////////////////////////////////////////////////
class mysql_backup
	{
	//---- Class Variables.
	//---------------------
	var $host; //---- host name e.g. localhost
	var $db; //---- db name
	var $user; //---- db username
	var $pass; //---- db password
	var $output; //---- file name(sqldata.txt)
	var $structure_only; //---- Output method : true/false
	var $fptr; //---- Do Not change this.
	//---------------------
	
	//---- Constructor function: This will Inisialize variables.
	//----------------------------------------------------------
	function mysql_backup($host,$db,$user,$pass,$output,$structure_only)
		{
		set_time_limit (120);
		$this->host = $host;
		$this->db = $db;
		$this->user = $user;
		$this->pass = $pass;
		$this->output = $output;
		$this->structure_only = $structure_only;
		}
	//----------------------------------------------------------

	//---- This will create the sqldata.txt file.
	//-------------------------------------------	
	function _Mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)  
		{  
	
		if (strval($this->output)!="") $this->fptr=fopen($this->output,"w"); else $this->fptr=false;  
	
		//connect to MySQL database 
		$con=mysql_connect($this->host,$this->user, $this->pass);  
		$db=mysql_select_db($dbname,$con);  
	
		//open back-up file ( or no file for browser output) 
	
		//set up database 
		//out($this->fptr, "create database $dbname;\n\n");  
	
		//enumerate tables 
		$res=mysql_list_tables($dbname);  
		$nt=mysql_num_rows($res);  
	
		for ($a=0;$a<$nt;$a++)  
			{  
			$row=mysql_fetch_row($res);  
			$tablename=$row[0];  
	
			//start building the table creation query 
			$sql="create table $tablename\n(\n";  
	
			$res2=mysql_query("select * from $tablename",$con);  
			$nf=mysql_num_fields($res2);  
			$nr=mysql_num_rows($res2);  
	
			$fl="";  
	
			//parse the field info first 
			for ($b=0;$b<$nf;$b++)  
				{  
				$fn=mysql_field_name($res2,$b);  
				$ft=mysql_fieldtype($res2,$b);  
				$fs=mysql_field_len($res2,$b);  
				$ff=mysql_field_flags($res2,$b);  
	
				$sql.="    $fn ";  
	
				$is_numeric=false;  
				switch(strtolower($ft))  
					{  
					case "int":  
						$sql.="int";  
						$is_numeric=true;  
						break;  
	
					case "blob":  
						$sql.="text";  
						$is_numeric=false;  
						break;  
	
					case "real":  
						$sql.="real";  
						$is_numeric=true;  
						break;  
	
					case "string":  
						$sql.="char($fs)";  
						$is_numeric=false;  
						break;  
	
					case "unknown":  
						switch(intval($fs))  
							{  
							case 4:    //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type 
								$sql.="tinyint";  
								$is_numeric=true;  
								break;  
	
							default:    //we could get a little more optimzation here! (i.e. check for medium ints, etc.) 
								$sql.="int";  
								$is_numeric=true;  
								break;   
							}  
						break;  
	
					case "timestamp":  
						$sql.="timestamp";   
						$is_numeric=true;  
						break;  
	
					case "date":  
						$sql.="date";   
						$is_numeric=false;  
						break;  
	
					case "datetime":  
						$sql.="datetime";   
						$is_numeric=false;  
						break;  
	
					case "time":  
						$sql.="time";   
						$is_numeric=false;  
						break;  
	
					default: //future support for field types that are not recognized (hopefully this will work without need for future modification) 
						$sql.=$ft;  
						$is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown 
						break;  
					}  
	
				//VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator 
	
				if (strpos($ff,"unsigned")!=false)  
					{  
					//timestamps are a little screwy so we test for them 
					if ($ft!="timestamp") $sql.=" unsigned";  
					}  
	
				if (strpos($ff,"zerofill")!=false)  
					{  
					//timestamps are a little screwy so we test for them 
					if ($ft!="timestamp") $sql.=" zerofill";  
					}  
	
				if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";  
				if (strpos($ff,"not_null")!=false) $sql.=" not null";  
				if (strpos($ff,"primary_key")!=false) $sql.=" primary key";  
	
				//End of field flags 
	
				if ($b<$nf-1)  
					{  
					$sql.=",\n";  
					$fl.=$fn.", ";  
					}  
				else  
					{  
					$sql.="\n);\n\n";  
					$fl.=$fn;  
					}  
	
				//we need some of the info generated in this loop later in the algorythm...save what we need to arrays 
				$fna[$b]=$fn;  
				$ina[$b]=$is_numeric;  
				  
				}  
	
			//$this->_Out($sql);  
	
			if ($this->structure_only!=true)  
				{  
				//parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself... 
				for ($c=0;$c<$nr;$c++)  
					{  
					$sql="insert into $tablename ($fl) values (";  
	
					$row=mysql_fetch_row($res2);  
	
					for ($d=0;$d<$nf;$d++)  
						{  
						$data=strval($row[$d]);  
					  
						if ($ina[$d]==true)  
							$sql.= intval($data);  
						else  
							$sql.="\"".mysql_escape_string($data)."\"";  
	
						if ($d<($nf-1)) $sql.=", ";  
		  
						}  
	
					$sql.=");\n";  
					//echo $sql;
					$this->_Out($sql);  
	
					}  
	
				$this->_Out("\n\n");  
	
				}  
	
			mysql_free_result($res2);      
	
			}  
		  
		if ($this->fptr!=false) fclose($this->fptr);  





		if ($this->structure_only==true)  
			{  

			$fname = $output;
			if (!mysql_connect($host, $uid, $pwd)) {
			   echo 'Could not connect to mysql';
			   exit;
			}
			
			$sql = "SHOW TABLES FROM $dbname";
			$result = mysql_query($sql);
			
			if (!$result) {
			   echo "DB Error, could not list tables\n";
			   echo 'MySQL Error: ' . mysql_error();
			   exit;
			}
			
			$fp = fopen($fname, "w");
			
			while ($row = mysql_fetch_row($result)) {
			
				$result2 = mysql_query("SHOW CREATE TABLE `".$row[0]."`");
				$row2 = mysql_fetch_row($result2);
				
				fwrite($fp, "DROP TABLE IF EXISTS `".$row[0]."`;\n\n");
			
				fwrite($fp, $row2[1] . ";\n\n");
				
			}
			
			fclose($fp);
			
			mysql_free_result($result2);
			mysql_free_result($result);

		}






		return 0;  
	
		}
	//-------------------------------------------
	
	//---- This will Open sqldata.txt.
	//--------------------------------	
	function _Open()
		{
		$filename = $this->output;
		$fp = fopen( $filename, "r" ) or die("Couldn't open $filename");
		while ( ! feof( $fp ) )
			{
			$line = fgets( $fp, 1024 );
			$SQL .= "$line";
			}
		return $SQL;
		}
	//--------------------------------
	
	//---- This will Restore data in sqldata.txt
	//------------------------------------------
	function Restore($sep=";")
		{
		//---- Calls _Open function to make an array of sql peices.
		//---------------------------------------------------------
		$SQL = explode($sep, $this->_Open($this->output));
		//---------------------------------------------------------
		
		//---- Make connection to MySql and the specified db.
		//---------------------------------------------------
		$link = mysql_connect($this->host, $this->user, $this->pass) or (die (mysql_error()));
		mysql_select_db($this->db, $link) or (die (mysql_error()));
		//---------------------------------------------------
		
		
		//---- This loop will execute every peice of SQL.
		//-----------------------------------------------
		for ($i=0;$i<count($SQL)-1;$i++)
			{			
				//echo $SQL[$i] . $sep . '<BR>';
				mysql_unbuffered_query($SQL[$i] . $sep) or (die (mysql_error()));
			}
		//-----------------------------------------------
	}
	//---------------------------------------------------------
	
	//---- This will put data in sqldata.txt
	//------------------------------------------
	function _out($s)  
		{  
		if ($this->fptr==false) echo("$s"); else fputs($this->fptr,$s);  
		}
	//------------------------------------------
	
	//---- This is the function to be called for backup.
	//--------------------------------------------------
	function Backup()
		{
		$this->_Mysqlbackup($this->host,$this->db,$this->user,$this->pass,$this->output,$this->structure_only); 
		return 1;
		}
	//--------------------------------------------------
	}
?>
Return current item: Pligg