Hi all,
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 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. Any tips? Is this something that should be posted on PHP-General
instead?
TIA,
/vjl/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
