coldfusion - getting array into database

user_13021663 0
Viewed 220

i am attempting to pull data from one website to insert into the database of another website in coldfusion - have gotten the data to the second site - and studied all the documentation i can find on coldfusion struct commands and i just seem to go around in circles -

it is just a simple array that i want to upload into a table - the tables from the two websites are identical -

evidently i need a very simple step by step instruction to understand how to get the data into a value i can upload into the second database - can someone please help

this is the code i used to get the data - and an image of the results - if the image doesn's show the url is http://www.tectrics.com/english/map/getjson.cfm

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>
<cfset meetlist=DeserializeJSON(CFHTTP.FileContent)>
<cfdump var="#meetlist#">

array image

coldfusion struct arrays
2 Answers

You're off to a good start. The contents of your variable meetlist contains an array of structs. So all you'll need to do is loop through the array of structs and perform an insert for each array element. Building off your existing code, here's what you will need.

Some improvements on this code sample should have <cftry>, <cfcatch> and <cftransaction> blocks, but this should be enough to get you started.

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>
<cfset meetlist=DeserializeJSON(CFHTTP.FileContent)>
<cfdump var="#meetlist#"> <!---  Remove this dump --->

<cfloop array="#meetlist#" index="i">
    <!---  each loop iteration will contain a struct called "i". Do an insert for each iteration of the loop --->
    <cfquery datasource="mydatasource">
        INSERT INTO table_name (
            attended, 
            childcare, 
            childfriend,
            ...
        )
        VALUES (
            <cfqueryparam value="#i.attended#" cfsqltype="cf_sql_integer">, <!---  use the appropriate cfsqltype that matches the column's data type --->
            <cfqueryparam value="#i.childcare#" cfsqltype="cf_sql_varchar">,  <!---  use the appropriate cfsqltype that matches the column's data type --->
            <cfqueryparam value="#i.childfriend#" cfsqltype="cf_sql_varchar">,  <!---  use the appropriate cfsqltype that matches the column's data type --->
            ...
        )
    </cfquery>
</cfloop>
user_120311190

I like user12031119's answer. But I would tighten it up some more

<cfset urlAddress="http://www.tectrics.com/english/map/localjson.cfm">
<cfhttp url="#urlAddress#" method="GET" resolveurl="Yes" throwOnError="Yes"/>

<cfscript>
meets = DeserializeJSON(CFHTTP.FileContent);

for(meet in meets) {
    EntitySave(EntityNew("table_name", meet)); 
}
</cfscript>

Entity

I would have to enable ORM and create an Entity definition. Look up ColdFusion Entities and ORM on how to do that

user_18458690
Related