我曾经能够使用
$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);
查询后,我得到一个空集。
好的,首先,您似乎缺少联接的ON
条件followTable
。我认为MySQL只是在这里做了一些奇怪的事情,但是您值得一试。我认为应该
FROM userTable
INNER JOIN followTable
ON followTable.followingid = userTable.userid
INNER JOIN shareTable
ON shareTable.userid = followTable.followingid
但可能会错误地解释您(查询)的意图。
真正的问题是您不能在语句中重复使用命名的占位符。从手册
您不能在准备好的语句中两次使用相同名称的命名参数标记
我建议您将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));
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句