Login   Register  
PHP Classes
elePHPant
Icontem

File: my_utils.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of chema garrido  >  My Utils  >  my_utils.php  >  Download  
File: my_utils.php
Role: Class source
Content type: text/plain
Description: core
Class: My Utils
Export and optimize MySQL database tables
Author: By
Last change: public
Date: 2 years ago
Size: 6,222 bytes
 

Contents

Class file image Download
<?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($filename0755);
            
umask($old_mask);
            
            return 
TRUE;
        }
        return 
FALSE;
    }
        
}
//End class