0

Im building a shop and each product has a unique set of attributes.

the product db looks like this:

products_id, relation_id, product_name, description, 
price, glass, shipping, img, cat, subcat, model

Since every product has several (~40) different attributes unique to that product only ive created a second table to store them.

products_id, att_name, att_val, att_head, att_standard, att_order

This works fine, because there will never be two unique rows. The problem, however, is when i need to modify the attributes content.

using MySQL Workbench i can modify a row using something like

UPDATE product_attributes SET att_val='1500' WHERE products_id='112' AND att_head='threshold'

This however, doesn't seem to work when i update from my php script.

Is there an easy way to modify the table to support updating?

Im well aware of the stupidity not having an unique column. But im not sure how to make the two tables relate. Where should i store the unique id of the attributes?

Martin
  • 63
  • 1
  • 8
  • You'll need to post your PHP code - the update should work just as well there as with any other client. – Michael Berkowski Mar 19 '13 at 13:46
  • "*doesn't seem to work*"? What *does* it "seem" to do instead? – eggyal Mar 19 '13 at 13:47
  • about making two tables relate, did you consider `foreign keys`? – Marko D Mar 19 '13 at 13:47
  • If you need to reference an ID but don't have one, treat it like Oracle's ROWID. Someone on stackoverflow posted a nice solution here: http://stackoverflow.com/questions/2728413/equivalent-of-oracles-rowid-in-mysql – blamonet Mar 19 '13 at 13:51

2 Answers2

2

One choice,

add a primary key "auto_incremented" into the product_attributes table...

ALTER TABLE `product_attributes` ADD  `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

This Id is just for CRUD (Create Read Update Delete) task.

The only relation you can have between your two tables is the products_id wich allow you to have few product_attributes for one product

JoDev
  • 6,633
  • 1
  • 22
  • 37
  • Sure but the products table will have no relation to that id? – Martin Mar 19 '13 at 13:58
  • No, it's just to allow CRUD on this table using this ID. The relation is `products_id`! – JoDev Mar 19 '13 at 14:01
  • After trying for over an hour to avoid this situation, this is the correct answer. to be able to do CRUD tasks, i need to create an auto incremented id field. – Harry Nov 19 '14 at 09:41
0

Since 1 product has more than 1 unique attributes that u store in a second table, you should use the ID of the table product and store it in the second table with the attributes.

Hope this is what u need?

Naruto
  • 1,210
  • 3
  • 25
  • 28