PHP Classes
elePHPant
Icontem

File: ezMySQL.cls.lib.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of S. M. ARIFUL ISLAM  >  ezMySQL  >  ezMySQL.cls.lib.php  >  Download  
File: ezMySQL.cls.lib.php
Role: Class source
Content type: text/plain
Description: Class to view database and manage(create, update, delete) stored procedures.
Class: ezMySQL
Manage MySQL database tables and stored procedures
Author: By
Last change: In class library, FormatText($txt) function has been changed.
Date: 7 years ago
Size: 7,646 bytes
 

Contents

Class file image Download
<?php
/***************************************************************************
 * ------- ezMySQL.cls.lib.php --------
 *--------------------------------------------------------------------------
 *
 * Author : S. M. ARIFUL ISLAM
 * Email : arif_look@yahoo.com
 * Contact : +88-0152344105
 * Country : Bangladesh
 * Begin : Thursday, Feb 15, 2007
 * Website : www.bsourcing.com
 * Check updates at : www.bsourcing.com/developers/classes/
 *
 ***************************************************************************/

class ezMySQL //Easy MySQL Administration Class Library.
{
    var
$apache; //Store apache server information
   
var $php; //Store php inforation
   
var $mysql_v_full; //MySQL version information(full)
   
var $mysql_v; //MySQL version information without floating point
   
var $strLink; //Database connection string
   
var $dbname; //Store current database name

    //Constructor.
   
function ezMySQL($dummyuser="", $dummypass="")
    {
       
$dummyuser = (!$dummyuser) ? "root" : $dummyuser;
       
$dummypass = (!$dummypass) ? "" : $dummypass;
       
$link=mysql_connect("localhost", $dummyuser, $dummypass); //Dummy connection to MySQL.
       
$this->apache=apache_get_version();
       
$this->php=phpversion();
       
$this->mysql_v_full=mysql_get_server_info();//Get MySQL Version Information(full).
       
$this->mysql_v=(int)mysql_get_server_info();//Get MySQL Version without floating point.
       
$this->strLink = $link;
    }
//End method.
   
    //Establish a connection with MySQL database.
   
function dbConnect($user="", $pass="", $dbname="", $host="")
    {
       
//Prepare connection requirements.
       
$UserName = (!$user) ? "root" : $user;
       
$Password = (!$pass) ? "" : $pass;
       
$DbName = (!$dbname) ? die("You should specify a database name.") : $dbname;
       
$HostName = (!$host) ? "localhost" : $host;
       
       
//Connect to the MySQL Server.
       
$this->strLink = @mysql_pconnect($HostName, $UserName, $Password) or die("Could Not Connect Database.");
       
//Selection of given database.
       
@mysql_select_db($DbName, $this->strLink) or die(mysql_error());
       
$this->dbname = $dbname;
    }
//End method.
   
    //Get table structures of a database.
   
function dbStructure()
    {
       
$tables = mysql_list_tables($this->dbname);
       
$num_tables = mysql_num_rows($tables);
       
       
//Select the current Database and Print.
       
$row=mysql_fetch_row(mysql_query("SELECT DATABASE()"));
        echo
"<br />";
        echo
"<span class='HeadText18'><b>Database Name:</b></span><span class='ContText14'> [ ".$row[0]." ]</span>";
        echo
"<br /><br />";
   
       
$i = 0;
        while(
$num_tables > $i)
        {
           
//Get Table name from array;
           
$TableName = mysql_tablename($tables, $i);
           
$fields = mysql_db_query($this->dbname, "SHOW FIELDS FROM ".$TableName) or die(mysql_error());
           
$output .= "<table class='ContText14' border=1 width=650 bordercolor='#C5E2C5'>";
           
$output .= "<tr bgcolor='#C5E2C5'><td colspan=6>&nbsp;<b>Table Name:</b> ".$TableName."</td></tr>";
           
$output .= "<tr><td height=5 colspan=6></td></tr>";
           
$output .= "<tr bgcolor='#EEEEDD'>"
                           
."<td>&nbsp;<b>Field Name</b></td>"
                           
."<td>&nbsp;<b>Type</b></td>"
                           
."<td>&nbsp;<b>Null</b></td>"
                           
."<td>&nbsp;<b>Key</b></td>"
                           
."<td>&nbsp;<b>Default</b></td>"
                           
."<td>&nbsp;<b>Extra</b></td>"
                       
."</tr>";
           
           
//Get column content.
           
while($row=mysql_fetch_array($fields))
            {
               
$output .="<tr bgcolor='#EEEEDD'>"
                           
."<td>&nbsp;".$row["Field"]."</td>"
                           
."<td>&nbsp;".$row["Type"]."</td>"
                           
."<td>&nbsp;".$row["Null"]."</td>"
                           
."<td>&nbsp;".$row["Key"]."</td>"
                           
."<td>&nbsp;".$row["Default"]."</td>"
                           
."<td>&nbsp;".$row["Extra"]."</td>"
                       
."</tr>";
            }
           
           
$output .="</table><br /><br />";
   
           
$i++;
        }
   
        echo
$output;
    }
//End method.
   
