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, Jan 28 '15 at 03:54
You first need to do more homework and research in ColdFusion. — Pankaj, Jan 28 '15 at 05:40
@Pankaj I would agree. But that doesn't really help me get this current project done at the moment. — Big Mike, Jan 28 '15 at 18:10
@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, Jan 28 '15 at 18:13
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](https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html). — Leigh, Jan 28 '15 at 21:12
There are different ways to structure it, but that is too big a discussion for an SO post. At the core, it looks like basic CRUD operations (Create, Read, Update, Delete) . A simplistic approach would be to create functions for each operation CreateXX, ReadXX, UpdateXX, etcetera, - where "XXX" is Users or Customers. The "CreateXXX" methods would `insert` a record, "ReadXXX" would do a `select`, etcetera. Simply invoke those functions when needed. — Leigh, Jan 31 '15 at 16:38
Note, your current queries are vulnerable to sql injection, so you should protect them with `cfqueryparam`. Nothing to do with your question, but if "email" is the PK, be careful the updating. You need to ensure the new email is not already in use, to avoid multiple records ending up with the same email. — Leigh, Jan 31 '15 at 16:39

score 1 · Answer 1 · answered Jan 27 '15 at 23:00

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 Answers1