Solved

ColdFusion inserting different data based on different inputs

Posted on 2007-03-26
12
217 Views
Last Modified: 2010-04-06
I am writing a cold fusion script that inserts data into a SQL database from a text file that is on a remote server.  As of right now my script reads in all of the data from the text file and will output it into tables on the screen (which is just for debugging problems)  I was just thrown a curve ball though, When I am inputting the values for counties the data is based off of another table.  So if the text file says its a job for CT then I have to add every county in CT into the job listing on my site.  I have absolutely no idea how to do this.  I NEED THIS ASAP, tell me what code you need to see. I need to insert the correct counties based on the state from the text file into the database for each record, I also need to trucate the counties field on that table so it does not insert the state but just the county.

Example of text file:

ID|Title|Location|State|Companyh|Jobfiled|URL|jobfield2 ~

My Code:

<html>
<head>
<title>t</title>
<body>
<p>The Following Jobs are the ones that are going to be added to the database and are being queryed from<br />
 <a href="www.test.com/test.txt">Job Source</a><br />
 To enter the Jobs scroll to the bottom of the list and hit the insert jobs button.
 
 



<br>
<table width="100%"  border="1" cellspacing="2" cellpadding="3">
  <tr>
   <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Job ID</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Job Title</font></td>
       <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Location</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">State</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Company Name</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">JobField1</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Website</font></td>
      <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Jobfield2</font></td>
      
        
 
  </tr>
<CFHTTP URL="test.com/test.txtt" METHOD="get"></CFHTTP>
<cfoutput>
<CFSET crlf = "~">

<CFLOOP INDEX="orec" LIST="#cfhttp.FileContent#" DELIMITERS="#crlf#">
  <cfif len(trim(orec)) >


<CFSCRIPT>

JobId = ListGetAt(orec, 1,"|");
JobTitle = ListGetAt(orec, 2,"|");
Location = ListGetAt(orec, 3,"|");
State = ListGetAt(orec, 4,"|");
CompanyName = ListGetAt(orec, 5,"|");
JobField1 = ListGetAt(orec, 6,"|");
Website = ListGetAt(orec, 7,"|");
JobField2 = ListGetAt(orec, 8,"|");

</CFSCRIPT>



</cfoutput>



 <tr bgcolor="##00FF99">
 <td width="62"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#JobId#</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#JobTitle#</font></td>
    <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#Location#</font></td>
      <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#State#</font></td>
      <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#CompanyName#</font></td>
    <td> <font size="1" face="Verdana, Arial, Helvetica, sans-serif">#JobField1#</font></td>
      <td> <font size="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="#website#" target="_blank">#Website#</a></font></td>
      <td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">#JobField2# </font></td>
      
 </tr>

  </cfif>
</CFLOOP>
</cfoutput>
</table>

<br />
<br />



</body>
</head>
</html>


Here is the Counties table

CID      Counties
1      Rhode Island-Bristol
2      Rhode Island-Kent
3      Rhode Island-Newport
4      Rhode Island-Providence
5      Rhode Island-Washington
11      Alabama-Anniston
12      Alabama-Birmingham
13      Alabama-Mobile/Dothan
14      Alabama-Montgomery
15      Alabama-Northern/Huntsville
16      Alabama-Tuscaloosa
17      Alaska-Fairbanks
18      Arizona-Flagstaff
19      Arizona-Phoenix
20      Arizona-Tucson
21      Arizona-Yuma
22      Arkansas-Eastern
23      Arkansas-Little Rock
24      Arkansas-Western
25      California-Anaheim/Huntington Beach
26      California-Central Coast
27      California-Central Valley
28      California-Chico/Eureka
29      California-Long Beach
30      California-Los Angeles
31      California-Marin County/North Bay
32      California-Oakland/East Bay
33      California-Orange County
34      California-Riverside County
35      California-Sacramento
36      California-San Bernardino/Palm Springs
37      California-San Diego
38      California-San Francisco
39      California-Santa Barbara
40      California-Silicon Valley/Peninsula
41      California-Silicon Valley/San Jose
42      California-Ventura County
43      Colorado-Boulder/Fort Collins
44      Colorado-Colorado Springs
45      Colorado-Denver
46      Colorado-Western/Grand Junction
47      Connecticut-Fairfield County
48      Connecticut-Hartford County
49      Connecticut-New Haven County
50      Connecticut-New London County
51      Delaware
52      District of Columbia
53      Florida-Daytona
54      Florida-Ft. Lauderdale
55      Florida-Ft. Myers/Naples
56      Florida-Gainesville/Jacksonville/Ocala
57      Florida-Melbourne
58      Florida-Miami
59      Florida-Orlando
60      Florida-Pensacola/Panama City
61      Florida-Pensacola/Panama City
62      Florida-St. Petersburg
63      Florida-Tampa
64      Florida-West Palm Beach
65      Georgia-Atlanta
66      Hawaii-Hawaii
67      Idaho-Boise
68      Illinois-Chicago
69      Indiana-Indianapolis
70      Iowa-Central/Des Moines
71      Kansas-Kansas City
72      Kentucky-Louisville
73      Kentucky-Northern
74      Louisiana-Baton Rouge
75      Louisiana-New Orleans
76      Maine-Central/Augusta
77      Maine-Northern/Bangor
78      Maine-Southern/Portland
79      Maryland-Baltimore
80      Massachusetts-Boston/Suffolk County
81      Massachusetts-Framingham/Worcester
82      Massachusetts-Western/Springfield
83      Michigan-Detroit
84      Michigan-Grand Rapids
85      Michigan-Lansing
86      Minnesota-Minneapolis
87      Minnesota-St. Paul
88      Mississippi-Central
89      Mississippi-Northern
90      Mississippi-Southern
91      Missouri-St. Louis
92      Montana-Eastern/Billings
93      Montana-Helena/Butte
94      Montana-Western/Missoula
95      Nebraska-Lincoln
96      Nebraska-Omaha
97      Nevada-Las Vegas
98      Nevada-Reno
99      New Hampshire-Northern
100      New Hampshire-Southern
101      New Jersey-Central
102      New Jersey-Northern
103      New Jersey-Southern
104      New Mexico-Albuquerque
105      New Mexico-Santa Fe

