php - 错误的 PDO Select 语句

Question

我的长轮询不起作用:

server.php

<?php
require 'pdo.php';
set_time_limit(0);
while (true)
{

$requestedTimestamp = isset($_GET['timestamp']) ? (int)$_GET['timestamp'] : time();

  clearstatcache();

  $stmt = $pdo->prepare( "SELECT * FROM publication WHERE publication_time > :requestedTimestamp" );
  $stmt->bindParam( ':requestedTimestamp', $requestedTimestamp );
  $stmt->execute();
  $rows = $stmt->fetch(PDO::FETCH_ASSOC);

if (count($rows) > 0) {
$publication = $rows['publication'];
$timestamp = strtotime($rows['publication_time']);
$my = array('publication'=>$publication,'timestamp'=>$timestamp);
$myJSON = json_encode($my);
echo $myJSON;
break;


} else  {
    sleep(2);
    continue;
    }

}

?>

这个问题让我很头疼:独立于 $timestamp，我从 SELECT 得到的结果是相同的，以防万一，我最后一个 $timestamp = 1439056820 (08/08/2015 15:00:20);

publication_time = timestamp field;

Answer 1

你的代码对我来说看起来很好。您是否尝试过将参数绑定(bind)为 int ？

$stmt->bindParam(':requestedTimestamp', $requestedTimestamp, PDO::PARAM_INT);

也许通过将时间戳作为字符串传递，您会得到一些奇怪的行为？

编辑:因为我有更多时间，所以我实际测试了代码，并且神奇:

Warning | 1292 | Incorrect datetime value: '1440162495' for column 'timetest' at row 1

问题不在于 PDO，而在于传递数据的方式。直接的解决方案是将时间戳转换为有效日期，因此如下所示:

$requestedTimestamp = isset($_GET['timestamp']) ? (int)$_GET['timestamp'] : time(); here
//convert unixtime to iso date
$requestedTimestamp = date('c', $requestedTimestamp);
clearstatcache();

$stmt = $pdo->prepare( "SELECT * FROM publication WHERE publication_time > :requestedTimestamp" );
$stmt->bindParam( ':requestedTimestamp', $requestedTimestamp );
$stmt->execute();
$rows = $stmt->fetch(PDO::FETCH_ASSOC);

希望这已经足够了:)