[Velocd]

Say I have the following setup:

| table: athlete |
+--------------------------------+
| id  |  name   |   sports
+--------------------------------+
| 1   |  'John'   |   '1, 2'


| table: sport |
+----------------+
| id  |  title
+----------------+
| 1   |  'soccer'
+----------------+
| 2   |  'track'

PHP Code:

$sql = "SELECT name, sports 
    FROM `athlete`";

$result = mysql_query($sql) or die('Invalid query: ' . mysql_error());

while ($athlete = mysql_fetch_array($result))
{
    $sql = "SELECT title 
        FROM `sport` 
        WHERE id IN ($athlete[sports])";
        
    $result = mysql_query($sql) or die('Invalid query: ' . mysql_error());
    
    $size = mysql_num_rows($result);
    $i = 0;
    
    while ($sport = mysql_fetch_array($result))
    {
        $sports .= $sport['title'] . ($i < $size ? ' & ' : '');
        
        $i++;
    }
    
    echo "$athlete[name] enjoys $sports";
} 


Unless I made a mistake, which is possible since I haven't tested the above code, it should print:

John enjoys soccer & track

My problem is I don't want to call a second query inside the first while loop.

If I had only 1 sport, say soccer, inside the `sports` field for row 1 of table `athlete`, I could do the following:

PHP Code:

$sql = "SELECT athlete.name, athlete.sports, sport.title AS sport_list
    FROM `athlete` 
    LEFT JOIN `sport` 
    ON (athlete.sports=sport.id)";

$result = mysql_query($sql) or die('Invalid query: ' . mysql_error());

while ($athlete = mysql_fetch_array($result))
{
    echo "$athlete[name] enjoys $athlete[sport_list]";
} 


Returning:

John enjoys soccer

This only works with one sport though. Is there someway to use a LEFT JOIN and grab the title for all sports whose IDs are located in the athlete's `sports` field?

Maybe return a subarray containing the values?

I understand I could easily insert the names of the sports into the `athlete` table, but in my real code things are much more complicated, and I might have the sport title change, therefore I would have to search every row in the `athletes` table that contains the prior sport name(s) and update it to the new name.

Sorry if my wording is confusing, the actual concept isn't, and hopefully there is a way to simply this process to my needs.

[originally posted at Sitepoint]