Inventory/Stock Tracking

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

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

Inventory/Stock Tracking

Post by Mr Tech »

I have created a shopping cart script and am wanting to add a Inventory/Stock Tracking script that notifies the admin when they are low on stock... This would normally be easy however I have set-up the script so that you can add "options" to your products. Options could be anything such as size, colour, make etc... Because there are options, I need tot rack the stock of each option combination.

The product name is:

Shoes

And has these size options:

Small
Medium
Large


And these color options:

Red
Blue
Green


The above options would need to be put into a list like this... I could then have a text box at the end of each line where you would enetr how much stock is left.

Shoes Small Red
Shoes Small Blue
Shoes Small Green
Shoes Medium Red
Shoes Medium Blue
Shoes Medium Green
Shoes Large Red
Shoes Large Blue
Shoes Large Green


Now for my question.

How would I select all those different options from the database and put them in the list like above without having duplicates? What I mean by duplicates is the list would end up like this:

Shoes Small Red
Shoes Small Blue
Shoes Small Green
Shoes Medium Red
Shoes Medium Blue
Shoes Medium Green
Shoes Large Red
Shoes Large Blue
Shoes Large Green
Shoes Red Small
Shoes Red Medium
Shoes Red Large
Shoes Blue Small
Shoes Blue Large
Shoes Blue Medium
Shoes Green Small
Shoes Green Medium
Shoes Green Large


Only having the two options (Size and Color) makes it a little easier but if you had 3, 4 or even 6 options, it would get a little more complicated.

Hopefully I'm making sense.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT foo1, foo2, foo3 FROM table1 WHERE foo1 IN('option1', 'option2', 'option3') AND foo2 IN('option4','option5','option6') .....
something like that, if I understand your question.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

Where can I find more info about the IN function from your query? two letter words don't get searched for in Google...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I couldn't remember where it was stuck in the documentation for a bit, but I found it.

http://dev.mysql.com/doc/refman/5.0/en/ ... #id2810044
Post Reply