dongmeiwei0226
2014-10-17 13:02
浏览 145
已采纳

使用PHP / PDO进行PostgreSQL UPDATE的RETURNING子句

I execute a simple postgresql update as:

UPDATE data SET Gender=lower(tmp.champs2) 
FROM tmp WHERE data.email=tmp.champs1 
AND (data.Gender IS NULL OR data.Gender='') 
AND tmp.champs2 IS NOT NULL 
AND tmp.champs2!='' 
RETURNING Gender

The PostgreSQL documentation said we can get the number of rows affected with the RETURNING clause.

But how can I get this result with PHP, PDO?

I try something like:

echo $requete = "UPDATE data 
    SET ".$value."=lower(tmp.champs".$num.") 
    FROM tmp 
    WHERE data.email=tmp.champs".$email." 
    AND (data.".$value." IS NULL OR data.".$value."='') 
    AND tmp.champs".$num." IS NOT NULL 
    AND tmp.champs".$num."!='' 
    RETURNING ".$value;

    $db->exec($requete);

    $db->fetch(PDO::FETCH_NUM);

    echo $row[0];

The correct number of rows updated are not returned.

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • douluanji8752 2014-10-18 14:14
    最佳回答

    According to PDO's doc:

    PDO::exec() executes an SQL statement in a single function call, returning the number of rows affected by the statement.

    Your current code $db->exec($requete); ignores the return value, which is the mistake. This should be for instance:

    $affected_rows = $db->exec($requete);
    

    A RETURNING clause is not necessary to get the number of rows affected (besides, it doesn't exist in some backends that PDO supports). The purpose of RETURNING is to retrieve rows in addition to updating, all in the same query.

    In the case where the RETURNING clause is necessary and results must be fetched, PDO::query should be used instead of PDO:exec(). The PHP code to fetch the results is the same as if the query was a SELECT.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题