ColdFusion inserting different data based on different inputs

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:

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

<table width="100%"  border="1" cellspacing="2" cellpadding="3">
   <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>
<CFSET crlf = "~">

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


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,"|");



 <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>


<br />
<br />


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

Who is Participating?

 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..

 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

<CFLOOP INDEX="orec" LIST="#cfhttp.FileContent#" DELIMITERS="#crlf#">
  <cfif len(trim(orec)) >
      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,"|");

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

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

ostashenpAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.


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

<cfset variables.county = listRest(getCountries.county, "-")>
ostashenpAuthor Commented:
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 ?

 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?

ostashenpAuthor Commented:
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


Would that make it easier ?        

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

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

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.

ostashenpAuthor Commented:
Then I would just join the tables to compare correct ?
ostashenpAuthor Commented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.