dqbr37828 2012-06-14 14:17
浏览 13
已采纳

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

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());
    }
}
  • 写回答

1条回答 默认 最新

  • dongping4273 2012-06-14 14:49
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?