I'm not sure you can do that - I think you need to retrieve the query as multiple rows (one part per row) and use something like Perl or PHP to add the data together within a single row.

Thanks for the quick reply. :)

I was hoping to avoid that solution, obviously, though it will end up being a simpler one I expect. I have some performance concerns with the approach given my data, but they would probably apply to doing it within SQL anyway, even if it were possible.

Is there perhaps something I can do to store arbitrary arrays in MySQL more elegantly and accessibly? I've always worked around the issue with delimited lists in the past, but it seems like there should be a better, faster solution of some kind.

(I work with PHP if it matters.)

Multiple related rows in the items_table is the recommended solution:

items_table
==============
item ¦ part
-----+-----
Kit. ¦ A
Kit. ¦ B
Kit. ¦ C
Widg ¦ A
Widg ¦ D

SELECT `parts_table`.`part` FROM `parts_table`,`items_table` WHERE `parts_table`.`part` = `items_to_parts`.`part` AND `items_table`.`item` = 'Kit.'

I also recommend that you start using numeric (auto_incremented, primary key indexed) IDs and a 3-table system:

items_table:
item_id ¦ itemname
--------+----------
1 ______¦ widg
2 ______¦ kit
 
parts_table:
part_id ¦ partname
--------+----------
1 ______¦ Bolt
2 ______¦ Washer
 
items_to_parts table (3rd table):
item_id ¦ part_id
--------+---------
1 ______¦ 1
1 ______¦ 2
2 ______¦ 1

SELECT `parts`.`partname` FROM `items_to_parts`,`items_table`,`parts_table` WHERE `items_table`.`item_id` = `items_to_parts`.`item_id` AND `parts_table`.`part_id` = `items_to_parts`.`part_id` AND `items_table`.`itemname` = 'Kit';

That system is much more efficient with a larger database of parts and items - however with only a handful your system will be workable.

Either way, once you have the SQL done, from PHP:

<?php
//connect to and select db first
$dh=mysql_query($sql); //assume $sql has the statement
$partlist="";
while ($part=mysql_fetch_assoc($dh))
 {
 $partlist[]=$part['partname'];
 }
print implode(", ",$partlist);
?>

That should give you a comma separated output as you requested in your first post.

Thanks, I'll take that for a spin. :)

Vince is right... Using a single column in a table to store multiple relationships as a comma delimited string screams, "Make a new table." Looks like you have items (table), parts (table) and relationships where an item can have many parts and a part can be used to make many items. This is a many-to-many relationship. So you need and Item_Parts table as Vince suggested with Item_ID (Foreign Key back to Items table) and Part_ID (Foreign Key back to Parts table).

Having this kind of structure makes maintaining your DB much easier in the long run.