在 PHP PDO 中如何获得“返回"PostgreSQL upsert 查询的子句值 [英] In PHP PDO how to get "RETURNING" clause values of PostgreSQL upsert query

问题描述

我用 postgreSQL 编写了这个 upsert 查询

I have this upsert query written in postgreSQL

$statement = 'INSERT INTO "CharactersUnlockToBuyLevels"
                ("CharacterId", "LevelId", "AmountToBuy", "EagleStatueId", "Location", 
                 "MapCoordinateTop", "MapCoordinateLeft")
              VALUES 
                (:CharacterId, :LevelId, :AmountToBuy, :EagleStatueId, :Location, 
                 :MapCoordinateTop, :MapCoordinateLeft)
              ON CONFLICT
                ("CharacterId")
              DO UPDATE 
                SET 
                  "LevelId" = EXCLUDED."LevelId",
                  "AmountToBuy" = EXCLUDED."AmountToBuy",
                  "EagleStatueId" = EXCLUDED."EagleStatueId",
                  "Location" = EXCLUDED."Location",
                  "MapCoordinateTop" = EXCLUDED."MapCoordinateTop",
                  "MapCoordinateLeft" = EXCLUDED."MapCoordinateLeft"                            
              RETURNING "CharacterUnlockToBuyLevelId"
             ';

查询工作正常，如果我在 PgAdmin 中运行它，我会按预期返回CharacterUnlockToBuyLevelId".但是，如果我准备它并使用 PHP 的 PDO 执行它，那么我似乎无法取回 RETURNING 值.

The query works fine, and if I run it in PgAdmin, I get the "CharacterUnlockToBuyLevelId" back as expected. However if I prepare it and execute it using PHP's PDO then I can't seem to get the RETURNING value back.

$stmt = $this->db->prepare($statement);
$returnedId = $stmt->execute(array(
     'CharacterId' => $characterId,
     'LevelId' => $unlockMethod['LevelId'],
     'AmountToBuy' => $unlockMethod['AmountToBuy'],
     'EagleStatueId' => $unlockMethod['EagleStatueId'],
     'Location' => $unlockMethod['Location'],
     'MapCoordinateTop' => $unlockMethod['MapCoordinateTop'],
     'MapCoordinateLeft' => $unlockMethod['MapCoordinateLeft'],
));

这只是返回 true，所以 $returnedId 实际上不会保存 id，而只会保存值 true.如何使用 PDO 的准备好的语句从 upsert 查询中获取返回值?

This just returns true, so $returnedId will not actually hold the id but just the value true. How can I get the RETURNING value from the upsert query back with PDO's prepared statements?

推荐答案

来自 http://php.net/manual/en/pdostatement.execute.php:

public bool PDOStatement::execute ([ array $input_parameters ] )

public bool PDOStatement::execute ([ array $input_parameters ] )

要获得返回的 val，请尝试 fetchAll

to get returned val, try fetchAll

$stmt = $this->db->prepare($statement);
$returnedId = $stmt->execute(array(
     'CharacterId' => $characterId,
     'LevelId' => $unlockMethod['LevelId'],
     'AmountToBuy' => $unlockMethod['AmountToBuy'],
     'EagleStatueId' => $unlockMethod['EagleStatueId'],
     'Location' => $unlockMethod['Location'],
     'MapCoordinateTop' => $unlockMethod['MapCoordinateTop'],
     'MapCoordinateLeft' => $unlockMethod['MapCoordinateLeft'],
));
$yourVal=$stmt->fetchAll();

这篇关于在 PHP PDO 中如何获得“返回"PostgreSQL upsert 查询的子句值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！