插入之前如何检查两个条件? [英] How can I check two conditions before inserting?

问题描述

我有三个表:

// Posts
+----+----------+---------------+-----------+
| id |  title   |    content    | id_author |
+----+----------+---------------+-----------+
| 1  | title1   | content1      | 1234      |
| 2  | title2   | content2      | 5678      |
+----+----------+---------------+-----------+

// Users
+----+--------+--------+
| id |  name  | active |
+----+--------+--------+
| 1  | jack   | 1      |
| 2  | peter  | 0      |
| 3  | John   | 1      |
+----+--------+--------+

// Votes
+----+---------+---------+
| id | id_post | id_user |
+----+---------+---------+
| 1  | 32      | 1234    |
| 2  | 634     | 5678    |
| 3  | 352     | 1234    |
+----+---------+---------+

现在我需要检查两个条件，然后才能在Votes表中插入新的投票:

Now I need to check two conditions before inserting a new vote into Votes table:

1. 作者的身份证和我通过的身份证是相同的? Posts.id_user = :author (我知道我可以通过FK做到这一点，但我不想要)
2. 当前用户的帐户是否处于活动状态? Users.active = 1

1. The id of author and what I have passed are the same? Posts.id_user = :author (I know I can do that by a FK, but I don't want)
2. The account of current user is active? Users.active = 1

这也是我的查询:

INSERT INTO Votes (id_post,id_user)
SELECT ?,?
FROM Posts p
WHERE p.id_user = :author limit 1;

如何在查询中添加第二个条件Users.active = 1?

How can I add second condition Users.active = 1 to my query?

编辑:实际上，我试图让没有投票权限的人((active = 0))不能投票.例如，如果SO禁止男人，那么我将无法投票发表帖子，因为我(当前用户)被禁止了.因此，我很确定在查询中应使用$_SESSION['id'].

Actually I'm trying to don't let people be able to vote who are inactive (active = 0). For example if SO bans men, then I cannot vote to post anymore, because I (current user) am banned. So I'm pretty sure $_SESSION['id'] should be used in the query.

推荐答案

INSERT INTO Votes (id_post,id_user)
SELECT p.id,u.id
FROM Posts p, Users u
WHERE p.id_user = :author
AND u.id = :user
AND u.active = 1 limit 1;

然后将参数user设置为等于当前用户ID.

then you set parameter user equal to the current user id.

编辑:我想表Votes中的id_user必须是选民的ID，而不是帖子的作者(对吗?)，因此我修复了消除JOIN的查询.

EDIT: I suppose id_user in table Votes must be the voter's id, not the author of the post (correct?), so I fixed the query eliminating the JOIN.

这篇关于插入之前如何检查两个条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！