在 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屋!