0
Comment
Question by:ostashenp
  • 7
  • 5
12 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 18796639

 1.  The "state" you are pulling out of the file, is it the state abbreviation like CT, NY, CA or is the full name?  If its the abbreviation, you need another table to map the abbreviation to the full name because the counties table uses the full name?

2. Since there are more than one counties to a state, what are you going to do with this data?  (That is, for each loop of your CFLOOP, you may have 4,5,6.. counties).  Are you inserting the same record again and again once for each county?  Or are you inserting the main record once and then inserting a child record for each county?  


Here is the general idea of how to query counties for each record...


<cfquery name="getCounties" datasource="#datasource#">
  select * from counties
</cfquery>

<CFLOOP INDEX="orec" LIST="#cfhttp.FileContent#" DELIMITERS="#crlf#">
  <cfif len(trim(orec)) >
 
  <CFSCRIPT>
      JobId = ListGetAt(orec, 1,"|");
      JobTitle = ListGetAt(orec, 2,"|");
      Location = ListGetAt(orec, 3,"|");
      State = ListGetAt(orec, 4,"|");
      CompanyName = ListGetAt(orec, 5,"|");
      JobField1 = ListGetAt(orec, 6,"|");
      Website = ListGetAt(orec, 7,"|");
      JobField2 = ListGetAt(orec, 8,"|");
  </CFSCRIPT>

  <cfquery name="getCounty" dbtype="query">
    select * from getCounties
      where counties like '#state#-%'
  </cfquery>
 


Your counties, for the current record, will now be in the GetCOUNTY query.   What to do with it?

0
 
LVL 2

Author Comment

by:ostashenp
ID: 18797306
Ok I will try that, now how do I cut down my return field.  In the County Table every county has the state like this:

New Mexico-Santa Fe


How do I truncate it so New Mexico- is not there and it only inputs Santa Fe
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18797374

 Use the list function to delimit the string at the hyphen...

<cfset variables.county = listRest(getCountries.county, "-")>
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18797389
Will I run into a problem because the states are abbreviated with 2 letters like for instance Maryland and Massachusettes

One is MA
One is MD

If I do where counties like '#state#-%'

will it get confused and will I get the correct output from each ?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18797483

 Yes, this is the #1 item I mentioned in my post above.  If your data file has MA, and MD, you need to translate them to their full names to match them to the counties.   Do you have a database table of states and their abbreviations?

0
 
LVL 2

Author Comment

by:ostashenp
ID: 18797522
No I do not have a table with that.

I was thinking of making a table like so:

CID    State    County

1         RI         Bristol
2         RI         Kent
3        RI          Newport

ETC.....

Would that make it easier ?        
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 39

Expert Comment

by:gdemaria
ID: 18797561

 sure, if you have that data?  ( data to populate state and counties)
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18797567
States...

AK      Alaska
AL      Alabama
AR      Arkansas
AZ      Arizona
CA      California
CO      Colorado
CT      Connecticut
DC      DC
DE      Delaware
FL      Florida
GA      Georgia
HI      Hawaii
IA      Iowa
ID      Idaho
IL      Illinois
IN      Indiana
KS      Kansas
KY      Kentucky
LA      Louisiana
MA      Massachusetts
MD      Maryland
ME      Maine
MI      Michigan
MN      Minnesota
MO      Missouri
MS      Mississippi
MT      Montana
NC      North Carolina
ND      North Dakota
NE      Nebraska
NH      New Hampshire
NJ      New Jersey
NM      New Mexico
NV      Nevada
NY      New York
OH      Ohio
OK      Oklahoma
OR      Oregon
PA      Pennsylvania
RI      Rhode Island
SC      South Carolina
SD      South Dakota
TN      Tennessee
TX      Texas
UT      Utah
VA      Virginia
VT      Vermont
WA      Washington
WI      Wisconsin
WV      West Virginia
WY      Wyoming
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18797604

in general, your database may benefit from both tables.   A table of  state_abbrev and State_name (no need for a stateID, the state_abbrev is your Primary Key).   A table for counties  with columns  countyID, state_abbrev and county_name.

0
 
LVL 2

Author Comment

by:ostashenp
ID: 18797611
Then I would just join the tables to compare correct ?
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18797641

 well, you have the state_abbrev in the file, if you get these tables set up the way we've discussed, you only need to fetch from counties table, because it has the state_abbrev and the county name.

 The problem is getting those tables setup.   I gave you the list of states, you would need to create/populate a table of state_abbrev and county names.  Hope you have a list of those... otherwise perhaps find it on the internet or use that flat file to populate it..
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18797643
I am doing that right now, but I am having trouble getting SQL Enterprise Manager installed.  First time I have used it, I am a mysql guy...
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now