我有id_user=5在三个不同的表中都有帖子的用户
questions (id_post | id_user ...) (7 posts)
marketplace (id_post | id_user ...) (9 posts)
jobs (id_post | id_user ...) (3 posts)
所以我想用一个查询来统计他在每个表中的帖子数
这是我到目前为止所拥有的...但是似乎没有用
(SELECT
COUNT(p.*) as count_questions
FROM $table_questions as p
WHERE p.id_user = :id_user1
)
UNION
(
SELECT
COUNT(m.*) as count_marketplace
FROM $table_marketplace as m
WHERE m.id_user = :id_user2
)
UNION
(SELECT
COUNT(e.*) as count_jobs
FROM $table_jobs as e
WHERE e.id_user = :id_user3
)
怎么了？
我期待在php这样检索
$count_questions = $result["count_questions"];
但它似乎不起作用
SELECT COUNT(id_user) as counter
FROM $table_questions WHERE id_user = :id_user1
UNION
SELECT COUNT(id_user) as counter
FROM $table_marketplace WHERE id_user = :id_user2
UNION
SELECT COUNT(id_user) as counter
FROM $table_jobs WHERE id_user = :id_user3
将分别返回3行和计数，您必须为所有计数赋予相同的别名。
如果您想确定每个结果行，请专门添加另一列作为标识符
SELECT 'Questions' as what, COUNT(id_user) as counter
FROM $table_questions WHERE id_user = :id_user1
UNION
SELECT 'Marketplace' as what, COUNT(id_user) as counter
FROM $table_marketplace WHERE id_user = :id_user2
UNION
SELECT 'Jobs' as what, COUNT(id_user) as counter
FROM $table_jobs WHERE id_user = :id_user3
会给像
what count
Questions 7
Marketplace 9
Jobs 3
您可以将它们检索为
$array = $stmt->fetch_all();
你会得到一个像
Array
(
[Questions] => 7
[Marketplace] => 9
[Jobs] => 3
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句