Query-generated tables with many-to-many data fields, best practice?

Question

You have two hypothetical tables:

TABLE 1
[id]  [item]  [amount]  [cost]
 1     hat     20        10
 2     shoe    7         45
 3     belt    2         25

TABLE 2 (many to many)
[item]  [color]
 hat     blue
 hat     red
 hat     yellow
 shoe    black
 shoe    white
 etc.    etc.

and when you run a query, you want to output a list that includes all of both data like this:

[ITEM]  [AMOUNT]  [COST]  [AVAILABLE COLORS]
 hat     20        10      blue, red, yellow
 shoe    7         45      black, white
 etc.

If the colors weren't present, it'd be a single query to grab all that info from one table and process the array in a loop. But to accommodate TABLE 2 I can think of two ways to do it:

Brute Force: run a query for every single return to get the data from TABLE 2, add commas, and insert it into the results array from TABLE 1, then output HTML table

Ugly Workaround: add a new column to Table 1 and periodically update with strings of data from Table 2 behind the scenes

...there's a better way, right?

Answer 1

If you are working with MySQL, the GROUP_CONCAT function might interest you.

See for instance this question : Can I concatenate multiple MySQL rows into one field?

Else, the "brute force" solution you described is generally the one that's used -- a possible optimization being to do only 1 query to get lines from the second table that correspond to all lines from the first table at once.

For instance, you'd :

• do the query to get that data from the first table
• do one query to get all data from the second table that correspond to data from the first table -- something like select * from table_2 where item in ('hat', 'shoe', 'belt')
• use a loop on the PHP side to "re-attach" results from the second query to the results from the first one

With this solution, you'll do a bit more work on the PHP side, but only 2 queries -- instead of 1 + (number of lines returned from the first query) ; which is generally much better, when you have lots of lines returned from the first query.