splitting CSV rows into multiple SQL inserts?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux