?
Solved

Creating database records from multiple lists

Posted on 2005-03-28
10
Medium Priority
?
153 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
What You Need to Know when Searching for a Webhost Provider
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

800 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