CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-2
Sundas Subhani
Sundas Subhani

Reputation: 23

Move values from one database to another in mysql and Codeigniter

I have a very large log table from which I want to move rows between specific dates, putting them into the same table structure on an other database. I don't want to copy everything, only specific date rows, so that the table on the main database stays small-ish. So I have to SELECT the data I want and only move (and delete) that.

Keep in mind that there is a lot of data, and I don't want to copy it all with a mysqldump. I want to create a php file or function and I will add a crone job which will run after few days and move all specific data to other database from main database.

Upvotes: 0

Views: 1759

Answers (1)

Richard
Richard

Reputation: 2815

Use something like this:

$sql = $db->query("SELECT * FROM old_table LIMIT 100");
while($row = $sql->fetch_assoc()){
    $values = "";
    foreach($row as $v)
        $values .= "'" .  $db->real_escape_string($v) . "', ";
    $values = rtrim($values, ", ");

    $db->query("INSERT INTO new_table (" . implode(",", array_keys($row) . ") VALUES ($values)");
    $db->query("DELETE FROM old_table WHERE `ID` = {$row->ID} LIMIT 1");
}

For two databases, use this instead:

$db = new MySQLi("hostname", "user", "password", "database");
$db2 = new MySQLi("hostname", "user", "password", "database");

$sql = $db->query("SELECT * FROM old_table LIMIT 100");
while($row = $sql->fetch_assoc()){
    $values = "";
    foreach($row as $v)
        $values .= "'" .  $db->real_escape_string($v) . "', ";
    $values = rtrim($values, ", ");

    $db2->query("INSERT INTO new_table (" . implode(",", array_keys($row) . ") VALUES ($values)");
    $db->query("DELETE FROM old_table WHERE `ID` = {$row->ID} LIMIT 1");
}

Upvotes: 1

Related Questions