Copy link to clipboard
Copied
I have the queries that get values from other two tables and then insert those values into the final tables. My code below did not inserted the correct record.
Thanks
<!---list all purNo, padNo where purNo = 3215--->
<Cfquery name="getDetails" datasource="#dsn#">
select purNo, padNo
from tbl_details
where purNo = 3215
</Cfquery>
<!---list all cusNO where purNo = 3215--->
<Cfquery name="getCust" datasource="#dsn#">
select cusNO
from tbl_customer
where purNo = 3215
</Cfquery>
From the form, I have one text box for user to enter new date then hit submit. Once submit button is clicked, I would like to get the results from above two queries to insert into tblOrder.
<cfset note ="new date changed from" />
<cfloop query="getDetails">
<cfloop query="getCust">
<cfquery name="insert" datasource="#dsn#">
insert into tblOrder (purNo, padNo, cusNO, note)
values
(
<cfqueryparam value ="#getDetails.purNo#" />,
<cfqueryparam value ="#getDetails.padNo#" />,
<cfqueryparam value = "#getCust.cusNO#" />,
<cfqueryparam value="#note# #getDetails.old_ate# &'to' & #form.new_date#" />
)
</cfquery>
</cfloop>
</cfloop>
Copy link to clipboard
Copied
Instead of nested loops, use this type of syntax.
insert into tblOrder
(field1, field2, etc)
select somefield, someotherfield, etc
from some other tables
where some conditions are met
Copy link to clipboard
Copied
You could do a join, enabling you to use just one query. Something like
<!--- space appended --->
<cfset note ="new date changed from " />
<Cfquery name="getCustDetails" datasource="#dsn#">
select tbl_details.purNo, tbl_details.padNo, tbl_customer.cusNO
from tbl_details, tbl_customer
where tbl_details.purNo = tbl_customer.purNo
and tbl_details.purNo = 3215
</Cfquery>
<cfloop query="getCustDetails">
<!--- note: 'old_date' replaces 'old_ate' and " to " replaces "to" --->
<cfset note = note & getCustDetails.old_date & " to " & form.new_date>
<cfquery name="insert" datasource="#dsn#">
insert into tblOrder (purNo, padNo, cusNO, note)
values
(
<cfqueryparam value = "#getCustDetails.purno#" cfsqltype = "cf_sql_varchar" />,
<cfqueryparam value = "#getCustDetails.padNo#" cfsqltype = "cf_sql_varchar" />,
<cfqueryparam value = "#getCustDetails.cusNO#" cfsqltype = "cf_sql_varchar" />,
<cfqueryparam value = "#note#" cfsqltype = "cf_sql_varchar" />
)
</cfquery>
</cfloop>