使用PHP处理关注/关注者

In my website I've a concept of following each other kinda twitter, I have a user connection table with the following structure,

==============================================================

ConnectedTo | ConnectedBy | SessionID | Connect_Status | RecordDate

==============================================================

ConnectedTo Holds User You Are Connecting To

Connnected By Holds The User Who Is Connecting

SessionID Again HoldsThe User Who Is Connecting

Now suppose we assume that user A Is connected to User B Table Entry will be like

===============================

B_id | A_id | A_id | connected | date

===============================

Now A is following B, Now Even If B Follows A Entry Will Be Like

===============================

A_id | B_id | B_id | connected | date

===============================

Now what I want is if both are following each Other, I've a page called network, so if am User A and I login, if I go to my network page, I should see people whom am following are following me back, but am not able to make out how should I detect that which users are following me back...It's like Google plus I gues, if you follow a user, you are following him, but if he follows you back than you are friends..so should I add 1 more field kinda say field X where before connecting I check if that user is following me, I should update both records as friends in field X.

And P.S I tried many things so don't ask me what I tried, I tried, and tried but still not able to makeout

Say your user ID is @id and connection table name is CONNECTION. Then the following query will list IDs of all users who are connected back to you:

select 
   me2friend.ConnectedTo
from 
   CONNECTION me2friend,
   CONNECTION friend2me
where
   me2friend.ConnectedBy = @id and
   me2friend.ConnectedTo = friend2me.ConnectedBy and
   friend2me.ConnectedTo = @id

$query = "SELECT c.ConnectedTo, cc.ConnectedBy
          FROM connections c
          LEFT JOIN connections cc ON c.ConnectedTo=cc.ConnectedBy AND cc.ConnectedTo='" . $loged['my_loged_user_id'] . "'
          WHERE c.ConnectedBy='" . $loged['my_loged_user_id'] . "'";
$sql = $mysqli->query($query);
while($row = $sql->fetch_assoc()) {
    if(isset($row['ConnectedBy'])) {
        // I'm connected with $row['ConnectedTo'] who is also following me
    } else {
       // I'm connected with $row['ConnectedTo'] but he don't follows me
    }
}

This seems like a very simple problem. You simply query first to find out if the user already has a connection to see if it should be made into a mutual connection.

SELECT * FROM tbl_connections WHERE ConnectedTo = $foreign_user_id OR connectedBy = $foreign_user_id

Once you have your row (or not) you judge the direction of the relation and append the user ID as needed:

if($result->rowCount() > 0){
    foreach($result as $row){
        if($row['ConnectedTo'] == $my_user_id) $row['ConnectedBy'] = $foreign_user_id;
        if($row['ConnectedBy'] == $my_user_id) $row['ConnectedTo'] = $foreign_user_id;
        $row['Connect_Status'] = 2; // 2 denotes that it is a mutual connection on the graph
        // Save row
        break;
    }
}else{
    // Insert new row
}

This is just a quick off hand answer.

It is true it is not the most elegant solution but it is one that provides more power in terms of how and when to create a mutual relationship. As you will notice a dual relationship between two people on G+ does not always mean friends. It is actually dependant upon the group you place them in as well.

Edit

The above code will only be used when you make a new friend. When querying you can just query for all rows and then show say a label for mutual connections judged by the connect_status or you can just query for only that connect_status:

SELECT * FROM tbl_connections WHERE Connect_status = 2

Will get all mutual connections.