I'm building a ColdFusion based site ([url removed, login to view]). The consumer, looking for an electrician in their area, inputs their zip code on my home page to find an electrician locally. I have most of the basics down, i.e. "where zip = [url removed, login to view]". I also have the basic inerts down, i.e. "insert into members (companyName,...) Values ([url removed, login to view],...) The problem that I have been working on for weeks without much success is that any member can have 100 + zips and any zip can have up to 3 members. I have figured out how to loop through the zip in my form's combo box and insert them on individual rows but I cannot figure out how to manipulate the data beyond that point. Nor can I figure out how to insert into the foreign keys correctly. Basic details-Consumer: The end result should be: If the consumer enters 33601 into [url removed, login to view] they should get results for every member that has that zip as one of their zips but that member may also have 33602 or 33699 or .... (As an electrical contractor in Tampa, Fl. I have 85 zips in my service area.)
My preference would be a customized tutorial that shows me how to build my site but, of that's not affordable, practical, I need: Functioning set of coldfusion pages with switches and fail safes to keep the consumer from my coldfusion errors. 1) The consumer queries the database against their zip code on [url removed, login to view] 2) If there is a match they are directed to a form ([url removed, login to view]) 3) If the form is validates, it submits to [url removed, login to view] which is dynamically addressed to the member's cell phone as a text message based on the consumer's query. (This is already functional) 4)[url removed, login to view] actions to [url removed, login to view] which displays a dynamic list of the member(s) they just submitted to. contd from top box- Basic details-Contractor(member): 1)Through various types of promotion the member is introduced to [url removed, login to view] where he/she inputs company name, address, city and picks a state from the dropdown. Also has 2 more city fields in an attempt to catch the member's entire service area. 2)[url removed, login to view] actions to [url removed, login to view] which also queries a (treated-as-read-only) table in the db which contains 4200+ zip codes against the enrolling member's state and city. 3) [url removed, login to view] holds a combo box of coma delimited zips. 4) the member inputs the remainder of their details such as the cell phone carrier dropdown which has 6 options representing a number from 1 to 6. This number represents a row in a 6 row table of carriers (part of the way mailer_1 is dynamically addressed). 5) [url removed, login to view] actions to [url removed, login to view] which is invisible to the member. If [url removed, login to view] validates, all info is inserted into [url removed, login to view], there is an index loop that inserts the stored zip codes into dbo.zipCode. This is where I have been stuck for days. What is supposed to happen is: 6) there's a 3rd table that is supposed to hold the zipID for every row containing 1 zip each in the zipID column and a memberID for for the submitting member's zips in the memberID column in dbo.zipCodes.
I'm using CFMX7 and SQL 2005 with a 3 table, relational database on my local web server which is running Server 2k3. IIS