fetch row above current row where.....

[glycerine]

Firstly what a great forum and site alot of the threads on here have helped me over the past couple of months create and update alot of sites.

So here is my problem,
I have a importer which imports a feed (csv file) from a supplier (no problem importing it) but the feed layout is different to wat i usually get from other suppliers. Here is the layout once imported:

Supplier table
model | partno | products_name | buy_price | rrp | vendor | product_type | category

Now my problem is that the categories are set in the models column a line above the groupings of the products in that group. Eg.

HEWLETT-PACKARD - Pavilion Series
*list of pavilion series computers*

HEWLETT-PACKARD - HP Series - xw4600
*list of hp series - xw4600 computers*

What i would like to do is take the category out from above the groups and place into the category column.

If anyone has an idea on how i would go about doing this please let me know i dont expect code (but would help) but what functions to use to go about doing this?

Thanks Craig

[diafol]

From what I can see, shouldn't the category column be a foreign key linked to a categories table? Although I can see why you can't do this if you get slightly different category strings for the same category from different suppliers (and could be difficult with importing csv files). Same could be said for vendors (should be a foreign key). You'll need some type of parser as far as I can see. You could "roll your own", but some do exist. I don't know if any of these would help:

How about:
http://www.magicparser.com/

The only thing I can think of is when your parser loops over the rows - when it comes across a single field (no commas), it stores this value as the category and uses it in the update query until a new category is found and then that is used etc etc.

[glycerine]

Thank you for your reply, I tried the online version of Magic Parser but unfortunately it didnt work how i would like it to. Sorry i probably should explain it a bit better, I currently get the feed with the following columns:

model | partno | products_name | buy_price | rrp | vendor | product_type

and the mysql table looks like this:

model | partno | products_name | buy_price | rrp | vendor | product_type | category

the csv file that i recieve looks like this:

HEWLETT-PACKARD - Pavilion Series,,,,,,,,,
CPP3660,FK913AA,HP Pavilion S3660a Intel... ,1155,1546.67,,1,,HEWLETT-PACKARD,Computer Systems
CPP6610A,FK971AA,"HP Pavilion a6610a Intel...,1288,1728.57,,5,23.01.2009,HEWLETT-PACKARD,Computer Systems
CPP6610,FK971AA,HP Pavilion a6610a Intel... ,895,1200,,5,,HEWLETT-PACKARD,Computer Systems
CPP6640A,FK908AA,"HP Pavilion a6640a Intel...,1539,2063.81,,5,23.01.2009,HEWLETT-PACKARD,Computer Systems
CPP6640,FK908AA,HP Pavilion a6640a Intel... ,1145,1535.24,,5,,HEWLETT-PACKARD,Computer Systems
CPP6710,FT958AA,HP Pavilion a6710a Intel... ,905,1314.29,NEW,0,20.02.2009,HEWLETT-PACKARD,Computer Systems
CPP6710A,FT958AA,"HP Pavilion a6710a Intel...,1330,1885.72,NEW,0,20.02.2009,HEWLETT-PACKARD,Computer Systems
CPP6730,FT960AA,HP Pavilion a6730a Intel... ,1079,1583.49,NEW,0,20.02.2009,HEWLETT-PACKARD,Computer Systems
HEWLETT-PACKARD - HP Series - xw4600,,,,,,,,,
CPX4605,KV700EP,HP xw4600 80+ E6850...  ,2075,2350,,30,,HEWLETT-PACKARD,Computer Systems
CPX4610,NG992PA,HP xw4600 80+ E8400... ,1799,2050,,16,,HEWLETT-PACKARD,Computer Systems
HEWLETT-PACKARD - HP Series - xw6600,,,,,,,,,
CPX6600,FE022AW,HP xw6600 80+ Dual Xeon... ,3575,4050,,3,,HEWLETT-PACKARD,Computer Systems

As you can see where is says "HEWLETT-PACKARD - HP Series - *****,,,,,,,,," that is the category for the products below. What i am wanting is for that to go into the categories column in the mysql database for each product below it.

The idea i had was for each row:

SELECT FROM suppliers_table (model) the first row above the current row where partno equals null and insert into category for the current row.

Hopefully thats a bit clearer.

Thanks

[diafol]

Yeah, fine. There seem to be 10 fields. Unfortunately it's past my bedtime and my brain's a bit mashed right now, but I think you could do something like this:

1) loop the file line by line, place it into an variable and explode it into an array.
2) your array should be something like $myarray[0] to $myarray[9] 3) check if $myarray[1] to [9] are blank - if so this $myarray[0] is your $category .
4) for each product, where part 3) is false, create an sql string, e.g.

$sql = "INSERT INTO table SET model = '{$my_array[0]}', partno  = '{$my_array[1]}', products_name  = '{$my_array[2]}', buy_price  = '{$my_array[3]}', rrp  = '{$my_array[4]}', ...., category ='{$category}'";

5) run the querystring
6) continue to loop.

Doubtless there are more sophisticated solutions.

[glycerine]

Thanks alot that makes alot of sense and although my php skill are still very limited ill give it ago and see how i get on.