SQL Followers and Followers

Question

Ok I have two tables, one of which is called account_members , and the other is account_follows . I want the Twitter system to use the following system in which account_members can follow each other.

 Account Follows Table Structure: id account_name followed_name time Account Members Table Structure: id Account_name status (Account active or not) 

It seemed to me that I could get away with one simple request to get all the accounts:

 public function following($account_name) { $sql = "SELECT F.id, F.account_name, F.followed_name, F.time, M.account_name AS session_name, M.status FROM account_follows F LEFT JOIN account_members M ON F.account_name = M.account_name WHERE F.account_name = :account_name AND M.account_name = :account_name ORDER BY id DESC LIMIT 5"; } 

All accounts will be displayed here followed by ( $account_name set via the URL)

The problem I am facing allows me to register with account_member the ability to track or unsubscribe friends of friends they follow. I am doing a simple check for the one registered with account_member to unsubscribe from any of their list by following these steps:

 if($_SESSION['account_name'] == $row['account_name']) { echo'<a href="" id="..." class="...">Unfollow</a>'; } 

The above works fine, but I want to do something similar with the followers registered in the accounts ... If that makes sense?

So, Bob is logged in, and Bob scans his next list and clicks mike , and sees who is mike strong>, and from this list can follow / unsubscribe from mike (and some of which Bob can follow)

Any help or guidance appreciated.

Answer 1

The request that you have will work for any member account name that passed through it, but the request itself does not take into account the currently incoming participant, so you need to join them.

The query returns the list of members specified after the specified account URL. At the same time, this is a bit that reports whether the registered user is also a member of this member. Use this bit to decide if you want to echo the link or unsubscribe.

 SELECT theirFollows.id, theirFollows.account_name, theirFollows.followed_name, theirFollows.time, M.account_name AS member_name, M.status, case when myFollows.followed_name is null then 0 else 1 end as sessionMemberIsFollowing FROM account_members M LEFT JOIN account_follows theirFollows ON theirFollows.account_name = M.account_name LEFT JOIN ( select followed_name from account_follows where account_name = :session_account_name ) myFollows on myFollows.followed_name = theirFollows.followed_name WHERE M.account_name = :account_name 

In one of your selected columns, session_name has been assigned, but this is a bit misleading, as account_name is passed from the URL. In addition, only one of you where clauses are needed, as this is the column that you enter.