php - 选择返回空数组的sql变量
问题描述
我正在尝试在MySQLSET
中使用3 个变量并获得其中两个的总和。
前两个变量@cFollow
和应该@cComment
分别返回一个整数值(返回多少行的计数);第三个是这两个整数的和。
这是我的SQL:
SET @cFollow = (SELECT COUNT(*) FROM followers WHERE unix > :unix AND following = :user);
SET @cComment = (SELECT COUNT(*) FROM comments WHERE comment_unix > :unix AND comment_track IN (SELECT upload_id FROM uploads WHERE upload_artist = :user));
SET @total = @cFollow + @cComment;
SELECT @total;
当我在 PHPMyAdmin 上测试它时,它返回了正确的值并且工作得很好。但是,当我在PHP中对其进行测试时,它返回了一个空数组。
这是我的PHP:
$holdPoint = (int)Input::get("hold_point");
$_SQL = "
SET @cFollow = (SELECT COUNT(*) FROM followers WHERE unix > :unix AND following = :user);
SET @cComment = (SELECT COUNT(*) FROM comments WHERE comment_unix > :unix AND comment_track IN (SELECT upload_id FROM uploads WHERE upload_artist = :user));
SET @total = @cFollow + @cComment;
SELECT @total;";
$_PARAMS = [":unix" => $holdPoint, ":user" => $user_id];
$check = DB::getInstance()->queryPro($_SQL, $_PARAMS);
var_dump($check);
这是结果var_dump
:
array(0){} // not very impressive...
// should be something like int(1) instead
我整晚都在搜索,学习如何从MySQL查询中返回PHP中的变量,这就是我所得到的。
感谢所有帮助,
干杯。
解决方案
这个答案并不是真正的答案,而是更多的评论。
另请注意,您的查询
SET @cFollow = (SELECT COUNT(*) FROM followers WHERE unix > :unix AND following = :user);
SET @cComment = (SELECT COUNT(*) FROM comments WHERE comment_unix > :unix AND comment_track IN (SELECT upload_id FROM uploads WHERE upload_artist = :user));
SET @total = @cFollow + @cComment;
SELECT @total;
最有可能被重写为一个查询
SELECT
SUM(alias.c) AS total
FROM (
SELECT COUNT(*) AS c FROM followers WHERE unix > :unix AND following = :user
UNION ALL
SELECT COUNT(*) AS c FROM comments WHERE comment_unix > :unix AND comment_track IN (SELECT upload_id FROM uploads WHERE upload_artist = :user)
) AS alias