Help with pulling data from sql

[juniorcfdev]

hello all,

I have a junior developer seeking some help on a situation i have encountered.

I have a MS SQL database with a column that has a recordset that looks like this. This column is called the SkuList.

[[14,1,0000.jpg][1,4,0001.jpg][45,1,0005.jpg]]

This is the orders column from the orders table out of our database. Basicly what it means is someone is ordering the following.
qty 14 - sku 1 - photo 0000.jpg
qty 1 - sku 4 - photo 0001.jpg
qty 45 - sku 1 - photo 0005.jpg

It is a basic table with customer info as well as what photo they orders and how many.

My question today is how do i parse out data like that and use that data as a variable in coldfusion?

I know i can query out that whole string with the following query:
<CFQUERY DATASOURCE="#datasource#" NAME="getdata">
Select SkuList
from orders
where ID=2
</CFQUERY>
<CFOUTPUT QUERY="getdata">#SkuList#</CFOUTPUT>

That would output the whole sting depending on how many items the customer ordered.

What i need to do is parse the whole string out and create variables for each. so on a fullfillment page i could list each item ordered into seperate rows and columns for that customer.
qty 14 - sku 1 - photo 0000.jpg
qty 1 - sku 4 - photo 0001.jpg
qty 45 - sku 1 - photo 0005.jpg

Ontop of all that i have to query a seperate database on that sku number to get an item description, so i would image that each element of the string would have to have its own variable.

I have read and reread many things about arrays and structures, but i am still confused on how this whole thing works. Please if anyone is so kind would you mind helping out a really confused person.

Thanks in advance

 

[r937]

any chance you can change the table design? whoever designed this wasn't thinking very far ahead, because it forces you to do a query to get the skulist, then do another query in a loop to get the item descriptions

