使用MySQL的MySQL查询

I am creating a social network in which you can follow someone or be friends with them. The data you are able to see depends on the type of relationship you have with a user. Right now I have two tables to work with. Posts and Relationships.

我正在创建一个社交网络，您可以在其中关注某人或与他们成为朋友。您能够看到的数据取决于您与用户的关系类型。现在我有两个表可以使用。帖子和关系。

Posts:

帖子：

| user_id | post_id | story |
-----------------------------
|  1      |  1      | text. |
-----------------------------

Relationships:

关系：

| rel_id  | user_1  | user_2| status |
--------------------------------------
|  1      |  1      |   2   |    3   |
--------------------------------------

I also have a users table but I don't think that is important here. SO, basically I want to select all of the posts from users that I am friends with or following. User_2 is always the recipient of the relationship. The numbers 1, 2 and 3 represent the "status" of the relationship. 1 being you are following the recipient, 3 being you are friends, and 4 being you are following the recipient (the only difference is that you also have a pending friend request). I set up a SELECT QUERY but it is not working right.

我也有一个用户表，但我认为这不重要。所以，基本上我想要选择我与朋友关注或关注的用户的所有帖子。 User_2始终是关系的接收者。数字1,2和3代表关系的“状态”。 1是你跟随收件人，3是你是朋友，4是你跟踪收件人（唯一的区别是你还有待处理的朋友请求）。我设置了一个SELECT QUERY，但它无法正常工作。

$query=" SELECT * FROM posts LEFT JOIN relationships ON (posts.user_id=    
relationships.user_2 AND relationships.user_1 = $user_id AND relationships.status = 4 
OR 3 OR 1)";

It selects all the posts ever, twice. Why is that? I want it to only select the posts where the user posting the post is in a relationship with me, with a status of 1, 3, or 4. What might I be doing wrong? What are other ways to do this?

它选择所有帖子，两次。这是为什么？我希望它只选择发布帖子的用户与我有关系的帖子，状态为1,3或4.我可能做错了什么？有什么其他方法可以做到这一点？

Thanks in advance!

提前致谢！

2 个解决方案

#1

That's not how OR works. Perhaps you should try IN.

这不是OR的工作方式。也许你应该尝试IN。

... relationships.status IN (4, 3, 1) ...

As for the duplication, use DISTINCT.

至于复制，请使用DISTINCT。

#2

Your OR in the WHERE clause condition is not in the correct syntax. try something like this:

WHERE子句条件中的OR使用的语法不正确。尝试这样的事情：

SELECT  * 
FROM    posts a 
            LEFT JOIN relationships b
                ON a.user_id = b.user_2 
WHERE   b.user_1 = $user_id AND 
        b.status IN (1,3,4)

I suggest that you use PDO or MYSQLi extensions.

我建议你使用PDO或MYSQLi扩展。

In PDO, it could look like this:

在PDO中，它可能如下所示：

<?php
$stmt = $dbh->prepare("SELECT   * 
                      FROM  posts a 
                           LEFT JOIN relationships b
                                ON a.user_id = b.user_2 
                     WHERE  b.user_1 = ? AND 
                            b.status IN (1,3,4)");

$stmt->bindParam(1, $user_id);
$stmt->execute();

?>

Remember to always filter your inputs especially it is used querying your database.

请记住始终过滤您的输入，尤其是用于查询数据库。

#1