Php Programming Code Examples Php > Database Related Code Examples DB Utils DB Utils <? // ################ // 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."); } } 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)); } 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; } ?>