使用PDO在2个不同数据库中重复行

Question

I am using PDO (mysql) to create 2 different database connections. I want to transfer a row of data from 1 table to another in a different database. This is not a duplication of the row, only certain rows are selected.

I am unable to get it to work, any ideas?

  private function moveCallToProduction() {
    try {
        $sql = "SELECT * FROM `calls` WHERE `id`=':id'";
        $query = $this->staging->prepare($sql);
        $query->execute($array);
        $results = $query->fetchAll(PDO::FETCH_ASSOC);
        try {
            $sql = "INSERT INTO `calls` (`id`,`sip_id`,`extension`,`caller_id`,`stage`,`status`,`survey_id`,`start`,`answer`,`hangup`,`end`) VALUES ('?','?','?','?','?','?','?','?','?','?','?')";
            $query = $this->production->prepare($sql);
            $query->execute($results);
        }
        catch(PDOException $e) {
            $this->informer("FATAL","There was a problem");
        }

    }
    catch(PDOException $e) {
        $this->informer("FATAL","We're unable to transport the call from the staging to production server. Error: ".$e->getMessage());
    }
}

Answer 1

fetchAll() returns an array containing all of the result set rows. You need to iterate over each row and insert it individually. For example:

...
$sql = "SELECT * FROM `calls` WHERE `id`=':id'";
$query = $this->staging->prepare($sql);
$query->execute($array);
$results = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
    try {
        $sql = "INSERT INTO `calls` (`id`,`sip_id`,`extension`,`caller_id`,`stage`,`status`,`survey_id`,`start`,`answer`,`hangup`,`end`) VALUES ('?','?','?','?','?','?','?','?','?','?','?')";
        $query = $this->production->prepare($sql);
        $query->execute($row);
    }
    catch(PDOException $e) {
        $this->informer("FATAL","There was a problem");
    }
}
...

You can also use the statement: while($result = $query->fetch(PDO::FETCH_ASSOC)) instead of fetchAll() to iterate over the results without having to store them in memory.

One thing to consider is what you want to do if an exception is encountered. Since you're inserting many times, you might consider using PDO::beginTransation() at the beginning, PDO::commit() if no exceptions occur, and PDO::rollBack() to cancel any changes if an exception does occur. This way you can be sure that everything transfers or nothing does.