CODEWITHSUNDEEP
phpmysqldatabasepdo
Kramhsiri
Kramhsiri

Reputation: 119

Moving rows from tables in one database to another database using PDO

First question, I have searched relentlessly for the answer but it seems people seem to ask "from one database to another" when they mean table. Anyways...

I have a database full of tables that record results for various game drawings. These tables have many different structures, with the fewest having 5 fields and the largest having around 20. I also have a database which contains tables of results from these games that are more than a year old. So in db1 I have game1, and in db2 I have game1_archive. What I am writing is a script to run once a day and move any entries from db1 tables that are more than a year old to the corresponding db2 table.

I have a list of tables in db1 to iterate through:

try {
    $db1_pdo = new PDO($db_game, $db_username, $db_password);
    $db2_pdo = new PDO($db_archive, $db_username, $db_password);
} catch (PDOException $exception) {
    echo 'Connection failed: ' . $exception->getMessage();
    die();
}

$cutoff_date = date('Y-m-d', time() - (366 * 24 * 60 * 60));

$tables = array();
$result = $db1_pdo->query("SHOW TABLES");
$tables = $result->fetchAll(PDO::FETCH_COLUMN, 0);

What would be the easiest to do is just an INSERT INTO _ FROM _ WHERE _ query like so:

foreach ($tables as $table) {
    $result = $db1_pdo->query("INSERT INTO archive.$table"."_archive * FROM $table WHERE drawing_date < '$cutoff_date'");
    $db1_pdo->query("DELETE FROM $table WHERE drawing_date < '$cutoff_date'");
}

But this doesn't work, I think because db1_pdo doesn't have access to both databases? The username and password to access both databases is the same. Is there an easy way to get INSERT INTO to work? If I have build statements by hand to INSERT, it will be a pain because each table has a different structure.

Upvotes: 1

Views: 1012

Answers (1)

dlegall
dlegall

Reputation: 412

I think using INSERT INTO ... SELECT ... method would work in your case 

$result = $db1_pdo->query("INSERT INTO archive.$table"."_archive SELECT * FROM $table WHERE drawing_date < '$cutoff_date'");

More info on this syntax is available there : https://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Upvotes: 1

Related Questions