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

blog comments powered by Disqus