Two tables referring to each other
I am trying to create a simple social networking program with posts about users and their friends. That is, the user with ID 1 has friends with IDs 2, 3. The user with ID 2 has friends with IDs 1, 3, 4. and so on. Now I would like to get the records of this user and his friends.
I have created two tables
user
user_id | firstname | surname | age
friend
user_id | friend_id
And writes something like this:
user
user_id | firstname | surname | age
1 | Nikola | Misic | 22
2 | Stefan | Ilic | 23
3 | Dragan | Jovic | null
friend
user_id | friend_id
1 | 2
1 | 3
2 | 1
3 | 1
3 | 4
user_id and friend_id are foreign keys pointing to user_id in the user's table.
The relationship looks like this:
I'm not sure if this is possible, maybe I was working with three tables, two tables linked through a composite table.
source to share
The proposed relationship as you described it (a table with two foreign keys to columns in another table) is quite possible and a relatively common thing to do.
As far as requests to user friends, based on what you said in the comments, I think this is:
select
u.*
from
users u
inner join friends f
on u.user_id = f.friend_id
where
f.user_id = ?
?
will contain the userid parameter from $_GET["userid"]
source to share