asked on

Create rows for exploded string

Say I have this in Table 1
Category 1/Category 2/Category 3

Would it be possible without getting too convoluted to take that string and look at is if it was
Category 1
Category 1/Category 2
Category 1/Category 2/Category 3

Grab the ID's for those 3 entries from Table 2, and insert into Table 3 using the ID from Table 1 and the ID from Table 2

Terry Woods

It looks possible to do it like this:

CREATE TABLE IF NOT EXISTS `test_cat` (
  `category` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test_cat`
--

INSERT INTO `test_cat` (`category`) VALUES
('Category 1/Category 2/Category 3'),
('Category A/Category B/Category C');

SELECT category, substring_index(category, '/', 1) as desired_value FROM `test_cat` 
union
SELECT category, substring_index(category, '/', 2) FROM `test_cat` 
union
SELECT category, category FROM `test_cat`

Open in new window

Result:

category                                 desired_values
Category 1/Category 2/Category 3	Category 1
Category A/Category B/Category C	Category A
Category 1/Category 2/Category 3	Category 1/Category 2
Category A/Category B/Category C	Category A/Category B
Category 1/Category 2/Category 3	Category 1/Category 2/Category 3
Category A/Category B/Category C	Category A/Category B/Category C

Open in new window

Terry Woods

Note also that "union" will automatically remove duplicate values from the results. This might be a good thing, but if not you can use "union all"

Gary

ASKER

It's a bit more complicated than that
I've built a fiddle here
http://sqlfiddle.com/#!2/88a0db

What should happen is I get three entries in the product_categories table like so
product_category_id category_id
1 1
2 2
3 3

Terry Woods

I'm still somewhat unclear on whether your test data is similar to your real data. For example, if you have the category_name value 'Category 1/Category 2/Category 3' then do the category_id's 1, 2 and 3 get extracted from the end of each category in the category_name? Or are they sequentially generated, or something else?

Gary

ASKER

Example is wrong it should end up with the following in product_categories
product_id category_id
1 1
1 2
1 3

Single product with ID's of the corresponding three category entries in the category table - category1, category1/category2 and category1/category2/category3

After my initial this would be great thought I realised I need to do other things with the categories that just wouldn't be possible in an SQL statement.
But in case you have any ideas I'll leave it open a while longer

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gary

ASKER

Not sure what is happening here but seems to be working.