一切都很好,除非我必须检查更新现有的记录,如果数据库得分是新的分数。当我把分数加在哪里

try {
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare('INSERT INTO UserTable (fb_id, level, score, star, timestamp) ' .
    'VALUES(:fb_id, :level, :score, :star, :timestamp)' .
    'ON DUPLICATE KEY UPDATE score= :score, timestamp= :timestamp WHERE score < :score');
    //WHERE score < $score
    $stmt->bindParam(':fb_id', $fb_id);
    $stmt->bindParam(':level', $level);
    $stmt->bindParam(':score', $score);
    $stmt->bindParam(':star', $star);
    $stmt->bindParam(':timestamp', $current_time);
    $stmt->execute();
    echo "New record created successfully";
}
catch(PDOException $e)
      {
        echo $sql . "<br>" . $e->getMessage();
      }
}

最佳答案

这就是你想要的吗?

INSERT INTO UserTable (fb_id, level, score, star, timestamp) ' .
     VALUES(:fb_id, :level, :score, :star, :timestamp)' .
     ON DUPLICATE KEY UPDATE
         score = GREATEST(:score, score),
         timestamp = (CASE WHEN :score > score THEN :timestamp ELSE timestamp END);

关于php - PDO插入重复 key ,但条件不起作用,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53242745/

10-11 22:29
查看更多