将结构元素插入数据库 [英] inserting structure elements into database

问题描述

我正在循环一个结构数组，如下：

I am looping through an array of structures as follows:

<cfloop array="#cfData#" index="i">
  <cfif structKeyExists(i, "date")>
    <cfset counter++>
   <cfoutput>#counter#</cfoutput> Date  is: <cfoutput> #i.date#</cfoutput> <br/>
  </cfif>
</cfloop>

现在，我必须将Date和其他键的值插入到我的数据库中，如下：

Now, I have to insert the values of Date and other keys into my database and I am attempting it like the following:

<cfquery datasource="mydb" dbname="Stats">
    INSERT INTO mydatabase
    VALUES  
       <cfif structKeyExists(cfData, "date")>
           <cfset counter++>#cfData.date#
       </cfif>
       ,
       <cfif structKeyExists(cfData, "delivered")>
           <cfset counter1++>
           #cfData.delivered#
       </cfif>
       ,
       ... and so on for other key values...
  </cfquery>

这是将它插入MySQL数据库的正确方法吗？

Is that a correct way of inserting it into the MySQL database?

PS：你也可以参考我以前的线程获取更多信息：

P.S: You can also refer to my previous thread for more information:

检查结构中的键存在

UPDATE：

其实，为了避免columlist不匹配，我决定测试它如下

Actually, in order to avoid the columlist mismatch, I decided to test it as following

<cfset KeyList   = "delivered,
                    unique_open,
                    spamreport,
                    drop,
                    request,
                    bounce,
                    deferred,
                    processed,
                    date,
                    startdate,
                    enddate,
                    open,
                    blocked">

<cfloop from="1" to="#arraylen#" index="i">

        <cfloop list="#KeyList#" index="colItem">    
        <cfif structKeyExists(cfData[i], "colItem")>
        <cfoutput>#cfData[i].colItem#</cfoutput>

      <cfelse>
         NULL
      </cfif>
        <cfif colItem neq listLast(KeyList)>,</cfif> 
    </cfloop>
    </cfloop>

仍在浏览器中显示NULL。

Still it shows NULL in the browser.

However, when I test it like the following , I get correct results for delievered: 5 NULL 12 2 1 12 1

<cfloop from="1" to="#arraylen#" index="i">


        <cfif structKeyExists(cfData[i], "delivered")>
        <cfoutput>#cfData[i].delivered#</cfoutput>

      <cfelse>
         NULL
      </cfif>


    </cfloop>

使用KeyList元素有什么问题？

What's wrong with using KeyList elements ?

推荐答案

这个答案是基于在问题中插入的数据连接的数组

This answer is based upon the array of struts linked to in the question being the data inserted

如果你知道你的预期结构值和名称的列名相同，这将是一个解决方案：

If you know your expected struct values and name your column names are the same, this would be one solution:

<!--- set your column names in a list --->
<cfset columnList = "date,delivered,open,processed,request,unique_open">

<cfquery datasource="mydb" dbname="Stats">
INSERT INTO mydatabase (#columnList#)
VALUES
  <!--- loop through your array --->
   <cfloop from="1" to="#arrayLen(cfData)#" index="i">
   (
    <!--- loop through the list of columns and see if they exists in the struct --->
    <cfloop list="#columnList#" index="colItem">
      <cfif structKeyExists(cfData[i], colItem)>
        <cfqueryparam value="#cfData[i][colItem]#">
      <cfelse>
         NULL
      </cfif>
        <cfif colItem neq listLast(columnList)>,</cfif>
    </cfloop>
    )<cfif i neq arrayLen(cfData)>,</cfif>
  </cfloop>
</cfquery>

在这种格式下，我建议使用 #createODBCDate code>，然后再插入日期

Also in this format i would suggest using #createODBCDate()# before inserting the dates

这篇关于将结构元素插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！