first, i would use Replace three times, to remove the leading [[, the trailing ]], and then replace every occurrence of ][ with some single character like ~

14,1,0000.jpg~1,4,0001.jpg~45,1,0005.jpg

then use ListToArray with ~ as the delimiter to place each grouping into an array element

then, looping over the array, use ListToArray again with comma as the delimiter to get at the quantity, item number, and filename

how much easier would it have been to run a query like

  select qty
       , itemno
       , filename
    from OrderItems
   where OrderID = 2

r937.com | rudy.ca

 

[juniorcfdev]

yea it would of been alot of easier to do things like that, but it didnt happen that way. The other dev worth the database that way.

is there a way i can use the brackets to get all of this into an array? what is the proper syntax? What i am confused about is the arry part.

Thanks

 

[r937]

did you try my suggestion? Replace, Replace, Replace, then ListToArray, then cfloop, then ListToArray within the loop

r937.com | rudy.ca

 

[ecobb]

Couldn't you just do two replaces? One on "[" and the other on "]"? That way, it wouldn't matter if it was "[","[[", or "[[[[[[[[[[[[", replace would take out every instance of "[".

#Replace(Replace(SkuList,"[","","All"),"]","","All")#

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

yeah, ecar, but if you take them all out, now you're stuck with pulling out the values from a string that looks like this --

14,1,0000.jpg1,4,0001.jpg45,1,0005.jpg

and now you still have to split the qty values off the back of the filenames...

r937.com | rudy.ca

 

[ecobb]

I guess that's what I get for not reading the original post thoroughly. I didn't even notice the "][" between records.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[juniorcfdev]

This is what i have so far. I output the steps along the way so i could see what each line looks like.

I am having trouble with that last part, the part where you said loop through the array and build another array ontop of that one.

Any chance you can help me out with that last part.

<cfquery datasource="#Datasource#" name="showorder">
SELECT SkuList
FROM orders
WHERE ID=2
</cfquery>

<cfset SkuList = "#showorder.SkuList#">
<cfoutput>
#skulist#
</cfoutput>
<br>
<br>
<cfset SkuList = #Replace(Replace (SkuList, "[[", "", "all"), "]]", "", "all")#>

<cfoutput>
#SkuList#
</cfoutput>
<br>
<br>
<cfset SkuList = #Replace(SkuList, "], [", "~", "all")#>

<cfoutput>
#SkuList#
</cfoutput>

<br>
<br>

<cfset list = #ListToArray(SkuList, "~")#>

<cfdump var="#list#">


Thanks again for all your input all. Im still a junior in coldfusion but i love the way it works.

 

[r937]

that last part --

<cfset MyArray = ArrayNew(2)>
<cfloop index="j" list="#SkuList#" delimiters="~">
<cfset temparray = #ListToArray(j,",")#>
<cfset temp2 = ArrayAppend(MyArray, "#temparray#")>
</cfloop>

<cfdump var="#MyArray#">

r937.com | rudy.ca

 

[juniorcfdev]

Thank you so much for your help and time.

Here is the final result or what we came up with, just in case someone wants to review or if there is another junior dev out there wondering the same things i did.

Thanks again

--------------------------CODE--------------------------

<cfquery datasource="#datasource#" name="listorder">
SELECT SkuList
FROM orders
WHERE ID=#URL.ID#
</cfquery>
<cfset SkuList = "#listorder.SkuList#">
<cfset SkuList = #Replace(Replace (SkuList, "[[", "", "all"), "]]", "", "all")#>
<cfset SkuList = #Replace(SkuList, "], [", "~", "all")#>
<cfset list = #ListToArray(SkuList, "~")#>
<cfset MyArray = ArrayNew(2)>
<cfloop index="j" list="#SkuList#" delimiters="~">
<cfset temparray = #ListToArray(j,",")#>
<cfset temp2 = ArrayAppend(MyArray, "#temparray#")>
</cfloop>


<table width="660" border="0" cellpadding="0" cellspacing="0" bordercolor="#333333" bgcolor="#000080">
<tr>
<td width="660"><div align="center" class="style1">Transaction Details (There is a total of <cfoutput>#ArrayLen(MyArray)#</cfoutput> items listed)</div></td>
</tr>
</table>
<table width="660" border="1" cellspacing="0" cellpadding="0">
<tr>
<td width="112" bgcolor="#999999" class="style1"><div align="center">QTY</div></td>
<td width="67" bgcolor="#999999" class="style1"><div align="center">SKU</div></td>
<td width="197" bgcolor="#999999" class="style1"><div align="center">Title</div></td>
<td width="163" bgcolor="#999999" class="style1"><div align="center">Details</div></td>
<td width="109" bgcolor="#999999" class="style1"><div align="center">Cost</div></td>
</tr>
<cfloop index="Counter" from=1 to="#ArrayLen(MyArray)#">
<cfoutput>
<tr>
<td class="style9"><div align="center">#MyArray[Counter][2]#</div></td>
<cfquery datasource="#datasource#" name="items">
SELECT SKU, GUILabel, PriceRetail
FROM merchandiseitems
WHERE ID=#Myarray[Counter][1]#
</cfquery>
<td class="style9"><div align="center">#items.SKU#</div></td>
<td class="style9"><div align="center">#items.GUILabel#</div></td>
<td class="style9"><div align="center">
<p>#MyArray[Counter][3]#<br>
<img src="../photos/_OutputToProduction/Thumbnails/#Trim(showorder.PathToPhoto)##Trim(MyArray[Counter][3])#"></p>
</div></td>
<td class="style9"><div align="center">$#items.PriceRetail#</div></td>
</tr>
</cfoutput>
</cfloop>

 

[r937]

i'm glad you got your app working

however, it's generally not a good idea to have a query inside a loop like that

if you have n skus, your app is doing n+1 queries in total

what you should have is one query in total, which involves a join, however, this goes back to your unnormalized data design, which makes an efficient join in your case next to impossible

r937.com | rudy.ca

 

[philhege]

First of all, the database designer should be tarred and feathered. Secondly, any data manipulation should take place in the data engine; that's its job and that's where the task belongs in the realm of proper app design.

Run this procedure:


use SomeTestDatabase
create table Orders(or_pk int identity(1,1) not null,
or_custid varchar(20) NULL,
or_skulist varchar(1024) NULL
)
--test data
INSERT INTO Orders (or_custid,or_skulist)
SELECT
'CST01','[[14,1,0000.jpg][1,4,0001.jpg][45,1,0005.jpg]]'
UNION
SELECT
'CST02','[[14,4,0000.jpg][1,6,0001.jpg][45,7,0005.jpg]]'
UNION
SELECT
'CST03','[[14,2,0000.jpg][1,1,0001.jpg][45,3,0005.jpg]]'


select *, 0 as processed into #temptable FROM Orders

select top 1 0 ORDER_ID, space(24) cust,0 qty,0 product,space(32) picture into #results where 1 = 0

declare @id int, @cust varchar(20),@yuck varchar(1024),@items varchar(256)
declare @qty int, @product int, @picture varchar(24)
WHILE (select count(*) from #temptable where processed = 0) > 0
BEGIN
select top 1 @id = or_pk, @yuck = right(or_skulist,len(or_skulist)-1), @cust = or_custid
from #temptable where processed = 0

set @yuck = left(@yuck,len(@yuck)-1)
print @yuck


while charindex(']',@yuck) > 0
begin
set @items = substring(@yuck,charindex('[',@yuck)+1,charindex(']',@yuck)-2)
print 'Items: ' +@items

set @qty = substring(@items,1,charindex(',',@items)-1)
set @items = substring(@items,charindex(',',@items)+1,len(@items))
set @product = substring(@items,1,charindex(',',@items)-1)
set @items = substring(@items,charindex(',',@items)+1,len(@items))
set @picture = @items
insert into #results
select @id, @cust as cust, @qty as qty, @product as SKU, @picture as picture



set @yuck = substring(@yuck,charindex(']',@yuck)+1,len(@yuck))
print 'Yet to process: ' + @yuck
update #temptable set processed = 1 where or_pk = @id
end

END
drop table #temptable
select * from #results

drop table #results

Save the code, sans DDL, in a stored procedure.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[philhege]

Er, and lose the Print statements, too.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[philhege]

PS, sorry I missed it, but at this point it'd be a small matter to join the merchandiseitems data to the output of the procedure. Then you could use the GROUP attribute of CFQUERY to format the rendering.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[philhege]

Um, I meant CFOUTPUT. I'm going home now.