ColdFusion: Using the same query throughout the page

Question

This seems like it should be easy, but man, I'm such a novice at some of this stuff. I feel like I've seen it done, so please bear with me if this is a dumb issue.

I have multiple cfifs set up on the page to filter out specific user groups when a form is submitted. I'd like to use the same cfquery in multiple places. The queries would be updating the same tables using the same form data, the difference would be creating new data or updating data.

EDIT: included more detailed code with common queries on top of the form submission and where I'd like them to render in the code comments

<cfquery name="setnewuserinfo" datasource="db">
  INSERT INTO users (name,email)
  VALUES ("#form.name#","#form.email#","#form.password#")
</cfquery>

<cfquery name="setnewcustomerinfo" datasource="db">
  INSERT INTO customers (name,phone,address,city,state,zip)
  VALUES ("#form.customer_name#","#form.phone#","#form.address#","#form.city#","#form.state#",#form.zip#)
</cfquery>

<cfquery name="updateuserinfo" datasource="db">
  UPDATE users
  SET 
    name = "#form.name#",
    email = "#form.email#"
  WHERE email = "#session.userinfo.email#"
</cfquery>

<cfquery name="updatecustomerinfo" datasource="db">
  UPDATE customers
  SET 
    name = "#form.customer_name#",
    phone = "#form.phone#",
    address = "#form.address#",
    city = "#form.city#",
    state = "#form.state#",
    zip = #form.zip#
  WHERE id = "#session.customerinfo.id#"
</cfquery>

<cfif IsDefined('form.submit')>
  <!--check signin-->
  <cfif IsDefined('session.userinfo'>
  <cfquery name="checkowner" datasource="db">
    SELECT role
    FROM users
    WHERE id = #session.userinfo.id#
  </cfquery>
    <cfif checkowner.role EQ "1"><!--is the owner-->
        <!--UPDATECUSTOMERINFO--> 
        <!--UPDATEUSERINFO-->
    <cfelse><!--not owner-->
        <!--SETNEWCUSTOMERINFO--> 
        <!--UPDATEUSERINFO-->
    </cfif><!--/check owner-->
  <cfelse><!--not signed in-->
      <!--CHECK DUPLICATE-->
      <cfquery name="checkdup" datasource="db" maxrows="1">
        SELECT id,email
        FROM users
        WHERE email = #form.email#
      </cfquery>
      <cfif checkdup.recordcount GT 0><!--there is a duplicate-->
          <cfquery name="checkowner" datasource="db">
            SELECT role
            FROM users
            WHERE id = #session.userinfo.id#
          </cfquery>
          <cfif checkowner.role EQ "1"><!--is the owner-->
              <!--UPDATECUSTOMERINFO--> 
              <!--UPDATEUSERINFO-->
          <cfelse><!--not owner-->
              <!--SETNEWCUSTOMERINFO--> 
              <!--UPDATEUSERINFO-->
          </cfif><!--/checkowner-->
      <cfelse><!--not a duplicate-->
        <!--SETNEWCUSTOMERINFO--> 
        <!--SETNEWUSERINFO-->
      </cfif><!--/checkdup-->

  </cfif><!--/check signin-->

</cfif><!--/form submit-->

I'd love to put the queries somewhere and within the <cfif> say "hey! call this particular query!", but I have no idea how.

Appreciate the help in advance.

From what you have described, it sounds like a cfc is the way to go. However, can you elaborate on what you mean by ...the difference would be creating new data or updating data The psuedo code above does not seem to do anything different for the various values, so it is not clear whether you really need quite so many cfif's. — Leigh
You first need to do more homework and research in ColdFusion. — Pankaj
@Pankaj I would agree. But that doesn't really help me get this current project done at the moment. — Big Mike
@Leigh one query would be UPDATE table SET field1 = form.field1 while another query would be INSERT INTO table (field1) VALUES (form.field1. If I had a user already in the system, then their tables would get updated, while if it was a new user, they'd get a new record inserted. I hope that makes sense... — Big Mike
Do you have that logic working yet? (It is hard to tell from your psuedo-code). If yes, please post the real queries so we can advise on how to wrap it in a function. See Chris's post for a rough outline. If no - you need to start on the query logic first. Worry about reuse once it is working. Start by constructing a basic query for an insert/update. There are different options for insert/updates depending on your dbms. For MySQL, one option for ON DUPLICATE KEY UPDATE. — Leigh

Chris Tierney Chris Tierney · Accepted Answer · 2015-01-27T23:00:31

Big Mike,

You'd best be suited using a MVC framework such as FW/1 or Coldbox to help you with this. I suggest looking into one.

If you don't use a framework, I'd at least use a CFC that contains your query code.

In the most simplistic manner for your question you'd use a UDF.

<cfif stuctKeyExists(form, "submit")>
    <cfif structKeyExists(form, "user1")>
        <cfif form.user1 IS A>
            <cfset setDatabase( value1 = form.value1 )>
        </cfif>
    </cfif>
    <!--- more if/else statements below --->
</cfif>

<cffunction name="setDatabase" output="false" returnType="void">
    <cfargument name="value1">

    <cfquery name="qSetInfo">
        UPDATE table1
        SET value1 = arguments.value1
    </cfquery>
    <cfquery name"qUpdateInfo">
        UPDATE table2
        SET value1 = arguments.value1
    </cfquery>
</cffunction>

ColdFusion: Using the same query throughout the page

1 Answers