dbUtils is a simple, pure-PHP solution which allows anyone to manage multiple mySQL databases and tables. dbUtils currently consists of 3 functions to allow you to copy databases and tables (matching your regular expression) from one server to another, another to rename a database, and one to copy a table from one database to another. Designed originally for database backup without the need for command-line access or telnet access, but expanded to provide functions normally not available in most database management front-ends like phpMyAdmin.

By : gr8gonzo

<?
// ### dbUtils for mySQL v1.0b ###
// by Jonathan Hilgeman
//
// dbRename:
// Description: Renames a specific Database
//
// Syntax: dbRename("Server_Name","Original_Database_Name","New_Database_Name");
//
// Example: dbRename("ServerOne","MyyDatabase","MyDatabase");
// (Correcting the misspelling of "My" in the Database Name)
// -----------------------------------------------------------------------------
//
// dbCopy:
// Description: Duplicates Select Tables or Databases from One Server to Another Using RegExps
//
// Syntax: dbCopy("From_Server","To_Server","Database_Regular_Expression","Table_Regular_Expression");
//
// Example: dbCopy("ServerOne","ServerTwo","/.*/","/.*/");
// (Copies all databases and tables from ServerOne to ServerTwo)
//
// -----------------------------------------------------------------------------
//
// dbTableCopy:
// Description: Copies a Table from One Database to Another On the Same Server
//
// Syntax: dbTableCopy("Server_Name","From_Database","To_Database","Table_To_Copy");
//
// Example: dbTableCopy("ServerOne","MyDatabase","YourDatabase","Shared_Info");
//

// #############
// Configuration
// #############

// Define Hosts (as many as you want)
// Example:
// $db["Server_Name"]["user"] = "mySQL Username";
// $db["Server_Name"]["pass"] = "mySQL Password";
// $db["Server_Name"]["host"] = "mySQL Host/IP Address";
// $db["Server_Name"]["port"] = "mySQL Port (usually 3306)";
// $db["Server_Name"]["time"] = "mySQL Connect Timeout in Seconds";

$db["ServerOne"]["user"] = "root"; // mySQL Username
$db["ServerOne"]["pass"] = "password"; // mySQL Password
$db["ServerOne"]["host"] = "localhost"; // mySQL Host/IP Address
$db["ServerOne"]["port"] = 3306; // mySQL Port (default is 3306)
$db["ServerOne"]["time"] = 10; // mySQL Connect Timeout in Seconds

$db["ServerTwo"]["user"] = "root";
$db["ServerTwo"]["pass"] = "password";
$db["ServerTwo"]["host"] = "www.sitecreative.com";
$db["ServerTwo"]["port"] = 3306;
$db["ServerTwo"]["time"] = 10;

$db["ServerThree"]["user"] = "user";
$db["ServerThree"]["pass"] = "pass";
$db["ServerThree"]["host"] = "127.0.0.1";
$db["ServerThree"]["port"] = 3306;
$db["ServerThree"]["time"] = 10;

// ################
// Define Functions
// ################

// ___________
// |¯¯¯¯¯¯¯¯¯¯¯¯¯|
// | dbTableCopy |
// |_____________|
// ¯¯¯¯¯¯¯¯¯¯¯
function dbTableCopy($Host,$FromDatabaseName,$ToDatabaseName,$dbTableName)
{
// Connect to Database
$dbLink = dbConnect($Host);

// Construct Query to Send to Receiving Server

// Table Definitions
$SendQuery[] = "DROP TABLE IF EXISTS $dbTableName;";
$SendQuery[] = ReturnCreateTable($FromDatabaseName, $dbTableName, $dbLink);

// Data Inserts
$TableInserts = ReturnTableInserts($FromDatabaseName, $dbTableName, $dbLink);

if(count($TableInserts))
{
foreach($TableInserts as $InsertString)
{
$SendQuery[] = $InsertString;
}
}

// Send All Queries to Receiving Server
foreach($SendQuery as $Query)
{
$dbResult = mysql_db_query($ToDatabaseName,$Query,$dbLink) or die(mysql_error() . " - Line 97 - $Query");
}

// Success!
return 1;

}

// ________
// |¯¯¯¯¯¯¯¯¯¯|
// | dbRename |
// |__________|
// ¯¯¯¯¯¯¯¯
function dbRename($Host,$DatabaseName,$NewDatabaseName)
{
// Connect to Database
$dbLink = dbConnect($Host);

// Get all Tables
$dbList = mysql_list_tables($DatabaseName,$dbLink) or die("No Database by that Name, or No Tables in Database!");

while ($dbRow = mysql_fetch_array($dbList))
{
$TableName = $dbRow[0];
$dbTables[] = $TableName;
}

// Construct Query to Send to Receiving Server

// Create Databases
$SendQuery[] = "CREATE DATABASE IF NOT EXISTS $NewDatabaseName;";

// Table Definitions
foreach($dbTables as $dbTableName)
{
$SendQuery[] = "DROP TABLE IF EXISTS $dbTableName;";
$SendQuery[] = ReturnCreateTable($DatabaseName, $dbTableName, $dbLink);
}

// Data Inserts
foreach($dbTables as $dbTableName)
{
$TableInserts = ReturnTableInserts($DatabaseName, $dbTableName, $dbLink);

if(count($TableInserts))
{
foreach($TableInserts as $InsertString)
{
$SendQuery[] = $InsertString;
}
}
}

// Drop Original Database
$SendQuery[] = "DROP DATABASE IF EXISTS $DatabaseName;";

// Send All Queries to Receiving Server
foreach($SendQuery as $Query)
{
if(substr($Query,0,15) == "CREATE DATABASE")
{
$dbResult = mysql_query($Query,$dbLink) or die(mysql_error() . " - Line 93 - $Query");
}
else
{
$dbResult = mysql_db_query($NewDatabaseName,$Query,$dbLink) or die(mysql_error() . " - Line 97 - $Query");
}
}

// Success!
return 1;

}

// ______
// |¯¯¯¯¯¯¯¯|
// | dbCopy |
// |________|
// ¯¯¯¯¯¯
function dbCopy($FromHost,$ToHost,$DatabaseRegExp,$TableRegExp)
{
// Connect to Databases
$dbLinkOne = dbConnect($FromHost);
$dbLinkTwo = dbConnect($ToHost);

// Get all Databases Matching Regular Expression
$dbList = mysql_list_dbs($dbLinkOne);

while ($dbRow = mysql_fetch_array($dbList))
{
$dbName = $dbRow["Database"];
if(preg_replace($DatabaseRegExp,"",$dbName) != $dbName)
{
$dbMatches[] = $dbName;
}
}

// Get all Tables Matching Regular Expression (Only in Matched DBs)
foreach($dbMatches as $dbName)
{
$dbList = mysql_list_tables($dbName,$dbLinkOne);

while ($dbRow = mysql_fetch_array($dbList))
{
$TableName = $dbRow[0];

if(preg_replace($TableRegExp,"",$TableName) != $TableName)
{
$dbToTransfer["$dbName"][] = $TableName;
}
}
}

// Construct Query to Send to Receiving Server

// Create Databases
foreach($dbToTransfer as $dbName => $dbTables)
{
$SendQuery["$dbName"][] = "CREATE DATABASE IF NOT EXISTS $dbName;";
}

// Table Definitions
foreach($dbToTransfer as $dbName => $dbTables)
{
foreach($dbTables as $dbTableName)
{
$SendQuery["$dbName"][] = "DROP TABLE IF EXISTS $dbTableName;";
$SendQuery["$dbName"][] = ReturnCreateTable($dbName, $dbTableName, $dbLinkOne);
}
}

// Data Inserts
foreach($dbToTransfer as $dbName => $dbTables)
{
foreach($dbTables as $dbTableName)
{
$TableInserts = ReturnTableInserts($dbName, $dbTableName, $dbLinkOne);

if(count($TableInserts))
{
foreach($TableInserts as $InsertString)
{
$SendQuery["$dbName"][] = $InsertString;
}
}

}
}

// Send All Queries to Receiving Server

foreach($SendQuery as $DataBase => $QueryArray)
{
foreach($QueryArray as $Query)
{
if(substr($Query,0,15) == "CREATE DATABASE")
{
$dbResult = mysql_query($Query,$dbLinkTwo) or die(mysql_error() . " - Line 184 - $Query");
}
else
{
$dbResult = mysql_db_query($DataBase,$Query,$dbLinkTwo) or die(mysql_error() . " - Line 188 - $Query");
}
}
}

// Success!
return 1;
}

// ____________
// |¯¯¯¯¯¯¯¯¯¯¯¯¯¯|
// | Subfunctions |
// |______________|
// ¯¯¯¯¯¯¯¯¯¯¯¯
function dbConnect($Host)
{
// Bring in db Array
global $db;

// Connect to Databases
if($SocketTest = fsockopen($db[$Host]["host"], $db[$Host]["port"], &$errno, &$errstr, $db[$Host]["time"]))
{
// Server is Responding!

// Close Socket
fclose($SocketTest);

// Try to Connect
if($dbLink = mysql_connect($db[$Host]["host"], $db[$Host]["user"], $db[$Host]["pass"]))
{
// Connection is now active.
return $dbLink;
}
else
{
// No connection.
die("Server $Host is responding, but refused your connection.");
}
}
else
{
// Server is Down!
die("Server $Host does not appear to be running mySQL on port " . $db[$Host]["port"] . " or is currently down.");
}
}

// The Following Function Is Property of the People
// Who Made phpMyAdmin (www.phpwizard.net)
// :: Slightly modified and Cleaned Up by JH

function ReturnCreateTable($Database, $Table, $dbLink)
{
// Start Definition
$Definition = "CREATE TABLE $Table (";

// Get Field Definitions
$dbQuery = "SHOW FIELDS FROM $Table";
$dbResult = mysql_db_query($Database, $dbQuery, $dbLink);

while($dbRow = mysql_fetch_array($dbResult))
{
$Definition .= "$dbRow[Field] $dbRow[Type]";

if(IsSet($dbRow["Default"]) && (!empty($dbRow["Default"]) || $dbRow["Default"] == "0"))
{
$Definition .= " DEFAULT '" . $dbRow["Default"] . "'";
}

if($dbRow["Null"] != "YES")
{
$Definition .= " NOT NULL";
}

if($dbRow["Extra"] != "")
{
$Definition .= " $dbRow[Extra]";
}

$Definition .= ",";
}

// Get Key (Primary, Unique, Etc...) Definitions
$dbQuery = "SHOW KEYS FROM $Table";
$dbResult = mysql_db_query($Database, $dbQuery, $dbLink);

while($dbRow = mysql_fetch_array($dbResult))
{
$KeyName=$dbRow['Key_name'];

if(($KeyName != "PRIMARY") && ($dbRow['Non_unique'] == 0))
{
$KeyName="UNIQUE|$KeyName";
}

if(!isset($index[$KeyName]))
{
$index[$KeyName] = array();
}

$index[$KeyName][] = $dbRow['Column_name'];
}

while(list($x, $columns) = @each($index))
{
$Definition .= ",";

if($x == "PRIMARY")
{
$Definition .= "PRIMARY KEY (" . implode($columns, ", ") . ")";
}
elseif (substr($x,0,6) == "UNIQUE")
{
$Definition .= " UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")";
}
else
{
$Definition .= " KEY $x (" . implode($columns, ", ") . ")";
}
}

// End Parentheses
$Definition .= ");";

// Get rid of repeated parentheses and misplaced commas
$Definition = str_replace(",,",",",$Definition);
$Definition = str_replace(",)",")",$Definition);

// Return Definition
return (stripslashes($Definition));
}

// The Following Function Is Property of the People
// Who Made phpMyAdmin (www.phpwizard.net)
// :: Slightly modified and Cleaned Up by JH

function ReturnTableInserts($Database, $Table, $dbLink)
{
$dbQuery = "SELECT * FROM $Table";
$dbResult = mysql_db_query($Database,$dbQuery,$dbLink);

$i = 0;

while($dbRow = mysql_fetch_row($dbResult))
{
set_time_limit(60);

$Table_list = "(";

for($j=0; $j<mysql_num_fields($dbResult);$j++)
{
$Table_list .= mysql_field_name($dbResult,$j).", ";
}

$Table_list = substr($Table_list,0,-2);
$Table_list .= ")";

if(isset($GLOBALS["showcolumns"]))
{
$InsertText = "INSERT INTO $Table $Table_list VALUES (";
}
else
{
$InsertText = "INSERT INTO $Table VALUES (";
}

for($j=0; $j<mysql_num_fields($dbResult);$j++)
{
if(!isset($dbRow[$j]))
{
$InsertText .= " NULL,";
}
elseif($dbRow[$j] != "")
{
$InsertText .= " '".addslashes($dbRow[$j])."',";
}
else
{
$InsertText .= " '',";
}
}

$InsertText = ereg_replace(",$", "", $InsertText);
$InsertText .= ");";

$AllInserts[] = trim($InsertText);

$i++;
}

return $AllInserts;
}


?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

 

developerWorks - FREE Tools!

IBM – Taking Web 2.0 to Work You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve. FREE! Go There Now!

NEW! Best Practices in Integrated Requirements Management Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right." FREE! Go There Now!

NEW! Discovering the value of WebSphere Process Server WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies. FREE! Go There Now!

NEW! Download a free trial of WebSphere Business Modeler Advanced V6.1.1 Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement. FREE! Go There Now!

NEW! Download DB2 9.5 for Linux, Unix, and Windows Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML. FREE! Go There Now!

NEW! Hello World: Learn how to install and use the Rational Asset Manager Eclipse client In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code. FREE! Go There Now!

NEW! Project and Portfolio Management Executive Resource Kit Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle. FREE! Go There Now!

NEW! Rational Talks to You: Manage RUP-based CMMI initiatives Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered! FREE! Go There Now!

NEW! The role of integrated requirements management in software delivery This paper is about the critical role that a discipline called integrated require­ments management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrat­ing, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way. FREE! Go There Now!

All FREE IBM® developerWorks Tools!