1 Reply Latest reply on May 15, 2007 11:23 AM by ace4u


      DB Structure
      memberID [PK] (int)
      company name, etc, etc. (varchar(MAX))

      MemberID [FK_ElectricalContractorInfo.memberID] (int)
      contractorZipID [PK] (int)
      zipID [FK_dbo.ZipCodes.zipID(numeric(18, 0))

      zipID [PK](int)
      zipCode (nvarchar(MAX))

      dbo.ZipCodeDatabase (read only, downloaded from web. Contains 42,000 zips on seperate rows.))
      zipCode [PK](int)
      Latitude (float)
      Longitude (float)
      Zip_Class (nvarchar(255))
      City (nvarchar(255))
      Sate_Number (float)
      County_Number (float)

      OK the consumer goes to my index.cfm page and enters thier zip code to find a member in thier area. I got most of that worked out. The problem I'm running in to is the member enrollment stage. The INSERT part.
      Let me start by saying that Member- AZADI is mostly responsible for the progress that I have made to date but I have fallen out of communication with him somehow.
      My main problem is that I can't seem to make the primary and foreign keys work nor do I have any idea what I'm doing here. On my page that inserts the data, I don't know if I have to make a statement to insert into the PK or FK or where.
      The parts on this page that I'm about to show you that make sense are mostly due to Azadi and the parts that don't- are my mess.
      The contractor goes to www.electriciansnet.com/city.cfm and fills out a few lines including their city. When submitted to contractor.cfm, form.city querys ZipCodeDatabase and returns all of the matching zips to form.zipCodes (which is not quite good enough because most contractors have more than 1 city in thier service area but we'll worry about that later). Anyway, they fill out the rest of the form and submit to enroll.cfm. enroll.cfm inserts all of the basic data into electricalContractorInfo. It also loops through the form.zipCodes and indexes them to dbo.zipCodes. That is where the problem starts because it did insert the zips where I told it to before I started redesigning the tables but it inserted them in a comma delimited format. I thought "GREAT!" But I was wrong. Back on index.cfm, the only results that would be returned is the rows that had only 1 zip in them. It did not read the results between the commas. So, I tore it down and tried changing the tables to reflec a zipID and contractorZipID. I think you gurus call that relational database normalization. The problem is, like I said, I have no idea how to reference these tables in my enroll.cfm. So as stupid as it may look, I'm going to show it to you guys so that maybe you can help. Note: whatever changes I need to make to the DB -I do directly in SQL Studio 2005.


      <!--- get inserted Member's userID --->

      <cfquery name="getMemberID" datasource="#enet#">
      SELECT (MAX(memberID)+1) AS memberID FROM dbo.ElectricalContractorInfo
      <cfquery name="getZipID" datasource="#enet#">
      SELECT (MAX(zipID)+1) AS zipID FROM dbo.zipCodes
      <!--- Insert all stats into electricalContractorInfo --->
      <cfquery name="insertData" datasource="#enet#">
      INSERT INTO dbo.ElectricalContractorInfo (memberID, companyName, StreetNumber, City, State, Contact_First, Contact_Last, Contact_Title, Contact_Phone, Contact_Email, Contact_URL, CellNumber, NetworkID, Slogan)
      VALUES (#getMemberID.memberID#, '#Form.company#', '#form.address#', '#form.city#', '#form.states#', '#Form.first_name#', '#form.last_name#', '#form.title#', '#form.PhoneO#', '#form.email#', '#form.url#',
      '#form.cell_phone#', '#form.carrier#', '#slogan#')
      <!--- Tbl_contractorZip--->

      <cfquery name="insertZipData" datasource="#enet#">
      INSERT INTO dbo.contractor_Zip (MemberID, zipID)
      VALUES ('#getMemberID.memberID#', '#getZipID.ZipID#')

      <!--- Tbl_zipCodes--->
      <!--- loop through submitted zip codes and insert them --->
      <cfloop index = "ListElement" list = "#form.zipCodes#"delimiters="#chr(10)##chr(13)#">

      <cfquery name="insertZipCodes" datasource="#enet#">
      INSERT INTO dbo.zipCodes (contractorZipID)
      SELECT CompanyID FROM tblCompany WHERE CompanyID NOT IN(SELECT CompanyID FROM tblNotes)
      INSERT INTO dbo.zipCodes (zipID, zipCode, contractorZipID)

      VALUES ('#getZipID.zipID#', '#ListElement#', '#CreateUUID()#') (just reaching here)

      Should the memberID, zipID, and contractorZipID all just be the same number in ascending order i.e. every insert 1,1,1 and 2,2,2 etc,etc,etc.?
      Why contractorzipID?

        • 1. Re: Keys
          ace4u Level 1
          Add On
          Keep in mind that the actual zip codes are anything but unique. Meaning that every zipcode can have up to 3 members and every contractor can have as many as 50-60 zip codes in their service area. Thats the "get to later" problem because the zip codes that print on to contractor.cfm may or may not be correct and probably will not be enough. Probably need to figure out how to query zipCodeDatabase for all zips matching county_number 51,52 and maybe 53 depending. Just as example.