Show the total numbers of invites referred by my invites

Question

In a part of the project I am working on, I am required to show the total number of invites made by me and the total number of invites made by my OWN invites also.

I have a referral table with the following fields

id (auto_increment) user_id (Unique) and ref_user_id

The code below is the code which displays the total number of invites made by me and it works fine

$sql = "SELECT COUNT(id) AS referrals FROM referrals WHERE ref_user_id = :ref_user_id";
$stmt = $link->prepare($sql);

//Bind the provided user id to our prepared statement.
$stmt->bindValue(':ref_user_id', $_SESSION["id"]);

//Execute.
$stmt->execute();
  
//Fetch the row.
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$referrals = $row["referrals"];

However, I am completely lost on writing the code which displays the total number of invites made by my invites, please how do I go about this.

PS : I am new to stackoverflow and really don't know how to use the editor so well.

Answer 1

I'm assuming that the field 'ref_user_id' is the person being invited.

The following query should give you the the total number of invites made by your invites. Feel free to modify it as needed.

Edit, based on what worked for you :

SELECT COUNT(id) AS indirect_referrals from referrals WHERE ref_user_id IN (SELECT user_id FROM referrals WHERE ref_user_id = :user_id)