[mv] Almost got a nested SQL loop working

[Karl Swisher <kswisher@iolinc.net>]

******    message to minivend-users from Karl Swisher <kswisher@iolinc.net>     ******

Hello,
	What I have is a read and a write SQL loop inside of a read SQL loop.
I have all of the below code working but only at different times and not
providing full output all the time. I'm modifying the rectipt.html page inside
of etc.  I am writting the order to an external SQL table.  Using interpolate=1
in the first or outside loop allows correct results to be returned in the
"newnumber" scratch variable of the second loop.  However when this
interpolate=1 is included,  the scratch variables cyprodid, cyproducttitle, and
cyproducttype return [sql-param ......] instead of the actual data.  When
interpolate=1 is removed from the first SQL loop,  the correct data is returned
in the scratch variables of the outside loop but the second SQL loop which is
another read by list loop returns the first field of the outside loop.  The
write loop operates correctly each way but is not being feed the correct data
all the time in all the fields desired.

	Does anyone know a way I can get both working.  I read the previous
archieve question dealing with nesting however that question referred to nesting
within the same table.  In my example the outside SQL loop is an
access to a different table than the inside loop. And even though the inside
loop tables are the same,  each access is closed with a {/sql] before the next
access.


[item-list]

Outside SQL loop
[sql type=list query="select producttype, producttitle,  productid from products where gpalb=[item-field gpalb] " interpolate=1]
	[set name="cyprodid"][sql-param productid] [/set]
	[set name="cyproducttitle"][sql-param producttitle][/set]
	[set name="cyproducttype"][sql-param producttype][/set]

First inside SQL loop, second read SQL loop
	[sql type=list query="select max(transdetailno) from newtransdetail_tbl"]
 		[set name='newnumber' interpolate=1][calc][sql-param transdetailno]+1[/calc][/set]
  	[/sql]

Second inside SQL loop, a write to table loop.
	[sql type=set interpolate=1]
			insert into newtransdetail_tbl
			values
			  ([scratch newnumber],
			   '[value mv_order_number]',
			    'now',
			   '[value name]',
			   '[value email]',
			   '[scratch cyproducttype]-[scratch cyprodid]',
			   '[scratch cyproducttitle]',
			)
	[/sql]
[/sql]
[/item-list]

I've got MiniVend 3.14-3. Any help will be appreciated.
Thanks
Karl
   
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list