php - 避免 PHP 中的重复值

Question

我在 php 中创建了一个“你可能认识的人”脚本。这个脚本在 php 好友系统中向我展示了我 friend 的 friend 。我有一个名为 users (user_id, name, surname, email, profile) 的表，其中保存有关用户的信息。另一个名为 friends(friend_id, user_one, user_two) 的表，保存好友用户的 ID。我的代码如下:

<?php
  // ------ gives me my friends

  $friend_query = mysql_query("  SELECT `user_one`, `user_two` FROM `friends` WHERE `user_one`='$session_user_id' OR `user_two`='$session_user_id' ");  

  while($run_friend = mysql_fetch_array($friend_query)){
    $user_one = $run_friend['user_one'];
    $user_two = $run_friend['user_two'];

    if($user_one == $session_user_id){
      $user = $user_two;
    } else {
      $user = $user_one;
    }

    $friend_id = getuser($user, 'user_id');


    // ----- gives me friends of my friends
    $friend_query_two = mysql_query("  SELECT `user_one`, `user_two` FROM `friends` WHERE (`user_one`='$friend_id' and `user_two` != '$session_user_id') OR (`user_one`!='$session_user_id' and `user_two`='$friend_id' )  ");  

    while($run_friend_two = mysql_fetch_array($friend_query_two)){                          
      $user_one_two = $run_friend_two['user_one'];
      $user_two_two = $run_friend_two['user_two'];

      if($user_one_two == $friend_id){
        $user_two = $user_two_two;
      } else {
        $user_two = $user_one_two;
      }

      $friend_id_two = getuser($user_two, 'user_id');
      // ------- gives me friends of my friends that are my friends also

      $check_friend_query = mysql_query("  SELECT friends_id from friends WHERE  (user_one='$session_user_id' AND user_two='$friend_id_two') OR (user_one='$friend_id_two' AND user_two='$session_user_id')  ");

      if (mysql_num_rows($check_friend_query) != 1){ 
        //here is the problem where I get duplicate values about friends of my friends

        $my_friend = $friend_id_two;
        $friends_friends = mysql_query("SELECT `user_id`, `name`, `surname`, `email`, `profile` FROM `users` WHERE (`user_id`='$my_friend')  ");

        while ($run_friends= mysql_fetch_assoc($friends_friends)) {
          $friend_user_id = $run_friends['user_id'];
        }
?>

我的代码运行良好，并为我提供了 friend 的 friend 。问题是我得到了一些重复的值。知道如何避免这种情况吗？

Answer 1

已更新

PHP 实现可能如下所示:

global $db;
$db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');

$friends = getFriendsOf( $session_user_id );
$friends_of_friends = getFriendsOf( $friends );

// Do whatever you want right here
// with your friends ($friends)
// and your friends' friends ($friends_of_friends)

function getFriendsOf( $user ) {
    global $db;

    if ( is_array( $user ) ) $where = ' IN (' . implode( ',', $user ) . ')';
    else $where = ' = ' . $user;

    $sql = "
SELECT GROUP_CONCAT( f.user_id ) as friends
  FROM (SELECT user_one as user_id
          FROM friends
         WHERE user_two {$where}
         UNION DISTINCT
        SELECT user_two
          FROM friends
         WHERE user_one {$where}
       ) f";

    $results = $db->query( $sql );
    $row = $results->fetch();
    return explode( ',', $row[ 'friends' ] );
}

原创

没有任何数据可供测试。但像这样的事情应该可以解决问题。

交 friend :

SELECT u.*
  FROM (SELECT user_one as user_id
          FROM friends
         WHERE user_two = '$session_user_id'
         UNION DISTINCT
        SELECT user_two
          FROM friends
         WHERE user_one = '$session_user_id'
       ) f
  JOIN users u
    ON u.user_id = f.user_id

获取 friend 的 friend :

SELECT u.*
  FROM (SELECT DISTINCT user_one as user_id
          FROM friends
         WHERE user_two IN (SELECT user_one as user_id
                              FROM friends
                             WHERE user_two = '$session_user_id'
                             UNION DISTINCT
                            SELECT user_two
                              FROM friends
                             WHERE user_one = '$session_user_id'
                           )
         UNION DISTINCT
        SELECT DISTINCT user_two
          FROM friends
         WHERE user_one IN (SELECT user_one as user_id
                              FROM friends
                             WHERE user_two = '$session_user_id'
                             UNION DISTINCT
                            SELECT user_two
                              FROM friends
                             WHERE user_one = '$session_user_id'
                           )
       ) f
  JOIN users u
    ON u.user_id = f.user_id