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: 3 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($filename, 0755);
           
umask($old_mask);
           
            return
TRUE;
        }
        return
FALSE;
    }
       
}
//End class