?
Solved

Creating database records from multiple lists

Posted on 2005-03-28
10
Medium Priority
?
158 Views
Last Modified: 2013-12-24
I have looped over several URL's with cfhttp and used several regex to parse the URL's for job data such as titles, salary, location, interview date etc. At the moment my code puts all the job titles in the joblist, all salaries in the salarylist etc.
I now want to make records from this data, each record should have an entry for titles, salary, location, interview date etc. How can I make the individual records for the database from the different lists?
0
Comment
Question by:VHSB
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:rob_lorentz
ID: 13643661

provided that all your lists are the same length you could do something like the following.

<cfloop from="1" to="#listLen(jobList)#" index="x">
      <cfquery name="insJobRecord" datasource="yourDSN">
            insert into yourJobTable
                  (title, salary, location, interviewDate)
            values
                  (<cfqueryparam value=listGetAt(jobList, x) cfsqltype="CF_SQL_VARCHAR">,
                  <cfqueryparam value=listGetAt(jobList, x) cfsqltype="CF_SQL_DECIMAL">,
                  <cfqueryparam value=listGetAt(locationList, x) cfsqltype="CF_SQL_VARCHAR">,
                  <cfqueryparam value=listGetAt(intDateList, x) cfsqltype="CF_SQL_DATE"> )
      </cfquery>

</cfloop>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13644550
well that depends.

Can the records be any combination of the above?  Or is the first title matched only with the first salary and the first location and the first interview date?

Are you looking at automating this processs (i.e. a cross join)
Or at making a form that people can select from the salary choices which one, the title choices, etc?
0
 

Author Comment

by:VHSB
ID: 13645731
mrichmon, It needs to be the first title matched with the first salary, location etc.

At the moment I just want to get the data for each job advert into indivldual records in the database. Once I have done that Ill be trying to allow users to free text search the jobs.

Regards
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 35

Accepted Solution

by:
mrichmon earned 1000 total points
ID: 13646066
In that case, Assuming that there are no missing entries in your list then the method posted by rob_lorentz should work with a few minor corrections:

<cfloop from="1" to="#listLen(jobList)#" index="x">
     <cfquery name="insJobRecord" datasource="yourDSN">
          insert into yourJobTable
               (title, salary, location, interviewDate)
          values
               (<cfqueryparam value="#listGetAt(jobList, x)#" cfsqltype="CF_SQL_VARCHAR">,
               <cfqueryparam value="#listGetAt(salarylist, x)#" cfsqltype="CF_SQL_FLOAT">,
               <cfqueryparam value="#listGetAt(locationList, x)#" cfsqltype="CF_SQL_VARCHAR">,
               <cfqueryparam value="#DateFormat(listGetAt(intDateList, x), 'MM/DD/YYYY')# #TimeFormat(listGetAt(intDateList, x), 'h:mm tt')#" cfsqltype="CF_SQL_VARCHAR"> )
     </cfquery>

</cfloop>

Changes:

Quotes needed on values
# needed on listGetAtfunction
There was jobList where it should be salarylist
CF_SQL_DECIMAL is known to have some bugs - use CF_SQL_FLOAT instead - it will still be inserted as a decimal but you avoid the CF bug.
I prefer CF_SQL_VARCHAR and using dateformat for dates rather than the CF_SQL_DATE type
I also added in the time in case you want that since it is an interview....
0
 

Author Comment

by:VHSB
ID: 13647965
Im using MySQL will this make any difference to the commands?
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13649750
nope (well sometimes MySQl requires the schema as well as table name, but I think CF takes care of this for you)

the cfqueryparm should take care of the correct processing/escaping for different databases.

There should be no difference to the above code needed.
0
 

Author Comment

by:VHSB
ID: 13655668
mrichmon, Im having a problem adding some of tge data to the database.
For example: My location list consists of entries such as, Dolygaer and Dare Valley Country Park, Wales
and my salaray list consists of entries such as, 9.20 per hour or £12,000 - £14,000 pa.
Im assuming my problem is down to the special characters in the data? Is there a data type that can deal with these data types?

Regards
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13656648
I don't think that the characters are due to the cfqueryparam.  I thought that it could deal with those characters.

But is your database set up to handle those special characters?

One way to tell is try to do the insert without the cfqueryparam.

If it doesn't work then you know that cfqueryparam is not the problem.

If it does work then you know that cfqueryparam is the problem and you have to manually worry about escaping any special values.
0
 

Author Comment

by:VHSB
ID: 13657226
MRICHMON, sorry to drag this out but could you explain what this error message means?
The list contains multi-word job descriptions.

Invalid list index 6.  
In function ListGetAt(list, index [, delimiters]), the value of index, 6, is not a valid as the first argument (this list has 5 elements). Valid indexes are in the range 1 through the number of elements in the list.  
 
The error occurred in C:\CFusionMX\wwwroot\Project\1.cfm: line 97
 
95 :           values
96 :                (<cfqueryparam value="#listGetAt(idList, j)#" cfsqltype="CF_SQL_VARCHAR">,
97 :
 
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13663212
It means that idList has only 5 elements and you are trying to access a element in position 6 which does not exist.

This can happen if jobList has 6 elements, but idList has only 5.

Remember early on how I mentioned that you would need to make sure that there were no missing entries in any of the lists for this method to work....
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

569 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