I have a problem and still have not found the solution. I have 2 tables, one for users and one for friends.
In the user table I save id and username
In the friends table I have the field user_1_id and user_2_id , which contains only the user id of the two friends.
How do I query friends for the logged in user with ID = 6, including their username faults of their friends?
There are many ways, with different levels of performance and ease.
SELECT utilizador.* FROM utilizador WHERE id IN (SELECT user_2_id FROM amigos WHERE user_1_id = '6');
In this case user_2_id would always have to be the friend. While the user_1_id would always be the same as the connected user. In this case whenever you add someone it would be necessary to have two INSERT .
Is there duplication between friends ( user_1_id = 1 and user_2_id = 2 | user_1_id = 2 and user_2_id = 1 ) since everyone is friends with each? p>
If there is no duplicity you will have to make some changes, you can see below but not recommend for major performance problems , it was only for give you an idea and quickly.
SELECT utilizador.* FROM utilizador WHERE (id IN (SELECT user_2_id FROM amigos WHERE user_1_id = '6') OR id IN (SELECT user_1_id FROM amigos WHERE user_2_id = '6')) AND id != '6';
The reason for this is that I can add or its accepted (for example!) what could make me a user_1_id or user_2_id .
To get '6' you could use $_SESSION[''] or $_COOKIE[''] or any other similar method, but the question seems to be the structure of MySQL and not this.