JOIN command to stop adding duplicate fields (php & mysql)

[Wardy]

Tweet

Yep, I have yet another problem
I have a database that has the following userid, frienduserid, friendusername in it (although friendusername probably doesn't really need to be there)

What happens is that a user (userid) can add another user as their friend.
So this means any user can add any other user to their friends list.
When a user clicks on the "add friend" link on my site the details are updated with the "new friend partnership". What I am having problems with is that a user can add the samne friend as many times as they want, so what I need is that if a user alreday has a friendship then if they try to add another then it doesn't get added.

I've been messing aroudn tryign to get a JOIN working so it works correctly but I just can't seem to get the thing working

This is what I have it as at the moment (obviously wrong)

    $query  = "SELECT * FROM friends LEFT JOIN `players` ON friends.frienduserid = players.userid WHERE friends.userid='$frienduserid2' AND friends.frienduserid='$friend2'";          
    $result = mysql_query($query) or die("Error: " . mysql_error()); 

    if(mysql_num_rows($result) == 1){ 
        echo("There is already a friendship request"); 

Can anyone help please (if I have made it clear enough anyway)?

Cheers
Wardy

[Rich]

I can't really see why you need the join. Isn't it just a case of select from friends where userid is this users id and frienduserid is the id of the person they are trying to add?

Current you seem to be comparing userid to the $frienduserid2 rather than the current users id - it might just be a case of a badly named variable.

[Wardy]

Rich you are indeed right. The reason I was trying to get more complicated than I needed to was someone suggested it.
I also forgot to delete all the duplicate enteries I had for each friendship request so it wasn't just matching 1 with the freinds I was testing it on, d'oh
At least the afternoons work has now been sorted, even though it shoudl of taken 5 mins

Cheers
Wardy