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; 
    } 


?>