A bit of a logic problem.....

[eluser]CI Freak[/eluser]
Hello,

I'm trying to figure out the best way of building a stock control system for a project im working on. Basically `Products` can have a set of options attached to them, so if the product was a t-shirt, you could have

Size: Small, Medium, Large
Colour: Red, Green, Blue, Black, Orange
Material: Cloth, Nylon, Whatever, etc....

I need to be able to attach a stock value to each possible combination, as well as a unique code, something like this:


Small : Red : Cloth : = Stock:5 Code:abc
Small : Green : Cloth: = Stock:0 Code:123
........................
Large : Orange : Nylon: = Stock:121 Code:x1x43



I've tried one method where i generate every possible combination of options, in ascending order: and create a db record for every combination.

The only problem is when the user has a large number of options , like 5 options, each with 12 possible variations, i get timeout errors when generating a form to input this data, memory limit issues, a huge database base table because every option that doesnt have stock entered has to be marked as unlimited and/ or set to the default amount.

I figure there must be a better way of handling this problem, but im not sure where to go from here.

[eluser]d1a8lo24[/eluser]
Most shopping carts that i know an use usually use separate tables for almost everything.

Example you would create a table for the main product whatever that may be. This will just have the basic info from description, sku, list price, sale price, etc...

Then you will create another table for options and there you will have option name, price, weight, sku, stock etc... what ever fields it may need to describe an option.

Now remember to ad ids like item_id and then to reference an option you will need to fields and options_id to keep this separate and an item_id field in the options table.

Then you just join them by item_id here is where you get creative in how you arrange the output.

In this case something that I would do will be 2 functions one to get the items and another to get the options but you will only need to call the items function to get everything.

example will be in the loop since booth functions will return an array while you're looping through the items you can add a var like item->options = $this->get_options(item_id) now when you output your result you will have an options array or nested array in your items array.

Hope this make sense.

Pretty sure that's what you have just wanted to point this out.

In return this will help with the big data set of possible variations by using a filter in the query for the options you will get exactly the options for that item.

Anyway most shopping carts that I know like cartx, sunshop and joomla's shopping cart have kind of the same database structure.

If you just want to get rid of the errors that you're having then just allocate more memory in the php.ini file or through a setting.

Here is one thing that I'm having a little trouble how much data are you outputting that you run out of memory? I deal with big chunks of data and i have never had problem. The only time I had problems was when I was manipulating images. so there might be something wrong with your code that keeps n looping the data if is not a big data set.

I have been able to output over 1000 options in a select tag with no problem it takes a few seconds to populate but that's it.

Anyway I hope I was able to help a little.

[eluser]darrentaytay[/eluser]
I agree with above - you want a database structure a little something like (obviously yours will have alot more information, this is a basic example):

Products table
--------------
id
price
name
code

Options table
-------------
id
option

Product_options table
---------------------
id
product_id
option_id
value

The products table should share information common to every product.
The options table should store all your options. If you make an entry called "Size" in this table, that will then be applicable to every product you have which has size

The product options table is where it all happens. If you had 3 different sizes, you would have 3 different entries here:

id - 1
product_id - 1
option_id - 1
value - Small

id - 2
product_id - 1
option_id - 1
value - Medium

id - 3
product_id - 1
option_id - 1
value - Large

[eluser]CI Freak[/eluser]
Thank you for your help d1a8lo24 and darrentaytay.

I already have the DB structure in place, something like this.

Product table (id field: `pid`)

option groups (linked via pid field in option groups and product table)

option values (linked via groupid in values and groups table).


Product <- option group(size) <- value1(small), value2(medium), value3(large)
<- option group(colour) <- value1(red), value2(green), value3(blue)


The reason I am running out of memory and getting timeouts is because one part of the application requires that you output all stock information on one screen, so a form is generated with input boxes for each and every possible combination of options.

the stock table is laid out something like this

selector | stockval | unlimited | code

570X112-443-132 | 15 | 0 | ABC123

the above record translates to the following:

Product ID 570
options:
SIZE: Medium (value id 112);
COLOUR: Red (value id 443);
MATERIAL: Cloth (value id 132);

