CODEWITHSUNDEEP
phpmysqlpdo
Bruce
Bruce

Reputation: 1071

php PDO MySQL Select results of one table based on array of results from another table

The following query will return all "blocker" users and all "blocked" users. Fairly straight forward query.

$stmt=$db->prepare('SELECT blocker,blocked FROM list_blocked 
                    WHERE (blocked = :username AND blocker <> :username) 
                    OR (blocker = :username AND blocked <> :username)');
$stmt->bindParam(':username', $username);
$stmt->execute();

The next query will return all usernames from the members table except the current $username.

$query = $db->prepare("SELECT username FROM members WHERE username <> :username");
$query->bindValue(':user', $username;
$query->execute();
$row = $query->fetchAll();

What I need is a way to get all the values from the results of the first query, excluding the active $username, and then exclude all those results from the second query.

So for example:

Example results of first query:
jim blocked joe
larry blocked joe
steve blocked joe
joe blocked tony
jack blocked joe

Those results applied to the second query would look something like:
$query = $db->prepare("SELECT username FROM members WHERE username <> :username 
                       AND username <> jim 
                       AND username <> larry
                       AND username <> steve
                       AND username <> tony
                       AND username <> jack");

How exactly would I achieve something like this?

UPDATE:

So I am trying to use a subquery but it keeps returning "Operand should contain 1 column(s)"

SELECT username FROM members WHERE username NOT IN (
    SELECT blocker,blocked FROM list_blocked 
                           WHERE (blocked = 'viraladmin' AND blocker <> 'viraladmin') 
                           OR (blocker = 'viraladmin' AND blocked <> 'viraladmin')
 )

ANOTHER UPDATE:

I attempted to use an inner join to achieve this, however the results always return empty, which I assume is because there is actually nothing to join. 

SELECT members.username, list_blocked.blocker, list_blocked.blocked
FROM members
INNER JOIN list_blocked 
ON members.username = list_blocked.blocker
AND members.username = list_blocked.blocked
WHERE members.username <>  'username' 
AND (list_blocked.blocked = 'username' AND list_blocked.blocker <> 'username') 
OR (list_blocked.blocker = 'username' AND list_blocked.blocked <> 'username')

Upvotes: 0

Views: 339

Answers (2)

Barmar
Barmar

Reputation: 780879

You can also use the LEFT JOIN - NULL pattern for this.

SELECT username
FROM members AS m
LEFT JOIN list_blocked AS b
ON m.username IN (b.blocker, b.blocked)
    AND ((blocked = :username AND blocker <> :username) 
        OR (blocker = :username AND blocked <> :username))
WHERE b.blocked IS NULL

This is based on the pattern in Return row only if value doesn't exist

Upvotes: 0

Zack
Zack

Reputation: 127

You can accomplish this using a sub query: 

SELECT username FROM members WHERE username NOT IN (
SELECT blocker,blocked FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
 )

Edited - to fit a SQL query you would need to union in the subquery so the results return no more then one column. : 

SELECT username FROM members WHERE username NOT IN (
   SELECT blocker as username FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
   UNION
   SELECT blocked as username FROM list_blocked 
                WHERE (blocked = :username AND blocker <> :username) 
                OR (blocker = :username AND blocked <> :username)
 )

Upvotes: 1

Related Questions