在log_history中显示用户列表以及用户登录次数

Question

我可以列出用户，但同时我想显示用户当天登录的时间

$stmt = $conn->prepare("select id,username,date from user_tbl");
$stmt->execute();
$row = $stmt-> fetchAll(PDO::FETCH_ASSOC);
$user_arr=array('data'=>$row);
echo json_encode($user_arr);

根据我想从log_history表中计算登录次数的用户id，我如何在PDO json编码中使用以下查询。

select count(id) as count1 from log_history where user_id=".$row['id']."

Answer 1

我想，你想要这样的输出：

------------------------------------------
id  |  username | date       | login count
------------------------------------------
1   |  user1    | 20-10-1019 | 20
------------------------------------------
2   |  user2    | 20-10-1019 | 25
------------------------------------------

你可以用Left Join

SELECT user_tbl.id, user_tbl.username, user_tbl.date, COUNT(log_history.id) AS login_count
FROM `user_tbl`
LEFT JOIN log_history
ON user_tbl.id = log_history.user_id
GROUP BY 1