Inventory Tracking

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Inventory Tracking

Post by Mr Tech »

I posted about this last week but didn't get the answer I needed. I'm going to explain it a bit better this time and hopefully it will make sense. I just can't get my head around what I need to do...

I've created a shopping cart script and want to add an inventory control feature. The only problem is, when someone adds attributes to a product, the script needs to be able to track each and every attribute... Easier said than done.

I tested our another shopping cart script to see how they did it and this was the result (i've uploaded a screenie): http://img176.imageshack.us/img176/7639/inventorygj3.th.gif

There are three attributes in the above screen shot... Size, Color and Length... See how it merges them all together so that each and every attribute that could possible be selected is covered...

My problem is, how do I merge all my product attributes together like that and prevent duplicates etc...

I got started but got stumped... I thought I would select one atrribute and build on from there but I just can't figure out how I could do it...

Code: Select all

$query = mysql_query("select distinct(optionid) from {$tbl_name}listings_items_options where listingid='$_GET[listingid]' order by optionid asc limit 1") or die(mysql_error());
while($row = mysql_fetch_array($query)) {

	$query2 = mysql_query("select * from {$tbl_name}listings_items_options where listingid='$_GET[listingid]' and optionid='$row[optionid]' order by valueid asc") or die(mysql_error());
	while($row2 = mysql_fetch_array($query2)) {

	// thats as far as I got...

	}
}
If someone can shed light on my problem, that would be fantastic...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you have items, right, and items have, say... a description, a price and two attributes. Sort of like this

item_id
item_desc
item_price
item_attrib_1
item_attrib_2

Like this it appears that your attributes would be at an item level, associated with the item and editable by item. Is this what you are after?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

No, the attributes are stored in a separate table from the item itself. The item doesn't have anything to do with my question. This is very hard to explain so bear with me :wink:

What I am needing do do is draw the attributes from their table and then display them in every possible combination that could be selected when a buyer is making an order...

So when someone makes an order (For example they buy a Shirt that is Red and Small), the script looks at the database, finds the combination (Shirt,Red) and deducts the the amount purchased from the total amount of stock...

Basically what I am needing help with is selecting all the attributes for a specific product (or item) so that I can specify the amount of stock for each combination... The attributes are already added to the product, I just need to display the different combinations.

Am I making sense?

I'll make another post after this with some screenshots that might help my explanation.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

OK, this is what the MySQL Database looks like when the attributes have been added to a product:

Image

The the script extracts the above onto a page matching the listingid with the product and optionid's and valueid's with the name and value of the attribute that is specified in another database table:

Image

After that, the script displays all the attributes to the buyer so they can select what they would like to purchase...

Image

OK... now where I get stuck... I need to display all the possible combinations that can be selected from the above picture in the admininistration section of the script so that the admin can specify how much stock they have for each products attribute.... Here is an example of "every combination:

Blue,Small,Long
Blue,Small,Short
Blue,Medium,Long
Blue,Medium,Short
Red,Small,Long
Red,Small,Short
Red,Medium,Long
Red,Medium,Short
Green,Small,Long
Green,Small,Short
Green,Medium,Long
Green,Medium,Short
Orange,Small,Long
Orange,Small,Short
Orange,Medium,Long
Orange,Medium,Short

Next to each of those combinations just above would have an input box where you can specify the amount of stock for that combination... How do I generate those options into that combination list above without duplicates etc?

That's probably the best I can explain it...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you do a SHOW CREATE TABLE on your listings table and your options table? I know where you are going with this, but I want to look at the relationships to see if this is the best way to achieve what you want.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

I'm so glad I am starting to make sense. Thank you :)

Listings Table:

Code: Select all

CREATE TABLE `listings_items` (
  `id` int(10) NOT NULL auto_increment,
  `updated` int(10) NOT NULL default '0',
  `cat` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `code` varchar(255) NOT NULL default '',
  `summary` text NOT NULL,
  `description` longtext NOT NULL,
  `special` enum('y','n') NOT NULL default 'n',
  `price` double NOT NULL default '0',
  `availability` varchar(255) NOT NULL default '',
  `priority` double NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
Options table:

Code: Select all

CREATE TABLE `listings_items_options` (
  `id` int(10) NOT NULL auto_increment,
  `listingid` int(10) NOT NULL default '0',
  `optionid` int(10) NOT NULL default '0',
  `valueid` int(10) NOT NULL default '0',
  `stock` int(10) NOT NULL default '0',
  `weight` double NOT NULL default '0',
  `weight_prefix` enum('+','-') NOT NULL default '+',
  `price` double NOT NULL default '0',
  `price_prefix` enum('+','-') NOT NULL default '+',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
The listingid from the Options Table is what connects with the Listings Table...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you need to find a way to get the total number of items in stock for the optionid <=> valueid pair, is that right?
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

I don't need to find the total number of stock, I just need to figure out how to list data from the Options Table in this format below so that I can specify the amount of stock for each attribute combination:

Blue,Small,Long
Blue,Small,Short
Blue,Medium,Long
Blue,Medium,Short
Red,Small,Long
Red,Small,Short
Red,Medium,Long
Red,Medium,Short
Green,Small,Long
Green,Small,Short
Green,Medium,Long
Green,Medium,Short
Orange,Small,Long
Orange,Small,Short
Orange,Medium,Long
Orange,Medium,Short

I'm fine with everything else but displaying it like the above...
obiron
Forum Newbie
Posts: 15
Joined: Fri Nov 10, 2006 4:50 am

Post by obiron »

Further down the line you are going to end up in a whole pile of brown sticky mess with this. Believe me, I've worked with stock control software for a long time.

You will find life much easier if you can create a unique code for each option set and use this as part of a unique key.

Take your 'master' code (lets call is a style).

Take your attributes (e.g. Colour and size). there may be others.

Iterate through the various options and generate a unique code, appending the attributes to the style.

Style.col.siz

shirt.red.sml
shirt.red.med
shirt.red.lrg
shirt.blu.sml
shirt.blu.med
shirt.blu.lrg

You need to stock control each item so each one should have a unique record in the product table.

If possible it is also useful to link the options chosen to the style, otherwise if you add a colour option to the colour option type, how do you know whether shirt.pnk.med is a legitimate option.

I know this isn't what you asked for, but I can see where you are headed!!

This becomes even more important if not all combinations are possible. E.g you probably wouldn't sell many shirts with a 48 inch chest and a 14.5 inch collar because you woudl be a very funny shape if that is what you wore.

As for display functions. I would suggest a table format with dimension 1 on the x axis and dimension 2 on the Y axis. If you have more than two dimensions then put the Z dimension on a tab.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

I agree with obiron. I have found that it is easiest to have an distinct SKU for each item in inventory -- after all they are "Stock Keeping Units." I usually then create other fields in each record to organize the records when displaying them. obiron says "Take your 'master' code (lets call is a style)", well I just call it a Master Code or SKU because style means something for certain products.
(#10850)
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Thanks for that. I'm going to have to read your post a few times to fully understand but I greatly appreciate it.

It looks like I've found the answer to my questions. It took me ages to work out. I need to do some testing and make sure it works in every scenario...

I've still got heaps to figure out but I'll get there. :D

When it comes to unique records, each attribute value has a unique id... What if I got all the unique IDs for a combination (E.g: the unique ID for Blue, Small, Large could be 1,3,2) and then I could put those numbers in an array and order it from lowest the highest (e.g: 1,2,3) and insert that into the database... http://au2.php.net/sort

When someone makes an order, I simply order the selected option IDs from lowest to highest and match it with the one in the database.

Do you reckon that will work?
Post Reply