MySQL compare two tables help.

[derekshull]

I have 2 tables that I want to compare and find the users that the username is not following and then display the username of the person not being followed.

Table 1: users Table 2: follow

In my head I see table 1 getting a list of all the usernames (column name is "username" from table 1) and then gets a list of all the usernames the user ($username) is not following (column name is "followname" from table 2).

How do I make it compare the two lists and not display the usernames the user is following already?

Table two is set up like so:
(username)         (User they follow)
username               followname
derekshull              dvdowns
derekshull              testuser
testuser                dvdowns

 

 

Here's what I have so far.  It's displaying users, but it's displaying users I follow and don't follow.  Weird.

 

$alluserslookup = mysql_query("SELECT * FROM users WHERE username!='$username'");
$thefollowerslookup = mysql_query("SELECT * FROM follow WHERE username='$username'");

while ($followersrow = mysql_fetch_assoc($thefollowerslookup)) {
$afollower = $followersrow['followname'];

while ($allusersrow = mysql_fetch_assoc($alluserslookup)) {
$allusers = $allusersrow['username'];

if ($afollower != $allusers) {
echo "<a href='viewprofile.php?viewusername=$allusers'>$allusers</a><br>";
}
}
}

[Jessica]

You haven't given us enough information.

You also need to look up how to use primary keys and foreign keys, and how to do a join.

[derekshull]

Ok after googling more for a while, I came up with this:

$alluserslookup = mysql_query("select u.* from users u left join follow f on u.username = f.username and f.followname = '$username' where f.username is null");

while ($allusersrow = mysql_fetch_assoc($alluserslookup)) {
    
    $allusers = $allusersrow['username'];
    
    echo "<a href='viewprofile.php?viewusername=$allusers'>$allusers</a><br>";
}

It's still displaying users that I follow and don't follow though.

Basically I'm take a list of all "username"'s from table "users   --->   taking a list of all the "followname"'s where "username" == "$username" from table "follow"    ---->  Then displaying only the names that don't appear in both lists.

[Barand]

Followed, but not followed by user B

SELECT a.username as notfollowed
FROM follow a
LEFT JOIN follow b ON b.username = a.followname
    AND b.username = 'User B'
WHERE b.username IS NULL
    AND a.username <> 'User B';

Folllowed by user B

SELECT a.username as followed
FROM follow a
INNER JOIN user b ON b.username = a.followname
    AND b.username = 'User B';

Edited April 24, 2013 by Barand