<?php
/**
* MySQL utils, optimize, export, show tables...
*
* @package my_utils
* @version 0.1 (2012-02-29)
* @license GPL v3
* @author Chema Garrido <chema@garridodiaz.com>
*/
class my_utils {
private $dbname;
private $db;//db connection instance
private $folder;//folder to store the dump
/**
*
* initialize mysql connection
* @param string $dbuser
* @param string $dbpass
* @param string $dbhost
* @param string $dbname
* @param string $folder
*/
public function __construct($dbuser='', $dbpass='', $dbhost='localhost', $dbname='',$folder=NULL)
{
$this->db = @mysql_connect($dbhost,$dbuser,$dbpass);
if (! $this->db )
{
die('<ol><li><b>Error establishing a database connection!</b>
<li>Are you sure you have the correct user/password?
<li>Are you sure that you have typed the correct hostname?
<li>Are you sure that the database server is running?</ol>');
}
$this->set_dbname($dbname);
$this->set_folder($folder);
}
/**
*
* closes mysql connection
*/
public function __destruct()
{
mysql_close($this->db);
}
/**
*
* writes the create tables in a destination folder
* @param string $folder
* @param array $tables
* @param boolean $data
* @param boolean $verbose
*/
public function export($folder=NULL,$tables=NULL,$data=FALSE,$verbose=TRUE)
{
$this->set_folder($folder);
//which tables to export
$tables = ($tables==NULL)? $this->show_tables():$tables;
foreach ($tables as $table_name)
{
$create_table = $this->show_create_table($table_name,$data);
$this->write_table_content($table_name,$create_table,$verbose);
}
}
/**
*
* get tables name from a db
* @param string $dbname
* @return boolean | array
*/
public function show_tables($dbname=NULL)
{
if ($dbname==NULL)
$dbname = $this->dbname;
//$query = 'SHOW TABLE STATUS FROM '. $dbname;
$query = 'SHOW TABLES FROM '. $dbname;
if ($result = $this->query($query))
{
$tables = array();
while($row = mysql_fetch_row($result)) array_push($tables, $row[0]);
return $tables;
}
return FALSE;
}
/**
*
* given a table name shows the table structure and returns the contents
* @param string $table_name
* @param boolean $data
* @return boolean | string
*/
public function show_create_table($table_name=NULL,$data=FALSE)
{
if ($table_name!==NULL)
{
if ($result = $this->query('SHOW CREATE TABLE '.$table_name))
{
$row = mysql_fetch_assoc($result);
$table_schema = preg_replace("/AUTO_INCREMENT=[\w]*./", '', $row['Create Table']).';';//deletes the autoincrement in the text
if ($data==TRUE)//if we said to export the data
$table_schema .= PHP_EOL.PHP_EOL.$this->get_data_table($table_name);
return $table_schema;
}
}
return FALSE;
}
/**
*
* given a table name returns all his data ready for export
* @param string $table_name
* @return boolean | string
*/
private function get_data_table($table_name)
{
if ($result = $this->query('SELECT * FROM '.$table_name))
{
$data = '';
while($row = mysql_fetch_row($result))
{
foreach($row as &$value){
$value = htmlentities(addslashes($value));
}
$data .= 'INSERT INTO '. $table_name .' VALUES (\'' . implode('\',\'', $row) . '\');'.PHP_EOL;
}
return $data;
}
return FALSE;
}
/**
*
* optimize table
* @param array $table
* @return boolean | string
*/
public function optimize_tables($tables=NULL,$verbose=TRUE)
{
$tables = ($tables==NULL)? $this->show_tables():$tables;
$tables=implode(', ',$tables);
$start = microtime();
$this->query('OPTIMIZE TABLE '.$tables);
if ($verbose) echo 'OPTIMIZE TABLE '.$tables.' in '.round(microtime()-$start,2).'s';
return FALSE;
}
/**
*
* writes the table content into an sql file
* @param string $table_name
* @param string $table_schema
* @return boolean
*/
private function write_table_content($table_name,$table_schema,$verbose=FALSE)
{
if ($table_schema!==FALSE && $table_name)
{
//$filename = $this->folder.date('Y-m-d').'-'.$table_name.'.sql';//filename ex: tables/2001-02-17-tablename.sql
$filename = $this->folder.$table_name.'.sql';//filename ex: tables/tablename.sql
if ($this->fwrite($filename, $table_schema))
{
if ($verbose) echo 'Created file: '.$filename.'<br />';
return TRUE;
}
}
return FALSE;
}
/**
*
* performs mysql query
* @param string $sql
* @return resource | boolean
*/
private function query($sql)
{
//echo $sql;
$result = @mysql_query($sql,$this->db);
//(var_dump($result));
return (mysql_num_rows($result))? $result:FALSE;
}
/**
*
* sets db to use in this connection
* @param string $dbname
*/
public function set_dbname($dbname=NULL)
{
$this->dbname = $dbname;
if ( ! @mysql_select_db($this->dbname,$this->db))
{
die('<ol><li><b>Error selecting database <u>'.$dbname.'</u>!</b>
<li>Are you sure it exists?
<li>Are you sure there is a valid database connection?</ol>');
}
}
/**
*
* sets the folder to export
* @param string $folder
*/
public function set_folder($folder=NULL)
{
if ($folder==NULL)
{
//sets as default
$this->folder = __DIR__.'/export/';
}
else
{
$this->folder = $folder;
}
//die($this->folder);
if (!is_writable($this->folder))
{
die('Folder no writable '.$this->folder);
}
}
/**
* write to file
* @param $filename fullpath file name
* @param $content
* @return boolean
*/
private function fwrite($filename,$content)
{
if ($file = fopen($filename, 'w'))//able to create the file
{
//writting content
fwrite($file, $content);
fclose($file);
//changing permissions
$old_mask = umask(0);
chmod($filename, 0755);
umask($old_mask);
return TRUE;
}
return FALSE;
}
}//End class
|
Download
