PHP and MySQL, Using a foreign key to get data from another table where it originates from

This question involves 3 tables that I have in PHPmyAdmin called:

• tblShoots
• tblMemberOnShoot
• tblMember

I need to run a query on a users dashboard which tells them, which photoshoots they have been to.The tblmemberOnShoot table is shown below, if I was to click on through an entry under fldShootID (which is a foreign key), it would take me to tblShoots which holds the shoot details, in there I need to pull out fldShootLocation.

At the moment my query is shown below which doesn’t entirely give the output needed, I need help with the join?

<?php 
        $query = "SELECT * FROM `tblMembersOnShoot` WHERE `fldMemberID` = 1";

        $result = $conn -> query($query);

        while($row = $result -> fetch_assoc()) 
            {
                echo $row['fldShootID']."<br>";

            } 
    ?>

Output on the page:

Answer

As you said, a JOIN is the way to go. Here’s what I’d suggest:

SELECT fldShootLocation FROM tblMember
LEFT JOIN tblMembersOnShoot
ON tblMembersOnShoot.fldMemberID = tblMember.fldMemberID
LEFT JOIN tblShoots
ON tblShoots.fldShootID = tblMembersOnShoot.fldShootID
WHERE tblMember.fldMemberID = 1