|
|
Posted by Vince LaMonica on 10/20/42 11:19
I sent this note off to the php-db list last night, but the more I thought
about it, the more I figured this was a general looping question rather
than a mysql-specific one.
I am attempting to take a CSV file that has order header and line item data on
each line and split it into mulitple insert queries into a mysql db. Each line
in the CSV file may only be one order, though it is common for there to be more
than one item ordered, so I need to be able to loop to insert the line item
values into the proper line item table. I also need to build a counter for a
value that is not provided in the CVS for each line item.
Here is a sample of a few lines of the CSV:
1110,6/20/2005,Jan Doe,1234 Spring St.,Anytown,PA,17033,0618456990,22.50,1,The Sample Book
1114,6/22/2005,Jon Smith,888 Main St.,Big City,CA,92648,0444409444,19.95,1,Coloring Book
1114,6/22/2005,Jon Smith,888 Main St.,Big City,CA,92648,9834119933,4.40,1,Picture Book
1114,6/22/2005,Jon Smith,888 Main St.,Big City,CA,92648,9482222922,59.99,4,Coffee Book
In the above file, the last 4 fields [item_num, cost, quantity, title]
belong in a line_items table. The first number, the order_number, also
goes into the line_items table, as well as the order_header table. The
contact info for each customer also goes into the order_header table. I do
not want duplicate entries in the order_header table, so I can't just to a
simple loop through each line in the text file and do an insert. I need to
be able to group an order by the order_number [the 1st field] and insert
the correct number of rows in both tables. I also need to create a counter
per order showing which line item number each item is. Eg: the Coloring
Book would be assigned a 1, the Picture book a 2, and the Coffee Book a 3
for order #1114. The Sample Book in order #1110 would be given a 1, since
it is the first [and only] item in that order.
I have been successful in assigning each value to a varable and looping through
the file via:
while ($line = fgets($fp,1024))
{
$i++;
if ($i > 1) { // using 1 because CSV includes a header row
list($order_number, ...) = csv_explode($line);
[i am using an Excel generated CSV with double quotes around each value and so
i have a csv_explode function setup to properly extract each value; also the
real CSV has about 2 dozen fields - i just cut it down to its basics for the
example here]
Doing 2 inserts here and closing the loop is obviously not the answer,
since I get duplicate header rows, and I haven't built a counter for the
line_item's table's line counter field. The primary key in the line item
table is a combo of the order_number and the line_counter. Each fresh
order_number needs to reset the line_counter to 1, until all line items
for that order are inserted.
I am having difficulty figuring out how to loop through the CSV to do the
inserts and create a line item counter. Any tips?
TIA,
/vjl/
Navigation:
[Reply to this message]
|