将信息插入到连接表ColdFusion中 [英] Inserting Information into a Junction Table ColdFusion

查看:329
本文介绍了将信息插入到连接表ColdFusion中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在博客帖子页面上,我向我的BlogPost表添加了Title,Body和UserID(来自作者表的外键):

On a Blog post page I add Title, Body, and UserID (Foreign Key from Authors Table) into my BlogPost Table:

<!--- Query to Insert Blog --->
<cfquery Result="blogEntry" datasource="prpblog">
    INSERT INTO BlogPosts (Title, BlogBody, UserID)
    VALUES
    (
        <cfqueryparam value='#Form.Title#' cfsqltype="cf_sql_varchar"/>,  
        <cfqueryparam value='#Form.BlogBody#' cfsqltype="cf_sql_varchar"/>, 
        <cfqueryparam value='#Form.SelectAuthor#' cfsqltype="cf_sql_numeric"/>
    )
</cfquery>

然后我想将博客包含的标签添加到连接表中

Then I want to add which tags the blog consists of into a Junction table (because of the many-to-many relationship caused by tags belonging to multiple blogs/blogs with multiple tags)

我知道如何将标签插入到联结表中,但是如何将我的标签插入到blogID到这个表中?

I know how to insert the tag into the junction table, but how would I insert my blogID into this table? The BlogID is an autonumber when a new blog is entered.

连接表列示例:

    BlogID  TagID
    10          1
    10          10
    10          13
    9           10
    9           1

<Select name="SelectTag" multiple="multiple" size="6">
<cfoutput QUERY="Tags"><option value="#TagID#">#Tag#</option></cfoutput>
</select>

<cfquery name="AddTag" datasource="prpblog">
    INSERT INTO BlogTagJunction (TagID)
    VALUES
    (
                <cfqueryparam value='#Form.???' cfsqltype="cf_sql_numeric"/>
        <cfqueryparam value='#Form.SelectTag#' cfsqltype="cf_sql_numeric"/>
    )
</cfquery>


推荐答案


我通过在博客的
插入语句之后创建一个查询语句从博客中获取博客ID I
刚刚创建:

I solved the problem myself by making a query statement after the insert statement for the blog that grabs the blog ID from the blog I just created:

这只有在 Title 列上有唯一索引时才有效。但是,它可能你甚至不需要单独的查询。如果 BlogID 是某种自动递增列(标识,自动递增,...),请使用 result cfquery的属性从您的第一个 INSERT 语句。确切的语法是db依赖。

That will only work if there is a unique index on the Title column. However, it is likely you do not even need a separate query. If BlogID is some sort of auto-incrementing column (identity, auto increment, ...), use the result attribute of cfquery to retrieve the generated ID value from your first INSERT statement. The exact syntax is db dependent.

由于 TagID 的来源是另一个数据库表,因此更容易跳过循环,并使用 INSERT / SELECT 在单个语句中生成联结表记录。你没有说明你的DBMS,但是对于SQL Server这样的。根据需要更改cfsqltypes:

Given that the source of TagID is another database table, it is simpler to skip looping and use a INSERT/SELECT to generate the junction table records in a single statement. You did not state your DBMS, but for SQL Server something like this. Change the "cfsqltypes" as needed:

  <!--- create the blog entry --->
  <cfquery result="blogEntry" ....>
       INSERT INTO BlogPost ( Title, Body, UserID )
       VALUES  
       (
         ... the values here  ....
       )
  </cfquery>

  <!--- associate the new blog entry with the selected tag id's --->
  <cfquery ....>
     INSERT INTO BlogTagJunction (BlogID, TagID)
     SELECT  <cfqueryparam value="#blogEntry.IDENTITYCOL#"  
                        cfsqltype="cf_sql_integer">
            , TagID
     FROM  YourTagTable
     WHERE TagID  IN
           (
              <cfqueryparam value="#Form.SelectTag#" 
                   list="true" 
                   cfsqltype="cf_sql_numeric"/>
           )
  </cfquery>

注意:请务必将这两个查询包含在 cftransaction 以确保数据完整性。

NB: Be sure to wrap both queries in a cftransaction to ensure data integrity.

从注释更新

您正在使用MS Access,然后使用结果属性。 CFQuery不支持MS Access的该功能。因此,您需要使用另一种方法来抓取新创建的ID。

If you are using MS Access, then using the result attribute is out. CFQuery does not support that feature for MS Access. So you need to use an alternative method for grabbing the newly created ID.

此链接中所述,几种方法来做到这一点。其中之一是向表中添加UUID列。 MS Access的另一个选项是使用 @@ identity 变量。在将记录插入到具有自动编号列的表中后,@@ identity值将包含新记录的ID。 此博客包含示例 。注意:正如我在其他线程中指出,必须在cftransaction 中加入BOTH个查询,否则无法正常工作。

As discussed in this link , there are several ways to do that. One of them is adding a UUID column to your table. Another option for MS Access is using the @@identity variable. After you insert a record into a table with an "Autonumber" column, the @@identity value will contain the ID of the new record. This blog contains an example. NB: As I noted in your other thread, you must enclose BOTH queries in cftransaction or it will not work correctly.

总之,您需要做三件事:

In summary you need to do three things:


  1. INSERT 新记录到 BlogPost 表中

  2. 抓取刚创建的新BlogID

  3. 使用第2步中的新ID将所选的tagID插入 BlogTagJunction

  1. INSERT a new record into the BlogPost table
  2. Grab the new BlogID you just created
  3. Insert the selected tagID's into BlogTagJunction using the new id from step #2

再次,使用MS Access,您不能跳过 cftransaction 在这里或它不会工作。将它们放在一起:

Again, with MS Access you cannot skip the cftransaction here or it will not work. Putting it all together:

<cftransaction>

    <!--- create the blog record --->
    <cfquery result="blogEntry" ....>
        INSERT INTO BlogPost ( Title, Body, UserID )
        VALUES  
        (
        ... the values here  ....
        )
    </cfquery>

    <!--- get the ID of the record you just inserted --->
    <cfquery name="getNewRecord">
        SELECT  @@identity AS TheNewID
    </cfquery>

    <!--- Use that new ID to associate blog and selected tag id's --->
    <cfquery ....>
        INSERT INTO BlogTagJunction (BlogID, TagID)
        SELECT  <cfqueryparam value="#getNewRecord.TheNewID#"  
                    cfsqltype="cf_sql_integer">
               , TagID
        FROM  YourTagTable
        WHERE TagID  IN
        (
            <cfqueryparam value="#Form.SelectTag#" 
               list="true" 
               cfsqltype="cf_sql_numeric"/>
        )
    </cfquery>

</cftransaction>

这篇关于将信息插入到连接表ColdFusion中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