Film on characteristics of the goods



  • There is a table with product and pole characteristics Good_IDField_IDValue♪ Maybe a lot of records with one. Good_ID and Field_ID and Value

    Field_ID - it's the identifier of a certain characteristic.

    We need to write a request to make a choice. Good_IDwhich satisfy several couples Field_ID and Value♪ In fact, filtering on the characteristics of the product.

    I can't figure out how one request is to pick out all the products that have blue and weight to 1kg.

    Updating

    Thank you, everyone. I mean, it's like this.

    SELECT DISTINCT Good_ID FROM chars WHERE 
    Message_ID IN (SELECT Good_ID FROM chars WHERE  Field_ID = 3 AND Value = 'серый')
    AND   Message_ID IN (SELECT Good_ID FROM chars WHERE  Field_ID = 2 AND Value = '3')
    

    If I didn't make the request correctly, correct the plas.



  • You must have a table with a list of goods. Type goods♪ I mean. Good_ID That's it. id from this table.
    Next, let's say:

    • Your performance table is called attributes
    • Field_ID for colour = 1
    • Field_ID for weight = 2

    Then you need a request to look like:

    SELECT g.* FROM goods g
    JOIN attributes aCo AS g.id=aCO.Good_ID AND aCo.Field_ID=1 AND aCo.value='синий'
    JOIN attributes aW AS g.id=aW.Good_ID AND aW.Field_ID=2 AND aW.value=1
    

    This request will show you all the supplies from the table. goodswhich have colourField_ID=1blue and weightField_ID=2) = 1.


    I've got a schematic on my website, so I'm gonna give you some advice: it's better if you make a summary of all the goods and their attributes. Other information can be added: prices, descriptions, names, pictures. ♪ ♪

    Something like that:

    Good_ID  SKU         title                    price   a1    a2  a3  
    -------  ---------   ---------------------    ------  ---   --- ---
          2  [артикул]   [наименование товара]    450.00  red   3   45
        452  [артикул2]  [наименование товара 2]   15.00  white 5   64
    

    a1a2a3 - these are Attribut values 1, 2 and 3 Field_ID)
    Depending on the frequency of changes in product information, such a table can be re-engineered once per hour/a few hours/day.

    Having such a table, it would be much easier for you to get the data out of the base, make the reports, you can make a quick search on the website (and ask any difficulty will always be in one table)... a lot more. ♪ ♪

    I suggest that.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2