CODEWITHSUNDEEP
phpmysql
pingpong
pingpong

Reputation: 1247

friends relationships using mysql?

I have these two tables:

user{id,name,password....};
friends{id,user_id,user_id2,accept(bool)}

for instance say i wanted to fetch friends for the user_id=14 and the row accept has to be 1! how would I do the mysql query thanks :)

ps. when fethcing the queries it has to be a 2 to way thing, if user1 is friends with user2 also means that user2 is friends with user1, if you get what I mean! :))

Upvotes: 0

Views: 341

Answers (4)

AndrewKS
AndrewKS

Reputation: 3837

I don't mean to be deprecating, but this is very, very basic sql, and you could have easily found the answer through google or books. That being said, here is the answer.

SELECT * FROM friends WHERE (user_id=14 OR user_id2=14) AND accept=true;

Let me break it down for you, since you are using SQL and if you don't learn it you will be wasting a lot of time searching for answers.

SELECT * FROM friends

means get all rows from friends. The "*" is a replacement for column names and means "all columns". For instance, if you had

SELECT (id,user_id) FROM friends

The rows returned would only contain those columns.

Then comes the WHERE clause.

WHERE (user_id=14 OR user_id2=14) AND accept=true

means exactly how it looks: the rows you select should have at least one user_id equal to 14 and accept should be true. MySQL uses true, false, and null (if allowed) for booleans. Some other database, like MSSQL, which uses a "BIT" type rather than a boolean uses 1, 0, or null for true, false, and null respectively.

The OR clause is because, as you stated in your edit, this friendship can be "2 way".

So if you put it together,

SELECT * FROM friends WHERE (user_id=14 OR user_id2=14) AND accept=true;

Upvotes: 1

Scott
Scott

Reputation: 17037

I would probably not do that in two tables. It makes more sense to me to have this be a many to many relationship between users. The problem you have is that you are capping the number of 'friends' in the friends table. That is unless you have the friends tables as a child of user. I'll assume there is a foreign key in the friends table pointing to user. I think you're part way there, but not sure why you have user_id2 as it doesn't make sense relationally. In this case the query would be:

select *
from user
   inner join friends on friends.user_id = user.id
where friends.id = true

Upvotes: 1

Rohan Grover
Rohan Grover

Reputation: 1604

Please try the following.

select 
 f1.user_id2 
from 
 user u,friends f1 , friends f2
where
f1.user_id1 = 14,
f1.accept = 1,
f1.user_id2 = f2.user_id1,
f2.user_id2 = f1.user_id1,
f2.accept = 1

Thanks, Rohan

Edit: Only makes sense joining user u table if you need details for the friends. Otherwise you can take the user u table out.

Upvotes: 2

James Miller
James Miller

Reputation: 71

This will give you every row for user_id=14 that accepted a friend

SELECT
  *
FROM
  friends
WHERE
  user_id = 14
  AND
  accept = TRUE

Upvotes: 0

Related Questions