MySQL Query Help - Replacing Multiple Sub-Strings

Replacing values in a string with values from another table...

[ChainsawXIV]

I'm trying to form a query which will find multiple sub-strings in a string from one table which match values from another table, and replace them with corresponding values from the second table. For example, if these are my two tables:

items_table 
Item Parts 
"Kit" "A,B,C" 
"Widget" "A,D" 

parts_table 
Part Name 
"A" "Bolt" 
"B" "Washer" 
"C" "Nut" 
"D" "Flange" 

The query I'm trying to work out would return:

Item Parts 
"Kit" "Bolt,Washer,Nut" 
"Widget" "Bolt,Flange" 

I've tried several unsuccessful approaches to this problem using MySQL's built in Replace function in combination with regular expressions, but haven't been able to get what I need. The stumbling block seems to be matching and replacing an arbitrary number of patterns within the string, but at this point I'm not even sure I'm using a valid approach. Here's my latest attempt, which obviously has some problems:

SELECT items_table.item,REPLACE( items_table.parts, parts_table.part, parts_table.name ) AS 'Parts'  
FROM items_table,parts_table  
WHERE items_table.parts REGEXP parts_table.part

I'm hoping one of you good folks can put me on the right track.

[vincevincevince]

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.

[ChainsawXIV]

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.)

[vincevincevince]

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.

[ChainsawXIV]

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

[ZydoSEO]

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.