Scenario: Optimizing code with LEFT JOIN?

[Velocd]

Say I have the following setup:


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


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


$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:

$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]

[Dean C]

Why not have both queries and loop through them and create an array. Then work with it from there..?

[Velocd]

Well, that's what I'm doing in the first example. I'm just wondering if there is a way to do it in 1 query. :[]

[Dean C]

You're doing a query loop there. For every row you're running two queries. What I meant was to run both queries. Loop through them both and place their contents in two arrays. That way you have two queries?

[Boofo]

Why don't you use just one table instead of having 2 tables? That way you could pull all the info with 1 query. ;)

[Velocd]

In my real code, the tables are much more complex and so is how the data is handled. One table wouldn't work at all.

As for creating two arrays from two loops, that would lead me to having arrays with arrays with arrays in them, and all sorts of messy goodness.

A suggestion by a member of Sitepoint (http://www.sitepoint.com/forums/showthread.php?p=1149531) was to create a 3rd table for containing the relations, and then using 1 query to access all the required data through an inner join.