SQL help needed please

clearz

Attributes_Stock Table

id | product_id | Attributes | Quantity

---

1 | 44 | 46,41 | 9
2 | 44 | 46,42 | 5
3 | 44 | 43,49 | 3
4 | 44 | 44,42 | 5
5 | 45 | 52,41 | 1
6 | 45 | 38,41 | 5
7 | 45 | 52,52 | 6
8 | 45 | 35,41 | 5
9 | 46 | 55,43 | 0


Attributes Table

id | product_id | Option_id

---

41 | 44 | 12
42 | 44 | 32
43 | 44 | 54
44 | 44 | 23
45 | 44 | 21
46 | 44 | 13


Hi I am having a problem tying to solve a sql statement. Im not even sure can this be solved using SQL. Basically it is from a shopping cart. There are two tables containing information about the attributes (Colour and Size) of certain clothes products. The mixture of attributes is stored as a string in the Attributes column. You can see it as 46,41 etc . This is pointing to item 46 and item 41 in the Attributes table. I need to be able to get the corresponding Option_id for each of these two items. Is there an sql statement where I cound use the data found in one cell in the Attributes_Stock Table.Attributes column and retrieve the two corresponding Option_id's for instance if I look at the Attribute string 46, 42 I would like to be able to get the result as follows


Option_id_1 | Option_id_2

---

13 | 32


I hope I made myself clear as its pretty late. Thanks for any help.

John.

DublinWriter

Hi John,

First off, it's a pretty bad table design you're dealing with as it doesn't conform to the best-pratice of what's called 'Third Normal Form'. But that's a side issue!

Assuming that there will only be ever two comma-seperated values in the attributes_stock.attributes field, then your SQL would look something like this:

select attributes.option_id from attributes, attributes_stock
where attributes.id = substr(attributes_stock,1,2) or
attributes.id = substr(attributes_stock,3,2)

Obviously you'd have to add an additional clause to the above to limit to a specific product and my particular use of the substr function mightn't be supported on your particular DBMS, but it should give you a starting point.

Again, this will work assuming that there are only ever two values in the attributes_stock.attributes field. It would be almost impossible to code a SQL statement if attributes_stock.attributes contained a variable number of values as you'd need to parse the string. Some DBMS might let you do what's called macro substitution where your clause could be something like...
"where attributes.id IN[@attributes_stock]", but that's about as much advice as I could give without knowing your specific DBMS.

clearz

Hi Dublin, thanks for the responce. I am fully aware of the non compliance with the process of normalization and you would never catch me designing a database in such a lazy fashon. The tables shown are snipets from the Zencart database which I'm currently using to put together an e-commerce solution for a client. This has been a nightmare to mod with un-commented badly written code and horrible design issues like what ive shown. Needless to say I will not be using Zencart again.

What I am trying to do here is only show the attributes that are in stock at the moment. A user of the site navigates to a product and then selects the Attributes from drop down combo boxes. There could be a combo box for the item colour and another for the item size but this is not limited to two as you mention in your post. The site admin can add as many Attributes as needed from 0-n.

Currently all attributes are shown on the product page regardlessly if they are in stock or not. The user is only told that the item is not in stock when he/she proceeds to the checkout. My proposal is to only alow access to one combo box at a time. When an item is choosn the second combo box will be dynamically filled out with the choices that are in stock that are compatible with the choice made in the first combo box using either javascript or ajax. This would continue until all combo boxes are selected.

The anoying thing is that the values in the option tags in the html come from the Attributes.option_id field. There is no reason why they cant just use Attributes.id here. This would make it a simple matter of quering Attributes_Stock.Attributes for the id's to use in the combo boxes.

FSL

While I would agree that the database may leave a lot to be desired you are being somewhat presumptive in your proposed solution. I would never assume I knew which of the criteria was the one of paramount importance to the customer, and then the next in order and so on.

The ideal solution would be one where the customer chooses one or more of the criteria and a list of in stock, which match the chosen (if any) criteria is displayed.

For example I want to buy a sweater, what is more important to me today colour, style or size? And will the same apply next time I visit?

clearz

FSL I'm not sure I get you. Why should it matter what attribute a customer selects first. An attribute has to be selected from each section before an item can be added to the cart.

FSL

Your gripe seemed to be that until you added to the cart you did not know if the item was in stock or not. Choices in a shopping scenario are not always absolute. Your way if the preferred choice of what you determine to be the first criteria to be chosen results in another of the criteria, which may be of greater importance, not being available the customer can leave the site. In my earlier example if colour was the first criteria you presented and I choose blue,my size might not appear I go away. Whereas I might have been happy to buy a brown or green one.

clearz

You accually got a very good point there FSL something that will deffently get a mention to my client. Thanks.