我正在使用PDO(mysql)来创建2个不同的数据库连接。我想将一行数据从一个表传输到另一个数据库中的另一个表。这不是行的重复,仅选择某些行。
我无法让它工作,有什么想法吗?
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());
}
}
fetchAll()
返回一个包含所有结果集行的数组。您需要遍历每一行并单独插入。例如:
...
$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");
}
}
...
您还可以使用语句:while($result = $query->fetch(PDO::FETCH_ASSOC))
而不是fetchAll()
来迭代结果,而无需将它们存储在内存中。
要考虑的一件事是,如果遇到异常,您要执行的操作。由于要插入多次,因此可以考虑在开始时使用PDO::beginTransation()
,如果没有发生异常,则PDO::commit()
,如果确实发生异常,则PDO::rollBack()
取消任何更改。这样,您可以确保所有内容都转移或什么都不转移。