php - pdo mysql 使用计数

Question

我以前可以使用

来做到这一点

      $userid = 1;
      mysql_query('
          SELECT sum(a) from (
              select COUNT(*) as a, userTable.userid, followTable.followingid, shareTable.postid
              FROM userTable
              INNER JOIN followTable
              INNER JOIN shareTable
                  ON followTable.followingid = userTable.userid
                  AND shareTable.userid = followTable.followingid
              WHERE followTable.userid = $userid
              UNION ALL
              SELECT count(*) as a, userTable.userid, Null as nullcolumn, shareTable.postid
              FROM userTable
              INNER JOIN shareTable ON shareTable.userid = userTable.userid
              WHERE shareTable.userid = $userid
          ) as sumofcount');

并使用 fetch 来获取值。然而对于 PDO 这似乎不起作用

这就是我使用 PDO 所做的事情

$streamquery  = '
    SELECT sum(a) from (
        select COUNT(*) as a, userTable.userid, followTable.followingid, shareTable.postid
        FROM userTable
        INNER JOIN followTable
        INNER JOIN shareTable
            ON followTable.followingid = userTable.userid
            AND shareTable.userid = followTable.followingid
        WHERE followTable.userid = :userid
        UNION ALL
        SELECT count(*) as heey, userTable.userid, Null as nullcolumn, shareTable.postid 
        FROM userTable
        INNER JOIN shareTable ON shareTable.userid = userTable.userid
        WHERE shareTable.userid = :userid
    ) as sumofcount';
$querystream = $conn->prepare($streamquery);
$querystream->execute(array("userid"=>$userid);

查询后我得到一个空集。

Answer 1

首先，您似乎缺少 followTable 连接的 ON 条件。我认为 MySQL 在这里做了一些奇怪的事情，但它可能值得你去研究一下。我觉得应该是

FROM userTable
INNER JOIN followTable
    ON followTable.followingid = userTable.userid
INNER JOIN shareTable
    ON shareTable.userid = followTable.followingid

但可能会错误地解释您的(查询)意图。

真正的问题是您无法在语句中重复使用命名占位符。来自 the manual

You cannot use a named parameter marker of the same name twice in a prepared statement

我建议您将 PDO 设置为在遇到错误时抛出异常。我假设您的 prepare 调用无提示地失败，因此请在创建连接后使用它...

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

现在在查询中使用两个不同的标记，例如

... WHERE followTable.userid = :userid1
// snip
... WHERE shareTable.userid = :userid2

并以相同的方式执行...

$querystream->execute(array(
    ':userid1' => $userid,
    ':userid2' => $userid));