Duplicate rows in 2 different databases using PDO

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());
    }
}

Answers


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.


Need Your Help

CodeLite - Console runs but code is not working

c++ compiler-errors console codelite

I just installed CodeLite 6.0 in two of my PC's, however in both of these computers I get the same issue: I am able to build and run the project, but when the console starts running all I see is:

Combine background-colour CSS3 with a image semitransparent

html css image css3

How can i combine a background-image with a degraded background-color in CSS3?