I have a page where should be displayed:

  • friend requests
  • already confirmed friends in this form: id - Username

How to implement such a system in PHP:

  1. What should be the structure of the database?
  2. How to determine that the user is friends or just sent a request?

    3 answers 3

    Make a separate table for applications, for example reguests. Let it be like this:

      | id | sender | taker | accept | 

    The first three fields are clear, but accept let it have values ​​of 1 or 0, depending on the confirmation of the application. That is, 1 if confirmed, 0 if not yet.

    To implement the system of friends, make a table frinends (for example) and let it have something like this:

      | id | id1 | id2 | 

    In the fields id1 and id2 you enter two people who made friends. Then just check for a pair of IDs in the table.

    Here it is. Briefly and I think clearly) Recently, I myself puzzled over such a system, so I will be happy to help.

    • And it is impossible to manage with one table? Let's put everything in the second, friends. And another such question, as I understood in the id field there is a pair of two id (id1 + id2)? - ka5itoshka
    • @ ka5itoshka, in the id field, the id of the line itself (well, the parameter will be useful. The id of people in this column do not participate). And if you put everything in one table, then you will have to use explode and more cycles - and this will load the memory. So two tables are enough;) - Emil Sabitov
    • @Emil Sabitov, I just can’t understand how to output the information I need in your implementation)) the user whose friends we want to display can be in some cases id1 or id2 (Suppose the first case - I sent a request to you, the second I received a request from you and in both cases the applications are confirmed, yes ?. And there is a user who does not accepted the application (ignores) and we don’t need to display this (i.e., we will output only those with whom 100% have become friends))) - ka5itoshka
    • one
      SELECT тут что нужно выбрать FROM reguests WHERE ( id = 'required' OR id2 = 'necessary ) AND accept` = 1 - Emil Sabitov
    • @ Emil Sabitov, thank you very much! Helped out :) - ka5itoshka

    Suppose you already have a table with users. It remains to create a table in which there will be "statuses" of relations between them and, if necessary, you can create another table in which the values ​​of statuses will be stored

    Table users_status :

    • user_inviter (user id who invited)
    • user_invited (guest user id)
    • stat_val_id (the current status is the key to the stat_id field in the table below)

    Status_value table

    • stat_id
    • stat_val (invited, rejected, accepted, ignored etc.)


    See the request at work or so:

     SELECT * FROM `userlist` WHERE `user_id` IN ( SELECT IF(`user_invited` = '3', `user_inviter`, `user_invited`) FROM `users_status` WHERE `stat_val_id` = '2' ) 
    • user_invited = '3' - "3" - the id of the user who is looking for friends
    • stat_val_id = '2' - "2" - status, meaning "friendly"
    • In principle, everything is clear, that's just this: let's say we want to bring out the friends of a certain person. What do you need to specify in such cases in SELECT? - ka5itoshka
    • If the status "friend" has id = 100500, then we get the id-names of all friends: SELECT user_invited FROM users_status WHERE user_inviter = '$ user_id' AND stat_val_id = '100500' - Deonis
    • @Deonis, you misunderstood me. There is a page of a person with id = 390. How to display on it all added (mutually) friends. those. a person can be user_inviter or user_invited and if this is all mutual, the records should be displayed. That is the crux of the problem. - ka5itoshka
    • @Deonis, your way, in my opinion - a little worse than I described in the previous answer. @ ka5itoshka and so I asked you to make one table, you already have 3. Maybe something else can be done? - Emil Sabitov
    • one
      Emil Sabitov, about the table with the values ​​- this, as I said, "if necessary" and absolutely not necessary. You only need to know their values, for example, 1 -> invited, 2 -> accepted, 3 -> rejected. And about the second remark, then you just need a correctly composed request. See the option on this link , but I’m not as strong in the requests as, say, @Yura Ivanov, so I don’t mind if he optimizes it. PS I updated my answer in the same way. - Deonis

    Here you can do with one table with the fields userId1, userId2, status. Consider the example of contact - there if you send a friend add request, you become a subscriber, and when the application is confirmed, then friends. Here, the status field is responsible just for determining the type of communication, and their change must be defined in the triggers. At the application level, you should not make a status change.

    Example. The user makes a request to add as a friend, while fulfilling the request:

     insert into friends_rels (userId1, userId2) values ($id1, $id2); 

    And the trigger has to determine whether this is an application, or confirmation. Defined by querying the table:

     select userId1, userId2 from friends_rels where userId1 = $id2 and userId2 = $id1; 

    If it does, it means this confirmation of the application: modifies the string NEW.status = 'friends' and performs an update on the application, where it also assigns the status of the fact that they are friends. If the record was not found by the select, then this is an application for adding as a friend and you just need to modify the status: NEW.status = 'subscribe' .

    The primary key for the string should not be done - it is simply not needed, but you only need to hang the uniqueness on the combination of the two fields.

    Thus, all logic is concentrated in one trigger only at the DB level, i.e. at the data storage level. There are no extra Join to determine the type of connection. Also, there is no need to handle a bunch of private situations like the one when one user made a request to the second, and the second user made a request to add a friend as the first - everything will be correct here, and in other implementations a deviation from the desired behavior is possible.

    • In fact, the same scheme was in the first answer, only with the primary key) And so well written! - Emil Sabitov
    • @ Emil Sabitov, only your scheme seemed to me much easier)) - ka5itoshka