友谊系统的SQL结构和询问 [英] Friendship System Sql Structure & Query

问题描述

我正在编写一个友谊系统，它有两个表.

I am coding a friendship system and it has two tables.

成员

• id
• 用户名
• 密码

朋友

• id
• user_id
• friend_id
• 状态

假设我想要一个可以选择成员$ userId的朋友ID的查询，如何在一次查询中做到这一点?

Let's say that i want a query that can select the friends IDs of the member $userId how possible to make this in one query?

我找到了一个解决方案，可以进行2次查询.拳头选择WHERE user_id = $userId的朋友，第二个选择WHERE friend_id = $userId的朋友，然后将它们混合成一个数组.如果没有其他解决方案，我将使用它.

I found a solution which is to make 2 queries. The fist selects the friends WHERE user_id = $userId AND the second selects friends WHERE friend_id = $userId and then MIX them in one array. If there is no other solution I'm going to use it.

请提供有关SQL结构&的任何想法.查询?

please any ideas for both the SQL structure & Queries?

推荐答案

使用:

SELECT f.friend_id
  FROM FRIENDS f
 WHERE f.user_id = $user_id
UNION
SELECT t.user_id
  FROM FRIENDS t
 WHERE t.friend_id = $user_id

使用UNION将删除重复项. UNION ALL会更快，但不会删除重复项.

Using UNION will remove duplicates. UNION ALL would be faster, but it doesn't remove duplicates.

如果要从MEMBERS表中获取朋友的信息，请使用:

If you want to get the information for the friends from the MEMBERS table, use:

SELECT m.*
  FROM MEMBERS m
  JOIN (SELECT f.friend_id 'user_id'
          FROM FRIENDS f
         WHERE f.user_id = $user_id
        UNION
        SELECT t.user_id
          FROM FRIENDS t
         WHERE t.friend_id = $user_id) x ON x.user_id = m.id

顺便说一句:希望您使用的是 mysql_escape_string 在变量上，否则您将冒着SQL注入攻击的风险:

BTW: I hope you're using mysql_escape_string on the variables, otherwise you risk SQL injection attacks:

这篇关于友谊系统的SQL结构和询问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！