douji9734 2013-11-10 15:02
浏览 137

PDO - 带有问号参数的批量UPDATE语句,提示?

I would like to update a MySQL table containing up-to-date currency symbol & value pairs from a given exchange rates service once every 24 hours.

Right now, I do something like this:

foreach ($currencies->rates as $currency => $rate) {
    $connection->addQuery("update/{$currency}", "UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?;", $rate, $currency);
}

$connection->output();

I get the $currencies object out from a JSON-encoded response, $connection is a reference to my database connection class.

If I want to create a SQL query group (a class I created to perform bulk commands), the syntax would be like this:

if (!empty($this->attributes["queries"])) {
    $queries = implode(" ", array_map(function ($input) { return (mb_strpos($input->attributes["query"], ";") !== false ? $input->attributes["query"] : "{$input->attributes["query"]};"); }, $this->attributes["queries"]));
    $parameters = [];

    foreach ($this->attributes["queries"] as $query) {
        if (!empty($query->attributes["parameters"])) {
            $parameters = array_merge($parameters, $query->attributes["parameters"]);
        }
    }
}

This way, if I have the following set of queries (with their final values after the query):

UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; [1.7, AED]
UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; [2.1, BYR]
UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; [0.7, EUR]
UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; [1.1, GBP]

The final string would be like this:

UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; UPDATE `i18n/currencies` SET `rate` = ? WHERE `code` = ?; [1.7, AED, 2.1, BYR, 0.7, EUR, 1.1, GBP]

The query is prepared and executed like this:

if ($output = $this->attributes["references"]["parent"]->attributes["link"]->prepare($queries, [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY])) {
    $result = ($output->execute((!empty($parameters) ? $parameters : null)) ? true : false);
}

But it's not working. I've tried different modifications to my code, but none of them seems to provide me with the solution I'm looking for.

Is it really possible to do BULK updates with PDO passing the parameters like this? Or is it another way I haven't discovered yet? Maybe it's not doable at all and I'm just being stubborn. I could rewrite this update script with single SQL queries, as my connection class will fire them up, from the first to the last one.

Can you give me a hint?

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 stata安慰剂检验作图但是真实值不出现在图上
    • ¥15 c程序不知道为什么得不到结果
    • ¥40 复杂的限制性的商函数处理
    • ¥15 程序不包含适用于入口点的静态Main方法
    • ¥15 素材场景中光线烘焙后灯光失效
    • ¥15 请教一下各位,为什么我这个没有实现模拟点击
    • ¥15 执行 virtuoso 命令后,界面没有,cadence 启动不起来
    • ¥50 comfyui下连接animatediff节点生成视频质量非常差的原因
    • ¥20 有关区间dp的问题求解
    • ¥15 多电路系统共用电源的串扰问题