title: php mysql selecting columns and compare with 3 values in different tables
body: <p>I have 4 mysql tables holding interrelated data</p>
  
  <ul>
  <li>playersTable</li>
  <li>participationTable</li>
  <li>hotelsTable</li>
  <li>bookingsTable</li>
  </ul>
  
  <p>I'm trying to retrieve the booking of players keeping in mind that some rooms might have 2 companions and some will not</p>
  
  <p>MYSQL Tables physical design are structured at this way </p>
  
  <p><strong>playersTable</strong> </p>
  
  <ul>
  <li>playerID</li>
  <li>playerFullName</li>
  <li>playerCountryID</li>
  </ul>
  
  <p><strong>participationTable</strong> </p>
  
  <ul>
  <li>p_id</li>
  <li>championshipId</li>
  <li>playerId</li>
  <li>countryId</li>
  <li>status</li>
  <li>booking_status</li>
  </ul>
  
  <p><strong>hotelsTable</strong></p>
  
  <ul>
  <li>hotelId</li>
  <li>hotelName</li>
  <li>championshipId</li>
  </ul>
  
  <p><strong>bookings</strong></p>
  
  <ul>
  <li>b_ID</li>
  <li>player1_id</li>
  <li>player2_id</li>
  <li>hotelId</li>
  <li>arrivalTime</li>
  </ul>
  
  <p>What I'm trying to do is <code>SELECT</code> all bookings and list them in a table where it can show both players, number 1 and number 2. </p>
  
  <p>I'm using this query to get the results: </p>
  
  <pre><code>    SELECT bookingsTable.*, playersTable.*, participantsTable.*, hotelsTable.* 
      FROM bookingsTable 
      LEFT JOIN participationTable 
      ON bookings.player1_id= participants.p_id 
      LEFT JOIN playersTable ON participants.playerID = players.playerID 
      LEFT JOIN hotelsTable ON bookings.hotelId = hotels.hotelId
  </code></pre>
  
  <p>I'm only getting one player, so I added I added another query that chooses the <code>player2_id</code> but when looping through the results, I cannot get it to work correctly, but I can get the player2_id number and not the name.</p>
  
  <p>This is my PHP code where it loads the results, a kind help is always appreciated. </p>
  
  <pre><code>&lt;?php do { ?&gt;
         &lt;tr&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['id']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['playerFullname']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['playerFullname']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['hotelName']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['arrivalDate']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['arrivalTime']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['departureDate']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['departureTime']; ?&gt;&lt;/td&gt;
           &lt;td&gt;&lt;?php echo $row_booking_retrival['Notes']; ?&gt;&lt;/td&gt;
         &lt;/tr&gt;
         &lt;?php } while ($row_booking_retrival = mysql_fetch_assoc($booking_retrival)); ?&gt;
  </code></pre>
  
  <p>Thanks in advance. </p>
  
  <p>Find the physical design and intended results at this link. </p>
  
  <p><a href="http://s18.postimg.org/gjsx4frfd/example.jpg" rel="nofollow">http://s18.postimg.org/gjsx4frfd/example.jpg</a></p>