asked on

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:

<html>
<head>
<title>t</title>
<body>
The Following Jobs are the ones that are going to be added to the database and are being queryed from 
<a href="www.test.com/test.txt">Job Source</a> 
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">
<tr>
<td>Job ID</td>
<td>Job Title</td>
 <td>Location</td>
<td>State</td>
<td>Company Name</td>
<td>JobField1</td>
<td>Website</td>
 <td>Jobfield2</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">#JobId#</td>
<td>#JobTitle#</td>
<td>#Location#</td>
 <td>#State#</td>
 <td>#CompanyName#</td>
<td> #JobField1#</td>
 <td> <a href="#website#" target="_blank">#Website#</a></td>
 <td>#JobField2# </td>

</tr>

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

 
 

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

gdemaria

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?

ostashenp

ASKER

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

gdemaria

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

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

ostashenp

ASKER

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 ?

gdemaria

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?

ostashenp

ASKER

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 ?

gdemaria

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

gdemaria

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

gdemaria

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.

ostashenp

ASKER

Then I would just join the tables to compare correct ?

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ostashenp

ASKER

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