Replace concatinated values with values from mapping table

Databases
1

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

I have 2 mysql tables one contains category mapping relationships between a supplier and our store: Basically what we call their categories eg ~ 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 other table contains supplier item ids with the categories that the supplier has the products in. Multiple 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 item paths in the supplier list with the correct paths from our store category list.

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

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

Thanks in advance

2

storing multiple values in a single column is going to give you huge headaches

as you have discovered

no, there is no easy way to do the substitutions with sql, not with the current table design

your easiest solution would be to read the entire Category Mapping Relationship Table into your application language (php or whatever), and do the replace there

if you can redesign the table so that each column contains only one value (what is known as first normal form), then the whole thing can be handled quite easily with a rather trivial query

3

Thank you. php was actually my first idea too but I posted to a php form and they got mad at me and told me its a MYSQL deal :wink: <g> funny how that happens. Anyway. I am currently working on a php work around but greatly appreciate your reply.

4

Basically, the design you have there is messy because it would be cumbersome, if possible at all, to do what you want just using sql.

A better option for the long term, if it’s practical, would be to redesign and build your tables so that each column in a table stores only 1 value. If rebuilding your tables is not an option then you can still do what you want but using some sort of server side programming language like php.

With php, you can simply read the required table data into a multi dimension array, do any data manipulation (which is pretty straight forward) and then write the manipulated data back to the appropriate tables. If you know php or have access to someone who is a php (or other language) coder then it shouldn’t be a difficult task for them.

If you need any help with the php code, post the code you have at the time and we can try to help :slight_smile:

5

Well, my main issue with the PHP code is the concatenated field. I just am not sure how to handle that. Im working on the php code now so I dont have much to show but when I get to that concatenated field, Im going to come to a screeching halt. Give me a few hours and Ill post the code I have if I have not solved it. Thank you for your reply.

6

You’ll more than likely have to split the concatenated strings into their components.

With php you can do that very easily with the explode() function which splits a string, based on a delimeter (in this case a comma), into an array of the string components.

You’ll then have to do some sort of looping or searching of the array with your other data to do the data “massaging” you need.

7

So heres what Ive got so far. I have attached some table sql data for you to play with.

The problem with this code is

  1. Its not getting the new path for 2,3,4th level concatenated values.
&lt;?php
mysql_connect("localhost", "xxxxx", "xxxxx") or die(mysql_error());
echo "Connected to MySQL&lt;br /&gt;";
mysql_select_db("xxxxx") or die(mysql_error());
echo "Connected to Database &lt;br/&gt;&lt;br/&gt;&lt;br/&gt;&lt;br/&gt;";
 
 		//DB Cleanup
		//mysql_query("UPDATE `zzz_CWR_itemToPath` SET CWR_item_path = replace(CWR_item_path,', ',' ');");
		//printf("Items Modified: %d\
", mysql_affected_rows());
		//echo "&lt;BR /&gt;";
		
		$query="SELECT * FROM zzz_CWR_itemToPath LIMIT 4000,600";		
		$result = mysql_query($query) or die(mysql_error());
	
		while($row = mysql_fetch_array($result)){
			 
			$indivPath = explode(",", $row['CWR_item_path']);
		    $numPaths = count($indivPath);
			//if ($numPaths&gt;3){
			echo "&lt;BR /&gt;" . $row['CWR_item_ID']. "     -     ". $row['CWR_item_path']."&lt;BR /&gt;";
				for ($i = 0; $i &lt; count($indivPath); $i++){
				    $pathNum = $i;
    				echo 'Path:  ' . ++$pathNum . ' - ' . $indivPath[$i]  ;
					
					$query2='SELECT `QMS_Categories` FROM `zzz_CWR-QMS_catMapping` WHERE `CWR_Categories` ="'.$indivPath[$i].'"';	
 	
					$result2 = mysql_query($query2) or die(mysql_error());
					if(mysql_num_rows($result2) &gt; 0) {
						while($row2 = mysql_fetch_array($result2)){		
							echo " New Path: ".$row2[$i]."&lt;BR /&gt;";
					 	}
					} else {
 						 echo "---------------- No New Path Available -----------------------&lt;BR /&gt;";
					}

			}
		} 				
?&gt;

and heres some data to plug in if you want to test.

CREATE TABLE IF NOT EXISTS `zzz_CWR_itemToPath` (
  `Product to` text NOT NULL,
  `Product Path to` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `zzz_CWR_itemToPath` (`CWR_item_ID`, `CWR_item_path`) VALUES
('31971','Boat Outfitting | Accessories'),
('13528','Boat Outfitting | Display Mounts,Entertainment | Accessories'),
('39745','Boat Outfitting | Trolling Motors,Boat Outfitting | Anchors/Chain/Rope'),
('37185','Winterizing | Winter Covers,Boat Outfitting | Winter Covers');
 



CREATE TABLE IF NOT EXISTS `zzz_CWR-QMS_catMapping` (
  `QMS Categories` text NOT NULL,
  `CWR Categories` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `zzz_CWR-QMS_catMapping` (`QMS_Categories`, `CWR_Categories`) VALUES
('Accessories|Boat Outfitting Accessories','Boat Outfitting | Accessories'),
('Entertainment|Accessories','Entertainment | Accessories'),
('Anchor & Docking|Anchors/Chain/Rope','Boat Outfitting | Anchors/Chain/Rope'),
('Trolling Motors','Boat Outfitting | Trolling Motors'),
('Electronics|Electronics & Antennas|Display Mounts','Boat Outfitting | Display Mounts'),
('Winterizing|Winter Covers','Boat Outfitting | Winter Covers'),
('Winterizing|Winter Covers','Winterizing | Winter Covers');

Let me know if you see anything obvious.

8

Ok I got some good web output now I just have to write it to db… thanks for your help. I think Im almost there!.

&lt;?php
 		mysql_query("UPDATE `zzz_CWR_itemToPath` SET CWR_item_path = replace(CWR_item_path,', ',' ');");
		
		printf("Items Modified: %d\
", mysql_affected_rows());
		echo "&lt;BR /&gt;";
		
		$query="SELECT * FROM zzz_CWR_itemToPath LIMIT 4115,400";		
		$result = mysql_query($query) or die(mysql_error());
	
		while($row = mysql_fetch_array($result)){
			 
			$indivPath = explode(",", $row['CWR_item_path']);
		    $numPaths = count($indivPath);
			//if ($numPaths&gt;3){
			echo "&lt;BR /&gt;" . $row['CWR_item_ID']. "     -     ". $row['CWR_item_path']."&lt;BR /&gt;";
				for ($i = 0; $i &lt; count($indivPath); $i++){
				    $pathNum = $i;
    				echo 'Path:  ' . ++$pathNum . ' - ' . $indivPath[$i] ;
					
					$query2='SELECT `QMS_Categories` FROM `zzz_CWR-QMS_catMapping` WHERE `CWR_Categories` ="'.$indivPath[$i].'" Limit 1';	
 	
					$result2 = mysql_query($query2) or die(mysql_error());
					
					while ($row2 = mysql_fetch_assoc($result2)){
						echo " New Path: ".$row2['QMS_Categories'] . "&lt;BR /&gt;";
					}



			}
		} 				
?&gt;