How to loop through two queries at the same time?

[pirlo89]

Hello everyone, I have two queries qryCustomer and qryStore. I have to loop through both at the same time. I tried to do this but that gave me duplicates.

<cfloop query="qryCustomer">

    <cfloop query="qryStore">

          Insert Into tblResults

          Values(#Name#,#Id#);

     </cfloop>

</cfloop>

If anyone knows how I can loop through both of them at the same time that way i can avoid inserting all ID's for the same Customer please let me know.

Thanks in advance.

[Steve Sommers]

I've found that if you want to reference the outer loop query within the inner loop, you need to make explicit references:

<cfloop query="qryCustomer">

    <cfloop query="qryStore">

          Insert Into tblResults

          Values('#qryCustomer.Name#',#Id#);

     </cfloop>

</cfloop>

Although I'm not sure if "Name" is in qryCustomer or not, this is just an example. Also get into the habit of using cfqueryparam and you may want to explicitly reference both just to clarify to future programmers (or yourself):

<cfloop query="qryCustomer">

    <cfloop query="qryStore">

        <cfquery datasource="#ds#">

            Insert Into tblResults ( Name,Id )
            Values(<cfqueryparam value="#qryCustomer.Name#" cfsqltype="CF_SQL_VARCHAR" />,<cfqueryparam value="#qryStore.Id#" cfsqltype="CF_SQL_INTEGER" />);

  </cfquery>

     </cfloop>

</cfloop>

Lastly, another option that may be more efficient is to create a single joined query so you are only looping through a single result set.

[pirlo89]

Thank you for answering on my question. I always use cfqueryparam but this time that is not something that cause the problem. I have them in my code but I haven't include on this example. Your answer did not help me to solve my problem. Also your suggestion to join two queries does not work in my case. I'm reading values from two different queries after I sent all parameters with Ajax. Then I have to grab values from those two queries and Insert them in the table. So I have to loop through both queries at the same time and do Inset. I'm not sure if that's possible in coldfusion and what would be other way to do this.

[Steve Sommers]

I guess I would need examples because I'm not sure the goal. Can you create a pseudo dump of the two query contents and what you expect to be inserted? Something like:

qryCustomer

1: id=123, name="customer 1"

2: id=124, name="customer 2"

qryStore

1: storied=234, name="merchant 1", productid=345, productname="something"

2: storied=234, name="merchant 1", productid=346, productname="something else"

3: storied=235, name="merchant 2", productid=347, productname="something different"

inserted:

???

[pirlo89]

Ok I will explain one more time with all details. I have screen where user can pick one or more customers or select All. Also on the same page User can pick Storage Unite(single ,multiple or All Storage Units). So when they pass the arguments to the next page I have two queries. Query Customers that will define number of selected customers and Query Storage that will define number of Storage Units. So User should not be able to submit anything if we have more customers than storage units. That's first thing that should be checked. If that is fine then we should loop through both quires Customers and Storage and Insert them in New Table. My current code looks like this:

<cffunction name="saveCustomers" access="remote" output="no" returnformat='JSON'>

  <cfargument name="AllCustomers" type="string" required="yes">

  <cfargument name="AllStorage" type="string" required="yes">

  <cfset fncResults = structNew()>

  <cfquery name="qryCustomers" datasource="Project1">

  Select ID, FirstName , LastName

  From Customers

  <cfif Arguments.AllCustomerss NEQ 'Y'>

        Where ID in (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Customers#" list="true">)

  </cfif>

  </cfquery>

<cfquery name="qryStorage" datasource="Project1">

  Select StorageID, StorageNumber, NumberOfUses

  From Storage

  Where NumberOfUses <> '0'

  <cfif Arguments.AllStorage NEQ 'Y'>

  and StorageNumber in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.Storage#" list="true">)

  </cfif>

  </cfquery>

  <cfloop query="qryCustomerss">

       <cfloop query="qryStorage">

       <cfif qryCustomers.ID NEQ qryAssign.CustomerID and qryStorage.NumberOfUses NEQ '0'>

       <cfset UsesLeft = #qryStorage.NumberOfUses# - 1>

       <cfquery name="AssignStorage" datasource="Project1">

            Insert Into ttAssignStorage(CustomerID, StorageNumber)

            Values (<cfqueryparam cfsqltype="cf_sql_integer" maxlength="20" value="#qryCustomers.ID#">,

            <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="5" value="#qryStorage.StorageNumber#">);

       </cfquery>

       <cfif isDefined("StorageNumber")>

            <cfloop query="qryStorage">

                 <cfquery name="UpdateStorage" datasource="Project1">

                 Update ttStorage

                 Set

                 NumberOfUses = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="2" value="#UsesLeft#">

                 Where StorageNumber in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#qryStorage.StorageNumber#" list="true">)

                 </cfquery>

            </cfloop>

     </cfif>

     </cfloop>

</cfloop>

This is my code that I'm using currently. I'm getting right values from my queries above but I'm having issues with looping thorough them and inserting those records. For example if I select all Customers and I have 150 of them, number of my storage units is 5. So my output will be each Customers is assign 5 times. What should not be done. I should get one Customer to be assign to one Storage Unit. So if you know what i'm doing wrong please let me know. My guess is that my cfloops are not at the right spot in my code.

Thanks in advance.

[Steve Sommers]

I would probably change the logic to do your customer query and then put the storage query within the customer loop and basically get one storage item at a time. Something like:

<cfquery name="local.qryCustomers" datasource="Project1">

Select ID, FirstName , LastName

From Customers

<cfif Arguments.AllCustomerss NEQ 'Y'>

  Where  ID in (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.Customers#" list="true">)

</cfif>

</cfquery>

<cftransaction>

<cfloop query="local.qryCustomerss">

  <cfquery name="local.qryStorage" datasource="Project1">

  Select StorageID, StorageNumber, NumberOfUses

  From Storage

  Where NumberOfUses <> '0'

  <cfif Arguments.AllStorage NEQ 'Y'>

   and StorageNumber in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.Storage#" list="true">)

    </cfif>

</cfquery>

  <cfif local.qryStorage.recordCount EQ 0>

   <cfabort showerror="ARGH! Not enough inventory!" />

  </cfif>

  <cfset local.UsesLeft = val(local.qryStorage.NumberOfUses) - 1 />

  <cfquery datasource="Project1">

  Insert Into ttAssignStorage(CustomerID, StorageNumber)

  Values (

   <cfqueryparam cfsqltype="cf_sql_integer" maxlength="20" value="#local.qryCustomers.ID#">,

            <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="5" value="#local.qryStorage.StorageNumber#">

  )

</cfquery>

  <cfquery datasource="Project1">

  Update ttStorage

  Set

   NumberOfUses = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="2" value="#UsesLeft#">

  Where StorageNumber in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#qryStorage.StorageNumber#" list="true">)

</cfquery>

</cfloop>

</cftransaction>

I corrected some scoping issues. In CFFUNCTIONs, you either need to explicitly refer to the LOCAL scope or you need to declare all local variables as VAR before using them (<cfset var qryCustomers = 0 />...). I prefer the local scope so I don't have to worry about forgetting to define the variable as var. I also wrapped the loop within a cftransaction so this executes as an all or nothing event -- if inventory runs out while looping, all are put back the way they were.

[pirlo89]

Thank you this helped me a lot. I have one more question. How I can compare number of records from these two queries? If number of records in query Customers is Less or equal than Number of records in query Storage that's fine otherwise throw an error message?

[Steve Sommers]

You can use the recordcount property:

<cfif local.qryStorage.recordCount LT local.qryStorage.recordCount>

  <!--- do something --->

</cfif>

But the last loop logic a provided does not lend itself well to this and I'm not sure it'll work for you anyway because of your use of NumberOfUses.

[pirlo89]

Thank you I got my code to work finally.