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.
