Skip to main content
P
pirlo89
Known Participant
October 16, 2015
Question

How to loop through two queries at the same time?

  • October 16, 2015
  • 1 reply
  • 2416 views

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.

This topic has been closed for replies.

1 reply

S
Steve Sommers
Brainiac
October 16, 2015

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.

    P
    pirlo89Author
    Known Participant
    October 19, 2015

    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.

    S
    Steve Sommers
    Brainiac
    October 19, 2015

    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:

    ???

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices