This is a little complicated but I’ll try to keep it simple and concise.
I’m trying to have one database get updated with the information from another database every so often by just dropping all the tables and re-inserting all the information.
There are reasons why this is the best choice that don’t matter for the problem I’m having.
Somehow, my script keeps trying to create a table that it already created, and I’m not sure why.
First, here is the actual script:
<?php
try
{ // Wrapped in a try->Catch for error reporting.
$_DB1_Gateway_PDO_Conn = new PDO
( // Create connection to DB1_Gateway MySQL Database.
"mysql:host=$_DB1_Gateway_Server_Name;dbname=$_DB1_Gateway_DB_Name",
$_DB1_Gateway_Creds['username'],
$_DB1_Gateway_Creds['password']
); // Uses parsed ini file for credentials for security.
$_DB1_Gateway_PDO_Conn->setAttribute
( // Prepare Attribute for Error Mode Exception Handling.
PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION // Sets Error Mode to Exception
);
$_DB1_Gateway_PDO_Conn->setAttribute
( // Prepare Attribute for Error Mode Exception Handling.
PDO::ATTR_EMULATE_PREPARES, false // Disables PDO Emulation for security.
);
$_Select_Tables_SQL =
("
SHOW TABLES
");
// Connects to the MySQL Database and runs the Select Tables query,
// assigning the data to the variable.
$_Pulled_Tables = $_DB1_Gateway_PDO_Conn->prepare($_Select_Tables_SQL);
$_Pulled_Tables->execute();
// Sets the fetch mode to Associative Array so it doesn't return a binary.
$_Pulled_Tables->setFetchMode(PDO::FETCH_ASSOC);
// Fetches the entire associative array and assigns it to a variable.
$_Fetched_Tables_Array = $_Pulled_Tables->fetchAll();
if (count(array_filter($_Fetched_Tables_Array)) === 0)
{ // If the array is empty, exits and nulls the connection.
$_DB1_Gateway_PDO_Conn = null;
exit(0); // Exits with 0 error code showing no error has occurred.
}
$_Table_Names = []; // Needed to be used in foreach loop below.
foreach($_Fetched_Tables_Array as $_Table_Name)
{
$_Table_Names[$_Table_Name["Tables_in_gateway"]] = $_Table_Name["Tables_in_gateway"];
}
$_Fetched_Table_Rows_Array = []; // Needed to be used in foreach loop below.
$_HowTo_Create_Tables_Array = [];
foreach ($_Table_Names as $_Table_Name)
{ // Produces the multi-select query for getting all table contents.
// Gets an array of Create Table queries for reproducing tables later.
$$_Table_Name = // Sets variable to table name.
("
SELECT *
FROM " . $_Table_Name . ";
"); // Creates sql variable for each table called the table.
$_Pulled_Table_Rows = $_DB1_Gateway_PDO_Conn->prepare($$_Table_Name);
$_Pulled_Table_Rows->execute();
$_Fetched_Table_Rows_Array[$_Table_Name] = $_Pulled_Table_Rows->fetchAll(PDO::FETCH_ASSOC);
// Puts array of records from table into another array
// $key is table name.
${'_HowTo_Create_Tables_SQL_'.$_Table_Name} =
("
SHOW CREATE TABLE " . $_Table_Name . ";
");
$_Pulled_Create_Table = $_DB1_Gateway_PDO_Conn->prepare(${'_HowTo_Create_Tables_SQL_'.$_Table_Name});
$_Pulled_Create_Table->execute();
$_HowTo_Create_Tables_RawArray[$_Table_Name] = $_Pulled_Create_Table->fetchAll(PDO::FETCH_ASSOC);
$_HowTo_Create_Tables_Array[$_Table_Name] = $_HowTo_Create_Tables_RawArray[$_Table_Name][0]['Create Table'];
}
}
catch(PDOException $_e)
{
$_DB1_Gateway_PDO_Conn = null;
// echo $_e->getMessage();
// mail_errors($_e->getMessage());
}
// Frees the result from the MySQL Database's temporary Result table.
$_DB1_Gateway_PDO_Conn = null;
try
{ // Wrapped in a try->catch for error reporting.
$_DB2_Gateway_PDO_Conn = new PDO
( // Create connection to DB2_Gateway MySQL Database.
"mysql:host=$_DB2_Gateway_Server_Name;dbname=$_DB2_Gateway_DB_Name",
$_DB2_Gateway_Creds['username'],
$_DB2_Gateway_Creds['password']
); // Uses parsed ini file for credentials for security.
$_DB2_Gateway_PDO_Conn->setAttribute
( // Prepare Attribute for Error Mode Exception Handling.
PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION // Sets Error Mode to Exception
);
$_DB2_Gateway_PDO_Conn->setAttribute
( // Prepare Attribute for Error Mode Exception Handling.
PDO::ATTR_EMULATE_PREPARES, false // Disables PDO Emulation for security.
);
$_DB2_Gateway_PDO_Conn->beginTransaction();
foreach($_Fetched_Table_Rows_Array as $_Table_Name_Key => $_Table_Rows)
{
${'_Drop_Table_SQL_'.$_Table_Name_Key} = ("
DROP TABLE IF EXISTS " . $_Table_Name_Key . ";
");
${'_Drop_Table_'.$_Table_Name_Key} =
$_DB2_Gateway_PDO_Conn->prepare(
${'_Drop_Table_SQL_'.$_Table_Name_Key});
${'_Create_Table_SQL_'.$_Table_Name_Key} = $_HowTo_Create_Tables_Array[$_Table_Name];
${'_Create_Table_'.$_Table_Name_Key} =
$_DB2_Gateway_PDO_Conn->prepare(
${'_Create_Table_SQL_'.$_Table_Name_Key});
${'_Create_Table_'.$_Table_Name_Key}->execute();
$_DB2_Gateway_PDO_Conn->commit();
}
catch(PDOException $_e)
{ // If error occurs, Exception causes automatic transaction rollback...
$_DB2_Gateway_PDO_Conn = null; // Null connection to the database.
// mail_errors($_e->getMessage()); // Mail errors accordingly.
}
$_DB2_Gateway_PDO_Conn = null;
?>
Now, the error I’m getting is that “user_level table already exists”. When I check the database, only that table exists. Dropping it through PHPMyAdmin’s UI doesn’t fix the issue.
Outputting the various variables and arrays to check if it is listed twice or if there’s some reason it would be recursive in the foreach has not gotten me anywhere because it is all normal AFAICT.
I just… have no idea what’s causing this issue. I’ve check syntax errors and the information being redundant but I just don’t know.
I need to re-evaluate the variable names so they’re less redundant and mistakes like this are less likely.
Running it now drops then creates the tables correctly.
