CODEWITHSUNDEEP
coldfusioncfccfquery
myTest532 myTest532
myTest532 myTest532

Reputation: 2381

Check if any field is different from database value

After I submit a form, I need to check if any field in the database table changed. If changed I create a new record, if not, I don't do anything. I can't just update the record.

Is there a way to do it without checking every single field like the code below?

<cfquery name="getData" datasource="myDS">
  Select * From table Where ID = #form.ID#
</cfquery>
<Cfset changed = false />
<!-- Check every field -->
<cfif form.data1 neq getData.data1>
   <cfset changed = true />
</cfif>
<cfif form.data2 neq getData.data2>
   <cfset changed = true />
</cfif>
<cfif form.data3 neq getData.data3>
   <cfset changed = true />
</cfif>
...

Thanks

Upvotes: 1

Views: 949

Answers (3)

Sathish Chelladurai
Sathish Chelladurai

Reputation: 690

We can use the cfquery to check the table having the existing data or not. If not having the same data means we can Insert the form. The following code may related to your scenario.

<cfif structKeyExists(form,"Submit")>
    <cfquery name="checkFormExisting" datasource="myDSN">
        SELECT * 
        FROM USERS 
        WHERE data1  = <cfqueryparam value="#form.data1#" cfsqltype="cf_sql_varchar">
            OR data2 = <cfqueryparam value="#form.data2#" cfsqltype="cf_sql_varchar">
            OR data3 = <cfqueryparam value="#form.data3#" cfsqltype="cf_sql_varchar">
    </cfquery>

    <cfif checkFormExisting.recordCount EQ 0>
        INSERT INTO 
            USERS (
                data1,data2,data3
                ) 
                VALUES (
                    <cfqueryparam value="#form.data1#" cfsqltype="cf_sql_varchar">,
                    <cfqueryparam value="#form.data2#" cfsqltype="cf_sql_varchar">,
                    <cfqueryparam value="#form.data3#" cfsqltype="cf_sql_varchar">
                )
    </cfif>
</cfif>

Hope, you're asking like the above code for your scenario.

Upvotes: 0

Snipe656
Snipe656

Reputation: 845

Might depend on what database you are using but you should be able to do a query that will insert if the data does not exist.

As an example I just tested this against Oracle 12c using CF2016 Enterprise and it creates a new record if the data does not exist. 

<cfquery name="Testing" datasource="Test">
    INSERT INTO TESTTABLE (DATA1, DATA2, DATA3)
    SELECT  <cfqueryparam value="#Form.Data1#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#Form.Data2#" cfsqltype="CF_SQL_VARCHAR" />, <cfqueryparam value="#Form.Data3#" cfsqltype="CF_SQL_VARCHAR" />
    FROM dual
    WHERE NOT EXISTS 
    (SELECT DATA1, DATA2, DATA3 FROM TESTTABLE WHERE DATA1 = <cfqueryparam value="#Form.Data1#" cfsqltype="CF_SQL_VARCHAR" />, 
    AND DATA2 = <cfqueryparam value="#Form.Data2#" cfsqltype="CF_SQL_VARCHAR" /> AND DATA3 = <cfqueryparam value="#Form.Data3#" cfsqltype="CF_SQL_VARCHAR" />)
</cfquery>

Upvotes: 3

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Can you explain this a little further? Why are you allowing a form to be submitted with changed data if you can't update the record itself? If you click Save, then compare the form data with the query data and have the action call a Create function when the data is different.

Let's say you have a thing query and form: 

<cfquery name="myThing">
    SELECT 
        thing_id,
        thing_name,
        thing_foo
    FROM 
        things 
    where 
        thingID = <cfqueryparam value="#url.thingID#">
</cfquery>

<form> 
    <input type="hidden" name="thing_id" value="#myThing.thing_id#">
    <input type="text" name="thing_name" value="#myThing.thing_name#">
    <input type="text" name="thing_foo" value="#myThing.thing_foo#">
    <button type="submit">Submit</button>
</form>

If you need to check the submitted data against what's already in the database, you can just run the query again on the form processing page and compare those values against the submitted form values. This example assumes you named the form fields the same as the database table columns. 

<cfset delta = false>
<cfloop item="key" collection="#myThing#">
    <cfif structKeyExists(form, key)>
        <cfif form[key] NEQ myThing[key]>
            <cfset delta = true>
        </cfif>
    </cfif>
</cfloop>

If any values differ, then create a new record. No idea what you need to do when the submitted values haven't changed. 

<cfif delta>
    <!--- Create a new record. --->
<cfelse>
    <!--- ¯\_(ツ)_/¯ --->
</cfif>

I've also seen it done where the original values are stored in hidden form fields and submitted along with the editable form fields. You could do this, but there's no guarantee that the values in the DB haven't been changed between you rendering the form and then submitting it.

You'll still have some challenge of how to tell if the DB values have changed on the way to the DB, but I'm not sure if you need so granular a check.

Upvotes: 1

Related Questions