    //Get Stored Procedure list.
    //Here--
    // $UpdateFile = File name with extension (i.e, page.php) from which page you will call the method - UpdateSP().
    // $ThisPage = File name with extension (i.e, page.php) from which page you called this method.
   
function ProcedureList($UpdateFile="", $ThisPage="")
    {
       
$sql="SHOW PROCEDURE STATUS";
       
$result=mysql_query($sql, $this->strLink);
       
       
$html_out= "<table width=90% border=1 bordercolor=#FFFFFF style='border-collapse:collapse'><tr>"
                       
."<td width=10% class='TitleText12' align='center'>SL.</td>"
                       
."<td width=30% class='TitleText12' align='left'>&nbsp;&nbsp;Database</td>"
                       
."<td width=40% class='TitleText12' align='left'>&nbsp;&nbsp;Procedure (Update)</td>";
        if(
$ThisPage!="")
           
$html_out .="<td width=20% class='TitleText12' align='center'>Delete</td>";
           
           
$html_out .="</tr>";
           
        if(
mysql_num_rows($result)>0)
        {
           
$sl=0;
            while(
$row = mysql_fetch_row($result))
            {
               
$sl++;
               
$html_out .="<tr>";
               
$html_out .="<td height=20 class='TitleText12' align='center'>".$sl.".</td>";
               
$html_out .="<td align='left'>&nbsp;&nbsp;".$row[0]."</td>";
               
$html_out .="<td align='left'>&nbsp;&nbsp;<a class=\"SPLink\" href=\"$UpdateFile?dbName=".$row[0]."&&SPName=".$row[1]."\" target=\"_blank\">".$row[1]."</a></td>";
                if(
$ThisPage!="")
                   
$html_out .="<td align='center'><a href='$ThisPage?del=1&&dbName=".$row[0]."&&SPName=".$row[1]."' class='SPLink'><b>Delete</b></a></td>";
               
               
$html_out .="</tr>";
            }
        }else
        {
           
$row=mysql_fetch_row($result);
               
$html_out .="<tr><td class='TitleText12'>Procedure list is empty.</td></tr>";
        }
       
$html_out .= "</table>";
       
        return
$html_out;
    }
//End method.
   
    //Here, first drop the existing procedure. Then Create a new procedure.
    //Here--
    // $SPName = Stored Procedure Name
    // $SPBody = Full stored procedure code.
   
function UpdateSP($SPName, $SPBody)
    {
       
mysql_query("DROP PROCEDURE IF EXISTS ".$SPName);
       
$result=mysql_query($SPBody);
        if(
$result)
        {
            return
"Stored procedure updated successfully.";
        }else
        {
            return
mysql_error();
        }
    }
//End method.
   
    //Create a new stored procedure.
    //Here, $SPBody = Full stored procedure code.
   
function CreateSP($SPBody="")
    {
        if(
$this->mysql_v>=5) //Check mysql version
       
{
            if(
$SPBody!="")
            {
               
$SPBody = $this->FormatText($SPBody);
               
$result = mysql_query($SPBody);
                if(
$result)
                {
                   
$strmsg = "Procedure created successfully.";
                    return
$strmsg;
                }else
                {
                    return
mysql_error();
                }
            }else
            {
               
mysql_close($this->strLink);
               
$strmsg = "Your procedure code is empty.";
                return
$strmsg;
            }
        }else
        {
               
$strmsg = "Could not create PROCEDURE. Please check your MySQL version and other features.";
                return
$strmsg;
        }
    }
//End method.
   
    //Drop stored procedure
    //Here, $SPName = Stored Procedure Name
   
function DropProcedure($SPName)
    {
       
$result = mysql_query("DROP PROCEDURE ".$SPName);
        if(
$result)
        {
           
mysql_close($this->strLink);
            return
"The procedure [ ".$SPName." ] droped successfully";
        }
        else
            return
mysql_error();
    }
//End method.
   
    //Show the code of a Stored Procedure
    //Here, $SPName = Stored Procedure Name
   
function ShowCreateSP($SPName)
    {
       
$result=mysql_query("SHOW CREATE PROCEDURE ".$SPName);
        if(
$result)
        {
           
$row = mysql_fetch_row($result);
           
mysql_close($this->strLink);
            return
$this->FormatText($row[2]);
        }else
        {
            return
mysql_error();
        }
    }
//End method.
   
    //Eliminate single coat('), dubble coat(") and back-slash(\) from String.
   
function FormatText($txt)
    {
       
$formated = str_replace("\\", "", $txt); //Exclude (\)
       
return $formated;
    }
}
?>