Bruce Bruce - 2 months ago 11
PHP Question

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')

Answer

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)
 )