1

Let's say you have a database with two tables named "clients" and "referrals".

TABLE clients has two columns: "id" and "name".
TABLE referrals also has two columns: "id" and "referred_by"

Both "id" columns are PRIMARY_KEY, AUTO_INCREMENT, NOT_NULL

TABLE clients has three rows:

1 | Jack  
2 | Frank  
3 | Hank

TABLE referrals also has three rows:

1 | 0  
2 | 1  
3 | 2

Meaning, Jack is client 1 and was referred by no one; Frank is client 2 and was referred by Jack; Hank is client 3 referred by Frank.

The SELECT command I used to display the meaning above was:

mysql_query("SELECT clients.id, clients.name, referrals.referred_by FROM clients INNER JOIN referrals ON clients.id=referrals.id");

while ($row = mysql_fetch_array($result))
{
    echo $row['id'] . " " . $row['name'] . " " . $row['referred_by'] . "<br>";
}

Which outputs:

1 Jack 0  
2 Frank 1  
3 Hank 2

Now the real question is:

How should I modify the code so that it outputs the name of the referrer instead of their id?

Meaning, it should look like this:

1 Jack  
2 Frank Jack  
3 Hank Frank

Thanks in advance ~

Edit: Make sure to mention how I should update the array as I'm lost on how I should update that whole echo line.

no-name
  • 37
  • 1
  • 3
  • 8
  • Why do you need 2 tables for this? One should be enough, with columns: `id, name, referred_by`. – ypercubeᵀᴹ Aug 13 '11 at 21:36
  • If I had 2 tables, it would make more sense to have in table `referrals` both the `id` and `referred_by` columns as FOREIGN KEYs to `clients.id` – ypercubeᵀᴹ Aug 13 '11 at 21:39
  • And do not even store the `1,0` record. Client `1,Jack` has not been reffered, so no record for him in the `referrrals` table. – ypercubeᵀᴹ Aug 13 '11 at 21:41
  • I simplified my real world project to a simple example so that it would be easier for someone to jump in and help. Also it made it easier for me to learn. I did try a bunch of different variations but I kept getting errors and after a couple frustrating hours I decided to just post the question on here. – no-name Aug 14 '11 at 00:28

1 Answers1

4

You're almost there - you just need to join back to the clients table a 2nd time to get the referrer name:

mysql_query("SELECT clients.id, clients.name, rclients.name as referred_by
FROM clients 
INNER JOIN referrals ON clients.id=referrals.id
LEFT JOIN clients as rclients ON referrals.referred_by = rclients.id");

while ($row = mysql_fetch_array($result))
{
    echo $row['id'] . " " . $row['name'] . " " . $row['referred_by'] . "<br>";
}
no-name
  • 37
  • 1
  • 3
  • 8
Derek
  • 21,828
  • 7
  • 53
  • 61
  • What to u mean by Update the array ? – anasanjaria Aug 13 '11 at 13:11
  • I dont' know what you're asking for with "updating the array". You were asking how to retrieve the names of the referrer, which the query supplied will do. – Derek Aug 13 '11 at 13:21
  • I'm asking how to display what I selected. Clearly, I'm using this line to display what was selected: echo $row['id'] . " " . $row['name'] . " " . $row['referred_by'] . "
    "; I'm simply asking how to update that line so it works. It's not like copy and pasting your code fixes everything. In fact, the whole first row doesn't even display. If I were to copy it, it would only show entries starting after the first client.     – no-name Aug 13 '11 at 17:44
  • Alright it works except the first row doesn't display I think because the client has no referrer? Anyway to include the first row? – no-name Aug 13 '11 at 21:31
  • @Derek: `INNER JOIN clients as rclients` should be `LEFT JOIN clients as rclients` so `'Jack'` is still shown - and as referred by none (`NULL`) – ypercubeᵀᴹ Aug 13 '11 at 21:34