title: PHP/MYSQL: how to loop matching results from a query already inside a while loop
body: <p>I'm very new to PHP/MYSQL and find this quite difficult to explain, so hopefully someone will understand what I'm trying to do.  I have a database that collects information on artists and songs.  I want to be able to link the artists to the song and display the "credit" with the song information.</p>
  
  <p>I have a database with tables similar to this:</p>
  
  <blockquote>
    <p><strong>Artist</strong>
    | artist_id, artist_name</p>
    
    <p><strong>Credits</strong>
    | credit_id, credit_name  </p>
    
    <p><strong>Song</strong>
    | song_id, song_name</p>
    
    <p><strong>Credit_To_Artist</strong>
    | credit_id, artist_id, song_id</p>
  </blockquote>
  
  <hr>
  
  <p>Example data:</p>
  
  <blockquote>
    <p><strong>Artist</strong>
    | 2, Peter Mark</p>
    
    <p><strong>Artist</strong>
    | 5, Mette Christiansen</p>
    
    <p><strong>Credits</strong>
    | 1, Producer  </p>
    
    <p><strong>Credits</strong>
    | 2, Writer  </p>
    
    <p><strong>Credits</strong>
    | 3, Vocalist  </p>
    
    <p><strong>Song</strong>
    | 23, The Game</p>
    
    <p><strong>Credit_To_Artist</strong>
    | 1, 2, 23</p>
    
    <p><strong>Credit_To_Artist</strong>
    | 2, 2, 23</p>
    
    <p><strong>Credit_To_Artist</strong>
    | 3, 5, 23</p>
  </blockquote>
  
  <hr>
  
  <p>I have created a page "<strong>song.php</strong>" that displays information on each song using mysql_real_escape_string to get the song ID from the URL:</p>
  
  <pre><code>$id = mysql_real_escape_string($_GET['id']);
  
  if (!$id) {
      die('Please provide an id!');
  }
  
  $query = "SELECT * FROM `Credit_To_Artist` AS c2a 
  INNER JOIN `Credits` AS cr ON cr.credit_id = c2a.credit_id
  LEFT OUTER JOIN `Artist` AS a ON a.artist_id = c2a.artist_id
  LEFT OUTER JOIN `Song` AS s ON s.song_id = c2a.song_id
  WHERE c2a.song_id = $id";
  
  $res = mysql_query($query);
  
  $row = mysql_fetch_assoc($res);
  </code></pre>
  
  <p>The issue I'm having is I want to be able to list all of the artists linked to that song, and all of their credits in brackets next to it.  Since there are more than one artist linked to each song, and most of them have more than one credit (producer, writer, vocalist etc), I have no idea how to write a loop function that shows both of these.  Below is my attempt to show what I mean, although it obviously doesn't work:</p>
  
  <pre><code> while ($row = mysql_fetch_array($res)) {
              $artist = $row[artist_name];
  
          echo "$artist";
  
          while ($row = mysql_fetch_array($res)) {
              $credit = $row[credit_name];
              echo "$credit";
              }
          echo "&lt;br /&gt;";
      }
  </code></pre>
  
  <p>This is what I'd ideally like to achieve from the example data above:</p>
  
  <blockquote>
    <p><strong>Song:</strong>  The Game</p>
    
    <p><strong>Credits:</strong>  Peter Mark (Producer, Writer)
    Mette Christiansen (Vocalist)</p>
  </blockquote>