计算左连接表中的行数

发布于 2025-01-03 21:08:51 字数 1157 浏览 3 评论 0原文

我有两个表：用户和日志。目前我的查询看起来像这样。

        $stmt = $this->db->prepare("SELECT u.id, u.email, u.salt, u.pass, u.approved, u.ban, u2.status  FROM `users` AS u LEFT OUTER JOIN `log` AS u2 ON u2.user_id = u.id WHERE u.email = ?") or die($this->db->error);
        $stmt->bind_param("s", $_POST['email']) or die($stmt->error);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows == 0) {
            die($this->ajax->respond(7));
        }
        $data = array();
        $stmt->bind_result($data['id'], $data['email'], $data['salt'], $data['pass'], $data['approved'], $data['ban'], $data['status']) or die($stmt->error);
        $stmt->fetch() or die($stmt->error);
        $stmt->close() or die($stmt->error);

日志表的状态列 - 指示用户是否已登录。我想要做的是检查 users 表中是否存在电子邮件，并计算 log 表中 status=1 的行数。是否可以通过一个且唯一的查询来实现这一点？

换句话说：

这是日志表

在此处输入图像描述

查看 status = 1 的行。这表明用户1 当前登录。为了防止从不同的浏览器再次登录，我想首先检查用户表中的电子邮件（基本登录过程），然后检查用户是否未登录（通过计算日志表中 status = 1 的行数）

原文

I have 2 tables: users and log. Currently my query looks like that.

        $stmt = $this->db->prepare("SELECT u.id, u.email, u.salt, u.pass, u.approved, u.ban, u2.status  FROM `users` AS u LEFT OUTER JOIN `log` AS u2 ON u2.user_id = u.id WHERE u.email = ?") or die($this->db->error);
        $stmt->bind_param("s", $_POST['email']) or die($stmt->error);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows == 0) {
            die($this->ajax->respond(7));
        }
        $data = array();
        $stmt->bind_result($data['id'], $data['email'], $data['salt'], $data['pass'], $data['approved'], $data['ban'], $data['status']) or die($stmt->error);
        $stmt->fetch() or die($stmt->error);
        $stmt->close() or die($stmt->error);

Status column of log table - is indicates if user already signed in or not. What I want to do is to check if email exists in users table and to count rows of log table where status=1. Is that possible with one and only query?

In other words:

Here is log table

enter image description here

Take a look at rows where status = 1. This indicates that user 1 currently signed in. To prevent second signin from different browser I want to at first check for email in users table (basic signin procedure) and at that moment check if user not signed in (by counting rows where status = 1 in log table)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

李白 2025-01-10 21:08:51

只需将查询更改为此 -

SELECT u.id, u.email, u.salt, u.pass, u.approved, 
u.ban, SUM(u2.status) AS status FROM `users` AS u 
LEFT JOIN `log` AS u2 ON u2.user_id = u.id 
WHERE u.email = ?
GROUP BY u.id

还有许多其他方法可以执行此操作。

Just change the query to this -

SELECT u.id, u.email, u.salt, u.pass, u.approved, 
u.ban, SUM(u2.status) AS status FROM `users` AS u 
LEFT JOIN `log` AS u2 ON u2.user_id = u.id 
WHERE u.email = ?
GROUP BY u.id

There are a number of other ways to do this as well.

回复收藏 0 原文

~没有更多了~