douchun6108 2017-11-16 17:17
浏览 18
已采纳

PHP和MySQL上的Friendsystem

at this moment I'm stucked on my code.

I want to display the names of accepted friends.

I have two tables:

Users 
[id, useruniqueid, username]

Friends
[id, user_a, user_b, status]

(status can be requested, pending or accepted)

For example:

Users:
1, 123, Kaan2106
2, 321, SOUser1
3, 456, SOU2
4, 654, Some4

Friends:
1, 123, 321, accepted
2, 321, 123, accepted
3, 456, 123, accepted
4, 123, 456, accepted
5, 654, 123, pending
6, 123, 654, requested

I want to display my friend(s) names like: "SOUser1" (because it's accepted)

And this is what I done:

<table>
<tr>
    <td>Name</td>
</tr>
<?php
$getem = mysql_query("SELECT * FROM Friends WHERE (`status` = 'accepted') AND (`user_a` = '$UserUUID' OR `user_b` = '$UserUUID') LIMIT 10");
while($row = mysql_fetch_assoc($getem)) {
    $friendsql = mysql_query("SELECT * FROM Users WHERE useruniqueid = '" . ($row['user_a'] == $UserUUID ? $row['user_b'] : $row['user_a']) . "'");
    $friendrow = mysql_fetch_assoc($friendsql);
?>
<tr>
    <td class="friend-name">
        <?php echo $friendrow['username']; ?>
    </td>
</tr>
<?php } ?>
</table>

And my output is like this SOUser1, SOUser1, SOU2, SOU2 (unwanted doubles)

The expected output should be like this SOUser1, SOU2 (no doubles)

I know that mysql_* is deprecated

  • 写回答

1条回答 默认 最新

  • douke3335 2017-11-16 17:34
    关注

    If you do it all in one query (with a join), you can use MySQL's distinct operator to only return one instance of each user's name.

    Try using a query like the one below. The join uses a case statement so that it joins the Friends table by either user_a or user_b, depending on which one is not the $UserUUID.

    select distinct
        u.username
    from
        Friends f
        join Users u
            on (
                u.useruniqueid = case
                                    when f.user_a='{$UserUUID}' then f.user_b
                                    else f.user_a
                                end
                )
    where
        (f.user_a = '{$UserUUID}' or f.user_b = '{$UserUUID}')
        and f.status='accepted'
    

    See it in an SQL Fiddle, with $UserUUID being set to '123'.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能