different stock values to different product attributes

[ridingwave]

Hi,

 

My name is Tiago and I'm from Portugal.

 

 

I've posted this question in a lot of forums but unfortunately still don't have an answer...

 

I'm using "The MySQL Database Admin v1.5.5", which is installed in my host's control panel.

 

I'm developing a webstore, and I want to create the possibility of associating different stock values to different product attributes.

 

Example:

 

I have two t-shirts of the model A, one in green and one in white. As it is today, I can only assign a total stock ammount of 2 items (white + green).

 

I want to have the possibility of associating a stock value for the model A white t-shirt, and another stock value to model A green t-shirt.

 

I already have a Products table on my Mysql database. On it there's an Attributes field. It also has a Stock field (it's working but only like a total stock, not a stock for each feature). So I can set different features to the same product.

 

Now what I need is a table that tells me that I can have different stock values (product quantities) for different attributes of the same product.

 

I'd be very thankfull if you could help me on this.

 

Thanks in advance.

 

Kind regards,

[Barand]

you could try a structure like this

[pre]

producr          subproduct

=========        ==============

prodID      --+  subprodID

description  +-- prodID

                  description

                  stock_qty

                 

DATA

product                            subproduct

 

123  |  T-Shirt A                  1  |  123  |  green          |  5

                                    2  |  123  |  white          | 20

[ridingwave]

Thx for your answer.

 

Can you tell me how to do something like that?

 

I'm a total newbie in Mysql...

 

Thx once again.

[ridingwave]

I'm getting close...

 

How do I connect prodID to the subproducts table?

[Barand]

Just ensure that sub products of a product contain the same prodID as the product.

 

to query

SELECT p.prodID, p.description as prod, s.description as subprod, s.stockqty
FROM product p
    INNER JOIN subproduct s ON p.prodID = s.prodID

[ridingwave]

This is what I did already:

 

Created table subproduct, which contains fields ID, subproductID, productID, description and stock.

 

Now I didn't understand your query very well...

 

You have some abreviations that I don't understand...

 

Sorry this is all new to me yet...

[Barand]

"p" is a table alias for product table

"s" is a table alias for subproduct table

 

saves having to repeat the whole tablename

[ridingwave]

ok thx

[ridingwave]

but and what do you mean by "as"?

 

p.description as prod

 

[Barand]

This time it's a column alias.

 

My query contained 2 columns called "description", so I gave one an alias of prod and the other an alias of subprod to distinguish them

[ridingwave]

of course...nevermind it's my stupidity!

 

 

[ridingwave]

ok so this is what i did:

 

SELECT fws_product.productID, fws_product.description, fws_subproduct.description, fws_subproduct.stock
FROM fws.product
    INNER JOIN fws_subproduct ON fws_product.productID = fws_subproduct.productID

 

i got this error:

 

SQL query: Documentation

 

SELECT fws_product.productID, fws_product.description, fws_subproduct.description, fws_subproduct.stock

FROM fws.product

INNER JOIN fws_subproduct ON fws_product.productID = fws_subproduct.productID

LIMIT 0 , 30

 

MySQL said: Documentation

#1142 - SELECT command denied to user 'riding'@'localhost' for table 'product'

 

 

 

what i did wrong?

 

 

[ridingwave]

one thing i noticed : fws.product instead of fws_product

 

[ridingwave]

now no error came. only this message:

 

MySQL returned an empty result set (i.e. zero rows)

 

 

[Barand]

You need to alias at least one of the "description" columns.

 

Try changing INNER JOIN to LEFT JOIN and see if you at least get product table data

[ridingwave]

i renamed description of subproduct to "subdescription" is that ok now?

 

then I inserted this code :

 

SELECT fws_product.productID, fws_product.description, fws_subproduct.subdescription, fws_subproduct.stock
FROM fws_product
    LEFT JOIN fws_subproduct ON fws_product.productID = fws_subproduct.productID

 

and got this as a result :

 

 

I guess I'm getting to what I need but on the website 'm still not getting any option to insert the subproduct info...

[Barand]

If LEFT join is pulling product table fields but no data from subproduct then there are no prodIDs in the subproduct table wich match those in product

[ridingwave]

but wasn't it supposed to show me a new subproduct form on the website, when adding new products?

[Barand]

You mean you expected it to appear by magic?

[ridingwave]

no, i thought that, when creating a new database table and inserting this query, it would appear.

 

when i choose to create a new product on the website administration i only get the product fields, not the subproduct.

 

what am i supposed to do?

[ridingwave]

Am I supposed to edit any php files?

 

Is it better to go to the php forum section?