PHP/Mysql Replace concatenated values in DB and show result

Summary: to replace concatenated values in one table with corrected values

[makenoiz]

Summary: to replace concatenated values in one table with corrected values from another table and return the results to php to output

Details:
I have 2 mysql tables one contains category mapping relationships between a supplier and our store: Basically what we call their categories Please note that "~" denotes sub category level:

Category Mapping Relationship Table

Supplier Cat..........| Our Cat.....
dogs~leashes~long.....| pets~walking
dogs~leashes~long.....| pets~travel
dogs~leashes~short....| pets~walking
dogs~leashes~nylon....| pets~walking
dogs~feeding .........| pets~feeding

The second table contains supplier item ids with the categories that the supplier has the products in. If the product resides in Multiple categories the categories are concatenated in the same field with a ','.Such as the following:

Supplier Item Table

Supplier item ID...| Supplier item Categories
28374 ............| dogs~leashes~long,dogs~leashes~nylon

My task is to replace the Supplier item Categories Path in the supplier table with our Path from our Category Mapping Relationship Table

So the result of the query/function Im trying to build for the above data modification would be the output of the column New item Category below:

Mysql result would be

Supplier item..| Supplier item Categories ..............| New item Categories
28374 ........| dogs~leashes~long,dogs~leashes~nylon ..|pets~travel,pets~walkin

g

PHP out put would be

28374 [TAB] pets~travel,pets~walking
28375 [TAB] whatevera,whateverb
28376 [TAB] whatevera,whateverb

Im not sure where to start and if anyone can even help me with psuedo code, that would be a great kick off for me. thanks in advance.

Im not sure how to handle the concatenated field and I would appreciate any help at all

[enigma1]

You could use an array to hold the supplier - store relationship. The array keys could specify the supplier and the values the store. That's for 1 to 1 relationship to simplify things and obviously you need to rearrange the table.

$cpath_array = array {
'dogs~leashes~long' => 'pets~walking,pets~travel',
'dogs~leashes~short' => 'pets~walking',
.....
};

Then for the items you create an array for each ID and match it against the cpath_array. $items_array is the supplier's data that hold the item identifiers and his categories.

$final_array = array();

foreach($items_array as $id => $tmp_path) {
$tmp_array = explode(',', $tmp_path);
$store_array = array();
foreach($tmp_array as $key => $value) {
$store_array = array_merge($store_array, explode(',', $cpath_array[$key]);
}
// At this point you have the $store_path string the $id and the supplier path, so you could build the entry into your final array.
$final_array[] = array('id' => $id, 'supplier_path' => $tmp_path, 'store_path' => implode(',', array_unique($store_array));
}

I haven't tested anything but it should give you an idea. At the end should be simple enough to perform the database insertions from the $final_array.

If you need to correspond category identifiers against the category names you could use the the final_array along with the appropriate separator (~).