Loop over a query to find the color and size

[GUJUm0deL]

Hiya folks. Need some help here. This is what the record in the dB looks like:

NOTE: ^ is the parser

itemID:
87^93

ProdName:
Test product<br><b>Color</b>: Blue^nice clean shirt


Now, the above example has the customer buying two products whose prod ID is 87 and 93. For Prod ID 87 the user chose the item in blue; and Prod ID 93 has no color or size pref.

What I'd like to do is the following for inventory purposes: do a loop over the ProductsSold table for each product and then find any items that have COLOR and SIZE.

So, in the above example, when I go into the inventory section to see how many items of prod ID 87 were sold, I want to be able to see that x-number of blue were sold, and x-number of red sold. Right now, I have the overall sold.

Does this make sense?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

does that make sense? no

you will have to supply a bit more information about the SroductsSold table and how you want to update it, and from where

and are you really storing two ids with a delimiter in a single column?

that's gonna give you nothing but pain and heartache

r937.com | rudy.ca

 

[GUJUm0deL]

Hey Rudy (long time and see)...I inherited this application from another developer and that is the dB design. Each customer's shopping cart item ID, product name is in one column separated via a delimiter.

For example, let's say we have two different customers, then the table would look like:

CustID          QtySold          ItemID          ProdName
1009091          1[COLOR=red]^[/color]2            15[COLOR=red]^[/color]96          Shirt101<br><b>Color</b>: Blue[COLOR=red]^[/color]washdry jeans

1004585          3[COLOR=red]^[/color]1[COLOR=red]^[/color]1          11[COLOR=red]^[/color]15[COLOR=red]^[/color]41       Suede Shoes<br><b>Size</b>: 10<br>Color: Black[COLOR=red]^[/color]Bunny[COLOR=red]^[/color]basekball

Aside from making the inventory more helpful, I haven't come across any issues with this design. I have it working (making my own modifications and code rewrites) with this logic in place. I just wanted to see how I can make the inventory level more helpful by separating the product count by color and size.

The ProductsSold table is (of course) being updated as the customer sale is successfully complete. That already happens. This issue I'm posting comes when the client goes into their admin section and looks in the inventory section. Right now, the inventory section deducts the on-hand quantity by however many the number of items sold is. This also works great.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

I just wanted to see how I can make the inventory level more helpful by separating the product count by color and size.

good luck with that


i have no idea where to even begin...




r937.com | rudy.ca