CODEWITHSUNDEEP
phpmysql
DevWithZachary
DevWithZachary

Reputation: 3685

Filter result from mysql query

I'm not really sure if this is a MySQL question or a PHP one. If its possible to be solved in SQL that would preferred.

Currently I have the below:

$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();

The MySQL query might return something like:

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

What I need is to be able to filter the results so that the above two results only one would be returned. i.e

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

another example: 

{
      "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"
    }

Would become:

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

Upvotes: 0

Views: 31

Answers (1)

LSerni
LSerni

Reputation: 57463

Unrelatedly, I would change the default fetch mode to PDO_FETCH_ASSOC so that you don't need to bother with numbered keys, which just duplicate information.

Then, of these combinations

 1  2  User1
 2  3  User2
 2  1  User2

you want to eliminate the second occurrence of User2 because the 1-2 pair already exists.

You can do this in MySQL by adding two computed fields:

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

Now, both rows will have the two extra fields set to "1" and "2", and you can add a distinct constraint on those.

Of course you can also do this in 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;

    ...
}

Upvotes: 1

Related Questions