Well then one of my customers wanted to be able to update his OSCommerce stock list frequently, not unreasonable since he has 4000 products and 3 shops selling them, plus the website.

He has an in-store database keeping track of all the products, and conveniently they are all arranged into category and sub-category already (160 categories in all). Each of the 250 manufacturers is also in the database linked to each product. Each product also has a model name and (albeit short) description.

So not a bad start, I thought the easiest thing to do would be to write a short script to output the products in the format of an easypopulate file then simply upload it to the server. I thought this would be an ideal opportunity to move the site onto AAMAX 1.5.5 at the same time, with all its added features including easypopulate.
There would be a few complications, eg a stock number is associated with each product option, such that shoe size 41 has a different code to shoe size 42, which is obvious when you think about it, but there is nothing in OSCommerce to allow for this. It would mean adding a couple of extra fields (stock qty and product code) to the attributes, but no big deal. This means we could easily update the products in the future by updating just the qty and price for each stock code. I cheated here and left the price in the products table, putting it in the attributes table as I intended would have been a nightmare with the checkout procedure, order history and so on.

The only problem was, it quickly became obvious that the server wasn't going to be able to handle an easypopulate file this big. Even extending the server script time-out to, well, very long, it just couldn't handle it....not good for visitors to the site. I think it's due to the huge number of attributes on this particular site (about 300) and easypopulate creates a file with all 300 of these for every single product. That's 4000 * 300 entries = 1.2million, no wonder my computer was having a hard time. And excel wouldn't have handled it anyway.

What with one thing and another I decided I needed to start from scratch. The shop staff in charge of the webiste would upload a stocklist in a prescribed format every so often, and I would create some add-ons for OSC to parse this and bung it in the database. A day of fiddling later saw me with a shop with 4000 products in all the categories as I wanted.....almost.

I thought I had nearly finished but there were a few problems. Firstly, and easy to fix was the problem that some things don't go on the website... commission on finance plans to staff are on there for example, so I needed to be able to disable certain categories....again I'm surprised this isn't in OSC as standard, so I simply extended the red/green dots as for products.

That was the easy bit. I then had to make some tools for staff to be able to convert products into attributes of other products....again obvious when you think about it, you don't want every size shoe as a separate product.

So I made that, it took ages, like 3 days solid.... I had to make many new admin pages, and at the end of assigning a product as an attribute I had to delete the old product, all automatically. Then I had to disable certain features in OSC, obviously it's no good staff being able to input new product options directly, as they wouldn't have an order code and therefore would never be updated (or would crash my system). I needed to be able to convert attributes into ones with different options, whilst maintaining correspondence with codes and qtys. Then I had to add up all the attribute qtys and insert into the products table (or they would appear as out of stock prematurely).

I am still slightly apprehensive as to what will happen when old products are discontinued or new ones appear, I guess I'll just send them to a "new products!" category for moving later.....

Anyway if anyone else is working along these lines I'd be interested to hear how you did it, and if there's an easy way to do this, please don't tell me, I might have to go pull my hair out and scream a bit


jon