Skip to main content
C
cfsetNewbie
Known Participant
July 9, 2009
Answered

Need help with writting an update / insert with related tables

  • July 9, 2009
  • 2 replies
  • 2016 views

I am new at ColdFusion. I am learning how to write querys and am creating a small app to collect information from visitors to my web site. (It is also a good way for me to learn this language) I am having a problem and it is not just how to use an update / insert with related tables. I am not sure if I am even gathering the proper variables to compair them to existing db records before it runs either the update or insert querys.Can someone help me, show me how to update / insert related tables and maybe tell me if I am creating the proper varibales to do the compairison?This is my code, I am commenting it out.

<!--- creating a variable to compair with the db table--->
<cfset userIP = ('#CGI.REMOTE_ADDR#')>

<!--- Run the query and compair the cfset to the remote_addr cell--->
<cfquery name="userTracking" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
SELECT REMOTE_ADDR
FROM user_track
WHERE REMOTE_ADDR = #userIP#
</cfquery>

<!-- if the record exists, then run this update--->
<cfif userTracking EQ userIP>
<cfquery datasource="#APPLICATION.dataSource#">
UPDATE user_track, trackDetail
SET user_track.REMOTE_ADDR=<cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">,
user_track.browser=<cfqueryparam value="#Trim(CGI.HTTP_USER_AGENT)#" cfsqltype="CF_SQL_VARCHAR">,
user_track.visits = visits+1,
trackDetail.date=<cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP">,
trackDetail.path=<cfqueryparam value="#Trim(PATH_INFO)#" cfsqltype="CF_SQL_LONGVARCHAR">
WHERE REMOTE_ADDR =<cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>
<cfelse>

<!--- if it doesn't, then insert a new record --->
<cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
INSERT INTO user_track, trackDetail
(user_track.REMOTE_ADDR, user_track.browser, user_track.visits, trackDetail.userID, trackDetail.date, trackDetail.path)
VALUES(
<cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">,
<cfif Len(Trim(HTTP_USER_AGENT)) GT 1>
<cfqueryparam value="#Trim(CGI.HTTP_USER_AGENT)#" cfsqltype="CF_SQL_VARCHAR">,
</cfif>
visits+1,
<cfqueryparam value="#Trim(CGI.HTTP_USER_AGENT)#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#user_track.userID#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP">,
<cfqueryparam value="#Trim(PATH_INFO)#" cfsqltype="CF_SQL_LONGVARCHAR">
)
</cfquery>
</cfif>


Am I at all close on this? This doesn't throw any errors, but it isn't working either. So obviously it is wrong. I get a cfdump at the end of my compairison query, but once it hits the if statement, it is lost.

Thank you for your time anyone.

Newbie

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    I have been working on his code through the weekend. I have all the querts working, properly. I figured out how to pass an id to a related table.Right now, I can't get my query that is compairing the ip address variable with what is in the db. I have tried a number of different ways, but it isn't working properly.

    This is what I have now:

    <cfset userIP eq #CGI.REMOTE_ADDR#>
    <cfquery name="userTracking" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
    SELECT Remote_address
    FROM user_track
    WHERE REMOTE_ADDR = #userIP#
    </cfquery>

    then to fire off the querys for insert and update I use this:

    <cfif userTracking.recordcount is 1>

    <cftransaction action="begin">

    my update query is here

    </cftransaction>

    <cfelse>

    <cftransaction action="begin">

    insert query

    </cftransaction>

    </cfif>

    How do I write the query to compair the db records with the cgi.remote_addr variables?

    this is my error as it sits:

    Variable USERIP is undefined.

    The error occurred in C:\tracking.cfm: line 1
    1 : <cfset userIP eq #CGI.REMOTE_ADDR#>
2 : <cfquery name="userTracking" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
3 : SELECT Remote_address


    You have to set the variable before you can use it.  You are trying to use it on line 1 of your template.

    2 replies

    C
    cfsetNewbieAuthor
    Known Participant
    July 9, 2009

    I was reading about using a tag called cftransaction and grouping the 2 queries together inside that tag. Is this what you mean? If I do that, then how would I pass the userID variable to the 2nd table to link those records together?

    Like this:

    <!--- Set the variable to compair record to --->

    <cfset userIP = ('#CGI.REMOTE_ADDR#')>

    <!--- compair the records--->
    <cfquery name="userTracking" datasource="#APPLICATION.dataSource#" dbtype="ODBC">
    SELECT REMOTE_ADDR
    FROM user_track
    WHERE REMOTE_ADDR = #userIP#
    </cfquery>

    <!--- if the record exists, run this--->
    <cfif userTracking.recordcount eq 1>
    <cftransaction>
    <cfquery datasource="#APPLICATION.dataSource#" name="mainTable">
             UPDATE user_track
       SET user_track.REMOTE_ADDR=<cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">,
                 user_track.browser=<cfqueryparam value="#Trim(CGI.HTTP_USER_AGENT)#" cfsqltype="CF_SQL_VARCHAR">,
        user_track.visits = visits+1,
             WHERE REMOTE_ADDR =<cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">
    </cfquery>

    <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
    INSERT INTO trackDetail
                (mainTable.userID, trackDetail.date, trackDetail.path)
    VALUES(
      <cfqueryparam value="#mainTable.userID#" cfsqltype="CF_SQL_VARCHAR">
      <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP">,
      <cfqueryparam value="#Trim(PATH_INFO)#" cfsqltype="CF_SQL_LONGVARCHAR"> )
    </cfquery>
    </cftransaction>
    <cfelse>

    <!--- if the record doesn't exist run this --->
    <cftransaction>
    <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC" name="mainTable">
    INSERT INTO user_track
                (user_track.REMOTE_ADDR, user_track.browser, user_track.visits)
    VALUES(
      <cfqueryparam value="#Trim(CGI.REMOTE_ADDR)#" cfsqltype="CF_SQL_VARCHAR">,
      <cfif Len(Trim(HTTP_USER_AGENT)) GT 1>
      <cfqueryparam value="#Trim(CGI.HTTP_USER_AGENT)#" cfsqltype="CF_SQL_VARCHAR">,
      </cfif>
      visits+1
    )
    </cfquery>

    <cfquery datasource="#APPLICATION.dataSource#" dbtype="ODBC">
    INSERT INTO trackDetail
                (mainTable.userID, trackDetail.date, trackDetail.path)
    VALUES(
      <cfqueryparam value="#mainTable.userID#" cfsqltype="CF_SQL_VARCHAR">
      <cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP">,
      <cfqueryparam value="#Trim(PATH_INFO)#" cfsqltype="CF_SQL_LONGVARCHAR"> )
    </cfquery>
    </cftransaction>
    </cfif>

    I ran this, it doesn't error, and it isn't adding anything to the db. So this can't be right either.

    D
    Dan_Bracuk
    Inspiring
    July 9, 2009

    There are various ways to get the id of the record you just inserted.  All but one depend on the datatype and/or db software.

    C
    cfsetNewbieAuthor
    Known Participant
    July 9, 2009

    The ID I need to grab from the main query is an auto number in an access databaseSitting here looking at this, It seems I am missing operators to make this work properly. But I don't know what those are.

    Any ideas?

    D
    Dan_Bracuk
    Inspiring
    July 9, 2009

    I see two things to look at.

    Your insert and update queries are attempting to update two tables.  You have to do one at a time.

    Your if/else logic should be based on the recordcount of the 1st query, not the value.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices