Create reports from delimited DB fields 1

[Pugman]

Years ago I created a webstore for one of my company's customers using CFShopKartLite. It's been working great, but now they want reporting (i.e. How many products sold?). The CFShopKartLite application inputs the shopping cart contents to 3 fields (CrtProductID, CrtProductName, CrtProductQuantity) of the database as a delimited string created by using CF's ListAppend function. Displaying a single order is easy using ListGetAt in a loop. I just can't wrap my mind around how to go through multiple orders and generate a total quantity for each product.

Here's a typical scenario (simplified):
John Sample orders 10 Books, 20 Decals and 30 Posters.
Jane Doe orders 15 Decals, 25 Posters, 5 Books and 5 Flash Drives

Here's the database view (simplified):
OrderNo CustName CrtProductID CrtProductName CrtProductQuantity
1 John Sample BK^DL^PS Book^Decal^Poster 10^20^30
2 Jane Doe DL^PS^BK^FD Decal^Poster^Book^Flash 15^25^5^5

I know I'm supposed to have at least some code to show I've been trying to solve this but nothing I've tried even comes close so I'm too embarassed to post it. I've scoured Tek-Tips and the internet and haven't found anything to help me. Any help would be greatly appreciated.

 

[r937]

to solve this problem without writing a mountain of complex code, redesign your table

storing multiple values inside a single column violates first normal form and results in just the sorts of problem you are encountering

and if you're unable to change the design of the table because of some canned application, then you will have to live with it, which means returning ~all~ rows to CF, using ListGetAt on each row, and accumulating your totals yourself

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

Thanks r937

I was afraid of that. I used the table design that came with CFShopKart Lite. I can change the table.

What's the best way to re-design it?

I know I'm asking alot, but any help or pointers would be great.

 

[r937]

redesigning the table is pretty straightforward, you can even use the same column names

keep CrtProductID, make it numeric, and only ever insert one id into it

drop CrtProductName, unless you want to keep historic records for products that a customer ordered if the product names change frequently -- you can retrieve the name from the products table (assuming you have a products table) using a join when you need it

keep CrtProductQuantity, make it numeric, and only ever insert the appropriate quantity corresponding to the id entered into CrtProductID

then insert multiple rows for each order -- one for each product





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Pugman]

r937

Thank you very much.