SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: SQL CF and data

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Uk
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL CF and data

    I am trying to manage a table that is generated from an external source... by creating a table in mysql for use within a cf application.

    This .cfm file is run via the scheduler... and is intended to keep the data in the local table up-to-date.

    This is what I do...

    Loop over the data...

    check if this item exists...

    if exist.... update the data...
    else... insert new data item...

    end loop

    Which is great... until... I deal with an item... that is no longer in the list...

    How can I delete an item that was in the table but is now not in the data feed?

    The data feed is just a long list..

    But basically... the table is a sql table of the information...

    I want to avoid... deleting all the data before the loop and inserting all as new... as this would result in new recordid numbers...

    Anyone?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    running a query inside a loop is a ~lot~ less efficient than running a single query for all rows

    in mysql you can make use of INSERT ON DUPLICATE KEY UPDATE, so there is no need to test each individual item to see if it exists already

    as for detecting items that are no longer in the feed, do a simple LEFT OUTER JOIN with an IS NULL test -- again, without the need for looping
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Uk
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937,

    This is how I have done it now... I am not that bothered about how efficient it is... as long... as it keeps the local table up to date..

    What do you think?...

    Code:
    <cftry>
    	<cfhttp url="feedurl" method="GET" resolveurl="No" throwonerror="yes"></cfhttp> 
    	<cfcatch type="any">
    		<h3>Looks like we had an problem:</h3>
    		<cfoutput>
    		Error Type: <i>#cfcatch.type#</i><br>
    		Error Message: <i>#cfcatch.message#</t>
    		</cfoutput>
    		<cfabort>
    	</cfcatch>
    </cftry>
    
    <cfset mydoc = xmlparse(cfhttp.filecontent)>
    <cfset postcodes = mydoc.stock.xmlchildren> 
    <cfset size = ArrayLen(postcodes)>
    
    <cfset countins = 0>
    <cfset countupd = 0>
    
    <cfloop index="i" from = "1" to = #size#>
    	<cfset pcode = mydoc.stock.line[i].pcode.xmltext>
    	<cfset ptype = mydoc.stock.line[i].ptype.xmltext>
    	<cfset days = mydoc.stock.line[i].days.xmltext>
    	
    	<!--- check for duplicate entries --->
    	<cfquery name="qExists" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
    		SELECT postcode FROM postcodelookup
    		WHERE postcode = '#pcode#'
    	</cfquery>
    	
    	<cfif qExists.recordcount is 1>
    		<!--- record is there update it... --->
    		<cfquery name="qUpdatePostcode" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
    		  UPDATE postcodelookup
    		  SET postcode='#ucase(pcode)#', type='#ptype#', days=#days#
    		  WHERE postcode = '#pcode#'
    	  	</cfquery>
    		<cfset countupd = countupd + 1>
    	<cfelse>
    		<!--- ...else insert new record... --->
    		<cfquery name="qInsertPostcode" datasource="#application.dsn#" username="#application.dsnun#" password="#application.dsnpw#">
    		  INSERT INTO postcodelookup
    		  (postcode, type, days)
    		  VALUES
    		  ('#ucase(pcode)#', '#ptype#', #days#)
    	  	</cfquery>
    		<cfset countins = countins + 1>
    	</cfif>	
    </cfloop>
    
    
    <cfoutput>
    Records Returned from feed: <i>#size#</i> <br>
    New Postcodes Inserted: #countins# <br>
    Postcodes Updated: #countupd# <br>
    </cfoutput>

    Huh?
    Last edited by mousemedia; May 21, 2009 at 04:58. Reason: Didn't say everything

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, what do i think about what, exactly?

    your code sure looks nice -- does it work correctly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Uk
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My code works fine... the *only* thing is does not do is remove/delete items.. it only add them..

    Say my table has five items in it.... A1, B2, C3, D4, E5

    Off this script is fired... and the Feed contain... A1, B2, D4, E5, F6

    A1, B2, D5, E5 would be Updated...
    F6 would be insterted (as it's new)...

    Giving a list of: A1, B2, C3, D4, E5, F6 (6 Items)

    the item C3 (the one that is now not in the list) is not removed

    To be correct it should read..: A1, B2, D4, E5, F6 (5 Items)

    It's driving me crazy...

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, to be honest, i don't know how to deal with cfhttp xml data

    with my limited knowledge, i would find a way to load it into a table

    as i said, that would allow me to use a single SQL statement to do the INSERT/UPDATE step, and then another single SQL statement to delete the ones that are no longer in the feed

    so if you can load your feed identifiers into a table, or even a list, i can help you from there, otherwise you're going to have to loop again... or something...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •