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! Addressing software-as-a-service challenges using Tivoli security and WebSphere solutions Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base. FREE! Go There Now!

NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo. FREE! Go There Now!

NEW! Don't wait! Try the Rational Application Developer (RAD) v7.5 open beta code today Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems. FREE! Go There Now!

NEW! Download a free trial of Lotus Quickr 8.0 Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications. FREE! Go There Now!

NEW! Info 2.0: Harnessing the power of Web 2.0 and Enterprise Mashups Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started. FREE! Go There Now!

NEW! Innovate don't duplicate! Asset reuse strategies for success Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository. FREE! Go There Now!

NEW! Webcast: Striking the right balance between manual and automated testing Join this webcast to learn how IBM Rational's Functional Testing solution enables you to implement automation your way, at your pace, with your existing staff. In this webcast, you’ll learn how you can eliminate redundancy of manual test scripts, reduce errors, and increase test coverage through test automation. After this presentation you will understand how IBM Rational Functional Testing solution can streamline your manual testing and make test automation easily attainable. FREE! Go There Now!

NEW! Webcast: 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!

Refresh! IBM Rational Systems Development Solution eKit With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential. FREE! Go There Now!

All FREE IBM® developerWorks Tools!