Reputation: 3685
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
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