PHP or C# script to parse CSV table values to fill in one-to-many table
- by Yaaqov
I'm looking for an example of how to split-out comma-delimited data in a field of one table, and fill in a second table with those individual elements, in order to make a one-to-many relational database schema. This is probably really simple, but let me give an example:
I'll start with everything in one table, Widgets, which has a "state" field to contain states that have that widget:
Table: WIDGET
===============================
| id | unit | states          |
===============================
|1   | abc  | AL,AK,CA        |
-------------------------------
|2   | lmn  | VA,NC,SC,GA,FL  |
-------------------------------
|3   | xyz  | KY              |
===============================
Now, what I'd like to create via code is a second table to be joined to WIDGET called *Widget_ST* that has widget id, widget state id, and widget state name fields, for example
Table: WIDGET_ST
==============================
| w_id | w_st_id | w_st_name |
------------------------------
|1     | 1       | AL        |
|1     | 2       | AK        |
|1     | 3       | CA        |
|2     | 1       | VA        |
|2     | 2       | NC        |
|2     | 1       | SC        |
|2     | 2       | GA        |
|2     | 1       | FL        |
|3     | 1       | KY        |
==============================
I am learning C# and PHP, so responses in either language would be great.
Thanks.