Friends system in PHP and SQL

Asked

Viewed 509 times

0

I have a problem and I haven’t found the solution yet. I have 2 tables, one of users and another of friends.

In the user table I store the id and the username

On the table of friends I have the field user_1_id and the user_2_id , which contains only the user id of the two friends.

How do I query the output of the logged-in user’s friends with ID=6, including their username of his friends?

php sql

  • SELECT * amigos user_1_id = $_SESSION['id'] where the session id variable stores the logged-in user id.. Then when retrieving the data you make another query in the user table by username id you got in the first query, or you can do it using JOIN(I think).. Try to put the code you are using, thus increases the chance of proper response.

  • mysql, postgresql, sqlserver?

  • 2

    All his questions were using Mysql, I believe that if it was another would warn, but good question. I failed to deduce that it would be Mysql. I have to learn not to answer empty questions, but I can’t. =(

  • @Inkeliz but only you have understood my point of view so easily with a comment as vague as mine demonstrates that you are smart and take things easily, give you a hint, always use the button to [close] vague questions, even if it is "silly" thing. Closing is not deleting or banning is just preventing questions that need clarification from receiving answers without reaching the minimum standard of quality ;)

  • I don’t have access to this function, I believe. If you do I don’t know where it is. Then I poke around the Meta to see if you have anything about it. : S

1 answer

3


There are several 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 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.

There is duplicity between friends (user_1_id = 1 and user_2_id = 2 | user_1_id = 2 and user_2_id = 1) since each one is each one’s friend? If yes, it’s ready!

Case there is no duplicity will have to make some changes, can see below, but not recommended for major performance problems, was just to give 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 your accepted (for example!) what could cause me to be one user_1_id or user_2_id.

To get the '6' could use $_SESSION[''] or $_COOKIE[''] or any other similar method, but the question seems to be the Mysql structure and not this.

  • 1

    I’ll try it out later and I’ll be in touch. But it looks that way. I’ll have to do one more INSERT when the user accepts the friend. Thank you

  • 2

    thanks that solved my problem. I made another Insert for the user id=6 could be user_1_id and user_2_id. Then I call friends for $_SESSION['id']. Thank you!

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.