计数结果不同的表单查询mysqlphp

Question

我有一个用户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"];

但似乎不起作用

Answer 1

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
)