troubleshooting Question

Combining Queries

Avatar of Jacob Leis
Jacob Leis asked on
ColdFusion LanguageMySQL Server
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#')
<cfset local.personKey = insertPerson["GENERATED_KEY"] />
<!--- get state_id --->
<cfquery name="getState" datasource="gm">
    SELECT  id
    FROM    ref_state
    WHERE   abbr = '#getDonations.state#'
<!--- 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.postalcode#')
<!--- 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)
<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('') AND NEQ uCase(stateIndex)>
    <cfquery name="origin" datasource="gm">
        SELECT  id, name
        FROM    ref_origin
        WHERE   name = '#uCase(stateIndex)#'
<!--- 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 =
<cfif NOT getPersonOrigin.recordCount>
    <cfquery result="insertPersonOrigin" datasource="gm">
        INSERT INTO person_origin (person_id, origin_id)
        VALUES (#local.personKey#,
<!--- insert into ref_global - need campaign_id --->
    <cfquery result="insertGlobal" datasource="gm">
        INSERT INTO ref_global (person_id, campaign_id)
        VALUES (#local.personKey#, #getDonations.campaign_id#)
    <cfcatch type="database">
        <!--- continue process - data already exists --->
 <cfquery result="insertTag" datasource="gm">
     INSERT INTO person_tag (person_id, tag_id)
     VALUES (#local.personKey#, 6)

Open in new window

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
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