Andrew Heath Andrew Heath - 1 year ago 29
PHP Question

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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download