display join query help

[BrickZ28]

Hello, I am a little stumped here. I have a few tables I am trying to display data with. The first is tbl_pilot which holds data such as ID numbers that come from other tables. So my issue is tbl_pilot there are 3 airframe choices tblPilotAirframeChoiceID1FK,tblPilotAirframeChoiceID2FK,tblPilotAirframeChoiceID3FK.

I am trying to display the name of these id from another table tblkp_airframe, which associates an ID with a name.

So my JOIN which displays everything in HeidiSQL

SELECT * FROM tbl_pilot 
 JOIN tblkp_sim_platform platform ON (platform.tblkpSimPlatformIDPK = tbl_pilot.tblPilotSimPlatformIDFK)
 JOIN tblkp_airframe acft1 ON (acft1.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID1FK)
 JOIN tblkp_airframe acft2 ON (acft2.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID2FK)
 JOIN tblkp_airframe acft3 ON (acft3.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID3FK)

I then run my php loop

 <?php while ($rowPending  = $stmt -> fetch()) //loops thru pending pilots WHILE1
                                      { 
                                        //looks plaform name


                               ?>
    <tr>
       <td><?php echo '<a href="mailto:' . $rowPending['tblPilotEmailtxt'] . '">' . $rowPending['tblPilotFirstNametxt'] . " " . $rowPending['tblPilotLastNametxt'] . '</a>'; ?>
      <td><?php echo $rowPending['tblkpSimPlatformtxt']; ?></td>
      <td><?php echo $rowPending['tblPilotHomeICAOtxt']; ?></td>
     <td><?php echo $rowPending['tblkpICAOTxt']; ?></td>
      <td><?php echo $rowPending['tblkpICAOTxt']; ?></td>
      <td><?php echo $rowPending['tblkpICAOTxt']; ?></td>
      </tr>
     <?php $_SESSION['id'] = $rowPending['tblPilotVatsimIDPK'];  } //this passes the users row id ?>

The issue that I have is that when I view it on the webpage instead of shoiwng the text name for each choice it only repeats choic3 all 3 times. I think it has to do with possible alias but i cant wrap my head around it. I am able to display "tblkpSimPlatformtxt" properly but there is only 1 option for that unlike the 3 for the "tblkpICAOTxt" Any help is much appreciated.

[diafol]

You're echoing $rowPending['tblkpICAOTxt'] (the same array element) 3 times. Were you really expecting different values??
Although you have JOINED tables, you have not aliased common fields you want returned. Using SELECT * is horrendously lazy - name the fields you need with an alias - it will make your life much easier.

SELECT * FROM tbl_pilot 
 JOIN tblkp_sim_platform platform ON (platform.tblkpSimPlatformIDPK = tbl_pilot.tblPilotSimPlatformIDFK)
 JOIN tblkp_airframe acft1 ON (acft1.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID1FK)
 JOIN tblkp_airframe acft2 ON (acft2.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID2FK)
 JOIN tblkp_airframe acft3 ON (acft3.tblkpAirframeIDPK = tbl_pilot.tblPilotAirframeChoiceID3FK)

To something like...

SELECT p.somefield, pl.someotherfield, a1.name AS a1name, a2.name AS a2name, a3.name AS a3name 
FROM tbl_pilot AS p 
 JOIN tblkp_sim_platform AS pl ON (pl.tblkpSimPlatformIDPK = p.tblPilotSimPlatformIDFK)
 JOIN tblkp_airframe AS a1 ON (a1.tblkpAirframeIDPK = p.tblPilotAirframeChoiceID1FK)
 JOIN tblkp_airframe AS a2 ON (a2.tblkpAirframeIDPK = p.tblPilotAirframeChoiceID2FK)
 JOIN tblkp_airframe AS a3 ON (a3.tblkpAirframeIDPK = p.tblPilotAirframeChoiceID3FK)

You don't half know how to make long table names.

  <td><?php echo $rowPending['a1name']; ?></td>
  <td><?php echo $rowPending['a2name']; ?></td>
  <td><?php echo $rowPending['a3name']; ?></td>

[BrickZ28]

Thank you for the reply I tried smething similiar to this but was getting erros and stuff but I see a small difference in what i tired and you stated, so hopefully that will work. Il let you know how it works out. Again thank you.

[BrickZ28]

Everything worked out well and I learned abit more. Joins have always beena weak point for me and something i always avoided, but i all seemed to click due to your reply. Thank you again.