The customer has 15 Medium, Red cloth shirts in stock, and this unique products id is ABC123.

The customer can add as many option `groups` as they like (size, colour, material etc..) and as many `options` as they like (small, medium, large OR x-small small medium large x-large xx-large).

before the customer add's the product to their cart, the system checks all of the options they have selected, arranges them in ascending order and look's up the stock from the table, if the product is out of stock then the user gets shown a message and the product will not be added to the cart.

it just seems like a really cumbersome/heavy way to achieve this, not to mention the more options you add, the bigger the db and the longer the load time in certain areas of the system. Keep in mind alot of people who use this system have products with like 6 or 7 option groups, each with at least 15 values in some cases, and the system needs to be able to have stock for any, and every given possible combination of those options...

[eluser]darrentaytay[/eluser]
I think that's to be expected if you are going to output so much data at once? Isn't there scope to paginate it by product type or something?

[eluser]InsiteFX[/eluser]

Code:

// Products
DROP TABLE IF EXISTS `products`;

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `shortdesc` varchar(255) NOT NULL,
  `longdesc` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `grouping` varchar(16) default NULL,
  `status` enum('active','inactive') NOT NULL,
  `category_id` int(11) NOT NULL,
  `featured` enum('true','false') NOT NULL,
  `price` float(4,2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

// Colors
DROP TABLE IF EXISTS `products_colors`;

CREATE TABLE IF NOT EXISTS `products_colors` (
  `product_id` int(11) NOT NULL,
  `color_id` int(11) NOT NULL,
  PRIMARY KEY  (`product_id`,`color_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

// Sizes
DROP TABLE IF EXISTS `products_sizes`;

CREATE TABLE IF NOT EXISTS `products_sizes` (
  `product_id` int(11) NOT NULL,
  `size_id` int(11) NOT NULL,
  PRIMARY KEY  (`product_id`,`size_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

ETC!

InsiteFX

[eluser]d1a8lo24[/eluser]
See your problem is not the options but how you're retrieving that information.

The following is my opinion not everyone will agree again we all have different ways of accomplishing different tasks.

1. You shouldn't be querying the database to much so why would you call the database for stock information if you can do it from the beginning, By using filters or to make my self more understandable the WHERE statement.

2. Look around all eCommerce sites you can see that there is a standard on how you view products.
1. the list of products -> database retrieval: Simple info thumbnail, name, sku, small description, price etc... also a quick check of stock from the field stock like WHERE stock_field != 0 this can help you by not showing the item that is not in stock or don't use the filter and keep that info so you can apply other tasks in the front-end.
2. Item's detail section -> database retrieval: Full product's info plus options available (If you use filters then certain options shouldn't show up if they are out of stock, that is why that options table should have a stock field).
3. Selection and buy botton -> at this stage you only work with ids, stock and prices.
4. check out time -> as previous those fields should help retrieve the basic info of the item and options.

So most of the time when you navigate a site to keep resources down you retrieve small chunks of data, unless its need it like when view full details either in an admin section or front-end section.

I my self still don't see why you're having such a big problem showing options, I can only see your problem being affected by 2 things.

1. Bad way of retrieving the info from the database, and even unless you're outputting over 1000 options you still shouldn't see an error just a small delay. In one of my applications I gave people the ability to choose not only the country but also state and city. Not just for the US but for the world I had to purchase a database with all this info. Anyway i had my options field and all that info, countries over 100, states over 1000 cities over 10000 never got an error and even the delay was acceptable.

2. Or you may have a problem in your code that is consuming resources.

My suggestion look at your code, the logic, try to simplify it, use the WHERE statement to your advantage.

Also I recommend that you look at a few shopping carts and see how they work from the admin to the front-end so you can get a better idea on how to apply the logic to your application.

One that use is call sunshop do a quick google search and use their demo, and the only reason that I use this cart is that it has the feature for pickup at store. I'm not sure if the rest of the carts have add it this by now but it was the only one when I purchase it about 5 years ago.

Good luck.