troubleshooting Question

Combining Queries

Avatar of Jacob Leis
Jacob Leis asked on
MySQL ServerColdFusion Language
7 Comments1 Solution232 ViewsLast Modified:
I would like to limit connections to the database by combining queries.  What is the best method to go about doing so?

Below is one of my blocks of queries that I would like to consolidate:
<cfquery result="insertPerson" datasource="gm">
    INSERT INTO person (first, last, entity_name)
    VALUES ('#getDonations.firstname#', '#getDonations.lastname#', '#getDonations.organization#')
</cfquery>
<cfset local.personKey = insertPerson["GENERATED_KEY"] />
<!--- get state_id --->
<cfquery name="getState" datasource="gm">
    SELECT  id
    FROM    ref_state
    WHERE   abbr = '#getDonations.state#'
</cfquery>
<!--- insert address1, city, state_id, country_id, postalcode into address --->
<cfquery result="insertAddress" datasource="gm">
    INSERT INTO address (address1, city, state_id, postalcode1)
    VALUES ('#getDonations.address1#', '#getDonations.city#', '#getState.id#', '#getDonations.postalcode#')
</cfquery>
<!--- insert into person_addresses --->
<cfquery result="insertAddress" datasource="gm">
    INSERT INTO person_address (person_id, address_id, type_id)
    VALUES (#local.personKey#, #insertAddress["GENERATED_KEY"]#, 1)
</cfquery>
<cfset local.personKey = insertPerson["GENERATED_KEY"] />
<!--- to get origin_id for person_orgin  --->
<cfif NOT isDefined('origin')
        OR isDefined('origin') AND structCount(origin) NEQ 2
        OR isDefined('origin.name') AND origin.name NEQ uCase(stateIndex)>
    <cfquery name="origin" datasource="gm">
        SELECT  id, name
        FROM    ref_origin
        WHERE   name = '#uCase(stateIndex)#'
    </cfquery>
</cfif>
<!--- insert into person_origin - need origin_id --->
<cfquery result="getPersonOrigin" datasource="gm">
    SELECT  id
    FROM    person_origin
    WHERE   person_id = #local.personKey#
        AND origin_id = #origin.id#
</cfquery>
<cfif NOT getPersonOrigin.recordCount>
    <cfquery result="insertPersonOrigin" datasource="gm">
        INSERT INTO person_origin (person_id, origin_id)
        VALUES (#local.personKey#, #origin.id#)
    </cfquery>
</cfif>
<!--- insert into ref_global - need campaign_id --->
<cftry>
    <cfquery result="insertGlobal" datasource="gm">
        INSERT INTO ref_global (person_id, campaign_id)
        VALUES (#local.personKey#, #getDonations.campaign_id#)
    </cfquery>
    <cfcatch type="database">
        <!--- continue process - data already exists --->
    </cfcatch>
</cftry>
 <cfquery result="insertTag" datasource="gm">
     INSERT INTO person_tag (person_id, tag_id)
     VALUES (#local.personKey#, 6)
 </cfquery>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros