Compare 2 Mysql Tables and Generate Create/Alter Statements

<?php
/**
 * Database Schema Comparison Tool
 *
 * This script is designed to compare two database tables and generate the required MySQL CREATE or ALTER scripts
 * to synchronize them. It's intended for developers and database administrators who need to keep database environments
 * aligned, such as development, staging, and production. The script automates the generation of SQL scripts necessary
 * for updating table structures, including adding missing tables, columns, and adjusting column definitions to match
 * between two specified databases.
 *
 * Features:
 * - Generates SQL scripts for synchronizing database schemas.
 * - Supports CREATE and ALTER TABLE commands based on comparison results.
 * - Automates the identification of discrepancies between two database tables.
 *
 * Usage:
 * Modify the `$connections` array with the database connection details and specify the databases to compare.
 * Execute the script in a PHP-supported command line or web server environment to get the SQL commands for synchronization.
 *
 * Note:
 * This script stops execution immediately to prevent unauthorized access. Remove the `header` and `die` function calls
 * to enable its functionality in a secure environment.
 *
 * Author: RapidMod.io
 * Website: https://rapidmod.io/
 *
 * Please ensure proper security measures are in place when using this script to prevent unauthorized database access.
 */
header("File not found",1,404); die(404);
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);

function executeQ($connections) {

    if(!empty($connections)){
        foreach ($connections as $conn){

        }
    }

    $conn = array(
        "name" => "",
        "type" => "",
        "user" => "",
        "password" => '',
        "host" => "",
        "port"=>""
    );

    $data = [];
    $dbNames = ["table1","table2"];
    /** @var PDO $pdo */
    $pdo = new PDO(
        "mysql:host={$conn['host']}; dbname={$conn['name']}",
        $conn['user'],
        $conn['password']
    );

    foreach ($dbNames as $dbName){
        $data["additional_tables"][$dbName] = [];
        $data["additional_columns"][$dbName] = [];
        $data["changes"][$dbName] = [];
        $data["schema"][$dbName] = [];
        $data[$dbName] = [ "tables" => [], "columns" => [] ];
        $params = [];
        $schema = fetchQ($pdo,"SHOW TABLES in {$dbName}",$params);
        if(!empty($schema)){
            foreach ($schema as $i => $x){
                $table = trim($x["Tables_in_{$dbName}"]);
                $data[$dbName]["tables"][$table] = $table;
                $data[$dbName]["columns"][$table] = [];
                $schemaA = fetchQ($pdo,"DESCRIBE `{$dbName}`.`{$table}` ",$params);
                if(!empty($schemaA)){
                    $formattedSchema = array();
                    foreach ($schemaA as $colInfo){
                        $data[$dbName]["columns"][$table][$colInfo["Field"]] = $colInfo["Type"];
                        $formattedSchema[$colInfo["Field"]] = $colInfo;
                    }
                    $data["schema"][$dbName][$table] = $formattedSchema;
                }
            }
        }
    }
   // die("<pre>".print_r($data,1));
    foreach ($data as $dbName => $info){
        if($dbName === $dbNames[0]){$check = $dbNames[1];}
        else{$check = $dbNames[0];}
        $tables = $data[$dbName]["tables"];
        if(!$tables){
            continue;
            die(":nothing #1125");
        }
        $dbChanges[$dbName] = [];
        foreach ($tables as $table){
            if(!isset($data[$check]["tables"][trim($table)])){
                $data["additional_tables"][$dbName][] = trim($table);
                $x = fetchQ($pdo,"show create table {$dbName}.{$table}",[]);
                if(!empty($x[0]["Create Table"])){
                    $data["changes"][$dbName][]  = $x[0]["Create Table"].";";
                }else{
                    die(":nothing #1124");
                }
            }
            if($data[$dbName]["columns"][$table]){
                foreach ($data[$dbName]["columns"][$table] as $col => $type){
                    if(isset($data[$check]["columns"][$table])){
                        if(!isset($data[$check]["columns"][$table][$col])){
                            $data["additional_columns"][$dbName][$table][] = $col;
                            $data["changes"][$dbName][] = getAlterTableStatement($table,$col,$data["schema"][$dbName][$table][$col]);
                        }
                    }
                }
            }
        }
    }
    $changes = $data["changes"]["table1"];
    if(!empty($changes)){
        $changes = implode(PHP_EOL.PHP_EOL,$changes);
        echo str_replace(PHP_EOL,PHP_EOL."<br>","SET FOREIGN_KEY_CHECKS=0;".PHP_EOL.PHP_EOL.$changes.PHP_EOL.PHP_EOL."SET FOREIGN_KEY_CHECKS=1;");
        die("");
    }
    die(":nothing #1123");
}

/**
 *
 * Name getAlterTableStatement
 *
 * @param $table
 * @param $column
 * @param $info
 *
 * @return string
 *
 * @author RapidMod.io
 * @author 813.330.0522
 * @created 1/25/20
 */
 function getAlterTableStatement($table,$column,$info){
    $alter = "";
    $stmnt = "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$info["Type"]}";
    if($info["Key"]){
        switch (strtolower($info["Key"])){
            case "pri" : return $stmnt. " PRIMARY KEY AUTO_INCREMENT ;"; break;
            case "mul" : $alter = PHP_EOL."ALTER TABLE `$table` ADD INDEX {$column} ({$column}); "; break;
            case "uni" : $alter = PHP_EOL."ALTER TABLE `$table` ADD UNIQUE INDEX {$column} ({$column}); ";break;
            default : break;
        }
    }


    if($info["Null"] === "YES"){
        $stmnt .= " NULL";
    }else{
        $stmnt .= " NOT NULL";
    }
    if($info["Default"]){
        if(in_array(strtolower($info["Type"]),["datetime","timestamp"]) && $info["Default"] !== "CURRENT_TIMESTAMP"){
            $var = "0000-00-00 00:00:00";
        }else if(is_numeric($info["Default"]) || $info["Default"] === "CURRENT_TIMESTAMP"){
            $var = $info["Default"];
        }else{
            $var = "'{$info["Default"]}'";
        }
        $stmnt .= " DEFAULT {$var}";
    }

    return "{$stmnt};{$alter}";
}

/**
 *
 * Name fetchQ
 *
 * @param PDO $pdo
 * @param string $query
 * @param array $params
 *
 * @return array
 *
 * @author RapidMod.io
 * @author 813.330.0522
 * @created 1/24/20
 */
function fetchQ(PDO $pdo, string $query,array $params = []){
        $stmnt = $pdo->prepare($query,[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true]);

        $x = array();

        if(isset($params[0]) && is_array($params[0])){$params = $params[0];}
        if(empty($params)){$params=null;}
        $success = $stmnt->execute($params);
        //\Rapidmod\Dev::printVar($stmnt);
        if($success){
            while($row = $stmnt->fetch(PDO::FETCH_ASSOC)){
                $x[] = $row;
            }
        }
        return $x;
    }
    executeQ([]);
?>