🔒 Locked

[web] SQL Help!

Started by clearz Feb 27, 2007 at 10:10 PM 3 replies 1.3k views

Original Post

Feb 27, 2007 at 10:10 PM


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.

dave

322

dave

Feb 27, 2007 at 10:19 PM

It IS late and so this might be a dumb question, but why not store the size and colour of the clothing item in the first table?

Dave

wmurdick

Feb 27, 2007 at 10:49 PM

I'm with Dave on this one. However, if you absolutely must do it the way you currently have it setup then I would suggest looking up the Mid() function and Locate() or Instr() or whatever your server uses.

Kylotan

Mod

3,487

Kylotan

Feb 28, 2007 at 7:53 AM

And once you've found the relevant string-parsing function, you may want to consider creating a VIEW on the table that performs that action for you, thus simplifying future queries.

Really though, I agree with fixing the table.

clearz

Author

clearz

Feb 28, 2007 at 8:38 AM

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 &#106avascript or ajax. This would continue until all combo boxes are selected.<br><br>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.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.