RonWon
asked on
Connect friends and user table together
Hello everybody,
I'm running into a problem with developing my site.
People can become friends of one and eachother and I want these people to see their friends a a specific page.
I have two tables:
Table 1: Friends
ID
user_1
state_1
user_2
state_2
Table 2: users
ID
First_Name
Last_Name
etc
People are friends once their ID is in user_1 or user_2 and both states are set to '1'.
Now I need to connect to the database and make a query where it pulls your friends ID and connects this to the users databes to get First_Name, Last_Name, etc.
How do I do this?
One of the problems is that the friends ID can be in column user_1 or user_2, so how do I filter this?
I saw a questions pretty similiar to mine, with a good answer, but just couldn't get this to work!
https://www.experts-exchange.com/questions/21498279/Database-setup-for-Friends-network-What's-the-best-way.html?sfQueryTermInfo=1+friend
Hope you guys can give me some help!
I'm running into a problem with developing my site.
People can become friends of one and eachother and I want these people to see their friends a a specific page.
I have two tables:
Table 1: Friends
ID
user_1
state_1
user_2
state_2
Table 2: users
ID
First_Name
Last_Name
etc
People are friends once their ID is in user_1 or user_2 and both states are set to '1'.
Now I need to connect to the database and make a query where it pulls your friends ID and connects this to the users databes to get First_Name, Last_Name, etc.
How do I do this?
One of the problems is that the friends ID can be in column user_1 or user_2, so how do I filter this?
I saw a questions pretty similiar to mine, with a good answer, but just couldn't get this to work!
https://www.experts-exchange.com/questions/21498279/Database-setup-for-Friends-network-What's-the-best-way.html?sfQueryTermInfo=1+friend
Hope you guys can give me some help!
ASKER
Hey dgmg,
The different states are used as a check for who asked who as friend!
For instance, when Mike (ID: 005) asks Jen (ID: 009) as friend, the table gives:
ID user_1 state_1 user_2 state_2
1 005 0 009 2
So, on Jen's page, a query searches for user_2 = 009 and state_2 = 2
When Jen accepts the friend request, a form updates the table to both states 1
ID user_1 state_1 user_2 state_2
1 005 1 009 1
So this means they have both agreed to the friendship!
I have a session variable made upon login called kt_login_id, this is the main ID of the user.
So when I want to select all records where the user is a friend (so 'kt_login_id' in user_1 or user_2 column).
The query I use to do this is:
SELECT *
FROM Friends
WHERE user_1 = '$_SESSION['kt_login_id']' AND state_1 = '1' OR user_2 = '$_SESSION['kt_login_id']' AND state_2 = '1'
Now it needs to be expanded so that it pulls the user data from table 'users'. This is the user data from the column where the ID is not $_SESSION['kt_login_id']!
Hope this all makes sense.
I could get your former code to work, so I hope this explanation helps a bit more!!!!!
The different states are used as a check for who asked who as friend!
For instance, when Mike (ID: 005) asks Jen (ID: 009) as friend, the table gives:
ID user_1 state_1 user_2 state_2
1 005 0 009 2
So, on Jen's page, a query searches for user_2 = 009 and state_2 = 2
When Jen accepts the friend request, a form updates the table to both states 1
ID user_1 state_1 user_2 state_2
1 005 1 009 1
So this means they have both agreed to the friendship!
I have a session variable made upon login called kt_login_id, this is the main ID of the user.
So when I want to select all records where the user is a friend (so 'kt_login_id' in user_1 or user_2 column).
The query I use to do this is:
SELECT *
FROM Friends
WHERE user_1 = '$_SESSION['kt_login_id']'
Now it needs to be expanded so that it pulls the user data from table 'users'. This is the user data from the column where the ID is not $_SESSION['kt_login_id']!
Hope this all makes sense.
I could get your former code to work, so I hope this explanation helps a bit more!!!!!
ASKER
The last line must say!!!!!!
I !couldn't! get your former code to work, so I hope this explanation helps a bit more!!!!!
Thanks!
I !couldn't! get your former code to work, so I hope this explanation helps a bit more!!!!!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked!
Thanks a million!
Thanks a million!
Now to your question. I am assuming the "Friends" relationship is bi-directional. By that I mean participation as user_1 means the same thing as partipating as user_2. In fact, a check constraint that user_1 < user_2 would be entirely logical to avoid duplicates or being a friend with yourself.
Anyway, here's the SQL:
Select u.firstname, u.lastname, f.firstname, f.lastname
From
(Select user_1 user, user_2 friend from Friends where status_1=1 and status_2=1
union user_2, user_1 from friends where status_1=1 and status_2=1) r
inner join users u on u.id = r.user
inner join users f on f.id = r.friend
order by u.id