php - 过滤mysql查询结果

Question

我不太确定这是 MySQL 问题还是 PHP 问题。如果可以用 SQL 来解决那就更好了。

目前我有以下内容:

$sql=$dbh->prepare("SELECT DISTINCT ToUserID, FromUserID FROM Message WHERE Deleted is NULL AND (ToUserID=? OR FromUserID=?)");
$sql->execute(array($_POST['UserID'], $_POST['UserID']));

$messages = $sql->fetchAll();

MySQL 查询可能返回类似以下内容:

{
      "ToUserID" : "1",
      "1" : "2",
      "0" : "1",
      "FromUserID" : "2"
    },
    {
      "ToUserID" : "2",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    }

我需要的是能够对结果进行过滤，使得上述两个结果只返回一个。即

if (item1.ToUserID == item2.FromUserID) && (item1. FromUserID == item2.toUserID) {
  remove item2
}

另一个例子:

{
      "ToUserID" : "1",
      "1" : "2",
      "0" : "1",
      "FromUserID" : "2"
    },
    {
      "ToUserID" : "2",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    },
    {
      "ToUserID" : "3",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    },
    {
      "ToUserID" : "4",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    },
    {
      "ToUserID" : "4",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    }

会变成:

{
      "ToUserID" : "1",
      "1" : "2",
      "0" : "1",
      "FromUserID" : "2"
    },
    {
      "ToUserID" : "3",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    },
    {
      "ToUserID" : "4",
      "1" : "1",
      "0" : "2",
      "FromUserID" : "1"
    }

Answer 1

与此无关，我会将默认的获取模式更改为 PDO_FETCH_ASSOC，这样您就不需要费心使用编号键，它只会重复信息。

然后，这些组合

 1  2  User1
 2  3  User2
 2  1  User2

您想要消除第二次出现的 User2，因为 1-2 对已经存在。

您可以通过添加两个计算字段在 MySQL 中执行此操作:

 SELECT ...
 ... IF (user1 < user2, user1, user2) AS oneUser,
 ... IF (user1 < user2, user2, user1) AS anotherUser,
 ...

现在，两行都将有两个额外字段设置为“1”和“2”，您可以对它们添加不同的约束。

当然你也可以在 PHP 中做到这一点:

 // Array
$seen = [ ];
while ($tuple = $rs->fetch()) {
    $key = $tuple['user1id'] < $tuple['user2id']
         ? "{$tuple['user1id']}:{$tuple['user2id']}"
         : "{$tuple['user2id']}:{$tuple['user1id']}";
    if (in_array($key, $seen)) {
         // Ignore this tuple.
         continue;
    }
    // Add combination to seen list
    $seen[] = $key;

    ...
}