Check if a user is following another user (social networking site)

[adambedford]

I'm in the process of building a social networking platform and I'm having a little trouble with this. Essentially, when a record is displayed (showing user info) I have a button giving the option to 'follow' that user. Obviously there are some conditions to displaying this buton, such as the user has to be logged in, but I also need to check that theyre not already following the other user.

 

I've got a simple if statement to check that there is a session in progress which then displays the 'follow' button, and if there is no session, it displays a 'log in to follow' message. The third option I need is 'You're already following this user'.

 

Below is my simple code that I have currently that displays the button. To check if they're already following them, I'm thinking I need to query my relationships table filtering results to only the logged-in user ID. How would I then go through that to check that the other user (which is actually in a table called businesses) isn't already matched to the user and therefore already being followed by them?

 

        <!--follow the business-->
        <?php if ($_SESSION) { ?>
        
        <a href="LINK TO FOLLOW SCRIPT" class="follow">Follow this business</a>
        
        <?php }

	else { ?>
        
        <a href="LINK TO NOT LOGGED IN CODE" class="follow">Login to follow</a>
        
        <?php } ?>
        
        <!--end follow code-->

 

So yeah, I'd really appreciate any help on this. I've got as far as querying the relationships table with a WHERE clause for the logged in user ID but then I don't know how to go about checking that the business ID in question isn't already being followed by that user.

 

Thanks in advance!

[Pawn]

Assuming the relationships table is in the form of pairs of user ID's, you would want to look for a row containing both the ID of the logged in user and the ID of the user they are looking at. E.g.

 

$user1 = $_GET['id'];
$user2 = $_SESSION['user_id'];
$sql = "SELECT * FROM relationships WHERE (user1 = $user1 OR user2 = $user1) "
     . "AND (user1 = $user2 OR user2 = $user2) LIMIT 1";
$query = mysql_query($sql);
if(!$query) {
    echo mysql_error()." <br />Query: ".$sql;
    die;
}
if(mysql_num_rows($query) == 1) {
    echo "You are already following this user.";
} else {
   echo "<a href=\"".$_SERVER['PHP_SELF']."?follow=".$user1."\">Follow this user.</a>";
}

 

More code and details of your DB schema would be helpful.

[adambedford]

Hi thanks for your reply!

 

I had kind of thought of that already (but much simpler) but the problem I have is that I'm repeating a list of records (businesses) down the page so I can't pin point one exactly to query whether its being followed yet or not.

 

So essentially I guess I need to decide before hand whether any of the businesses displayed on the page are being followed or not before rendering the correct markup.

 

With regards to my database: I have a table for users (User_ID, Name, Email etc.) a table for businesses (Biz_ID, Name, Email etc) and a relationships table connecting the two (Relationship_ID, User_ID, Relationship_type, Business_ID).

 

I'm now thinking about turning to AJAX to display the button. Could I make the ajax request and POST the business ID to a different script which then took the business ID and used your above code to figure out whether it's being followed already or not. This way I could make these requests on the fly and decide the right button to display for each record(business) individually. Does this sound like a good idea?

[ignace]

I wonder if this could work?

 

SELECT b.*,
       following AS (CASE (SELECT * FROM followers WHERE (id1 = u.id or id1 = b.id) and (id2 = u.id or id2 = b.id)) WHEN NULL THEN 0 ELSE 1 END)
FROM businesses b, user u
WHERE u.id = $uid;

 

In your PHP you would then just check:

 

if (0 == $row['following']) { echo '<follow/>'; }

 

Edit: silly me

 

SELECT (CASE WHEN f.follower IS NULL THEN 'Yes' ELSE 'No' END) AS following, b.*  FROM businesses b
LEFT JOIN followers f ON b.id = f.followee

[adambedford]

ignace, thank you for your reply. would you mind running through your code quickly...I'm pretty new to all this! The tables I have are 'users' 'businesses' and relationships' so I'm not quite sure I would convert your example to mine.

 

Once again, thank you and I aplogise for not understanding

[ignace]

SELECT (CASE WHEN f.follower IS NULL THEN 'Yes' ELSE 'No' END) AS following, b.*  FROM businesses b
LEFT JOIN followers f ON b.id = f.followee

 

What this code does is it selects all records from the table businesses and then left joins it with relationships. So let's assume we have 3 businesses and one user is following 2 of them:

 

businesses
1, business_name1, telephone1, ..
2, business_name2, telephone2, ..
3, business_name3, telephone3, ..

relationships
1, 1
1, 2

 

If I know perform a left join I get as a result:

 

business_id, business_name, business_telephone, .., relationships_follower, relationships_followee
1, business_name1, telephone1, .., 1, 1
2, business_name2, telephone2, .., 1, 2
3, business_name3, telpehone3, .., NULL, NULL

 

Notice the NULL these indicate businesses the user is not following so we could to this in PHP itself but MySQL has the data...

 

(CASE WHEN r.follower IS NULL THEN 'Yes' ELSE 'No' END) AS following

 

In PHP I can check if the user is following the business with:

 

echo $row['following'];//Yes|No

 

I use follower and followee to make it easier to read then to use id1, id2. You should also notice that a business does not follow a user so the followee column always contains the id of a business never that of a user therefor we don't need hard-to-read stuff like:

 

(id1 = u.id or id1 = b.id) and (id2 = u.id or id2 = b.id)

 

SELECT (CASE WHEN r.follower IS NULL THEN 'Yes' ELSE 'No' END) AS following, b.*  FROM businesses b
LEFT JOIN relationships r ON b.id = r.followee
WHERE r.follower = $uid