Insert each item in a comma delimited textfield as its own record?

[lovewebdev]

I have a textfield where users may input comma delimited text such as keywords. I want to insert each keyword into its own records.

[Newsgroup_User]

lovewebdev wrote:
> I have a textfield where users may input comma delimited text such as keywords. I want to insert each keyword into its own records.

Use any of a number of list functions and tags that ColdFusion provides
for such processing.

http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_13.html
http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_j-l_15.html

I'm guessing that <cfloop list=""...> is probably a good place to start.

[lovewebdev]

Can you go in a little more depth. How to use cfloop to accomplish this task?

[Dan_Bracuk]

<cfloop list="#yourlist#" index="i">
insert query goes here.

or

<cfquery>
insert into yourtable
(field1, field2, etc, keyword)
<cfloop list="#yourlist#" index="i">
select #value1#, #value2#, etc, #i#
from some_small_table
union
</cfloop>
select dummy1, dummy2, etc, dummykeyword
from some_small_table
where 1 = 2

[lovewebdev]

I don't understand all the parameters, and why there are 2 select statements and why there are 2 tables. Also I dont see values specified in your insert statement.

It's one comma delimited textfield in a form along with another value going into a table. I'm going to mess with it and see what I get. I'm using SQL server

[lovewebdev]

Thanks dan. For some reason I don't understand the second code block. Basically there's just a textfield where users will input a comma delimited list. I need to insert each item as it's own record along with another field. So the form is like:

<input type="text" name="keywords"> this will be a comma delimited list
<input type="hidden" name="someid" value="#URL.someid#">
This will stay the same

Needs to go in as:

foreign key id Keyword
1. 235 keyword1
2. 235 keyword2
3. 235 keyword3

Instead of:
Foreign key id. Keyword
1. 235 keyword1, keyword2, keyword3

[Dan_Bracuk]

Both of my suggestions will accomplish what you said you want. The better one depends on the type of database you are using. With oracle, the loop inside the query is faster. With redbrick, the query inside the loop is faster. I've not tried it with any other db software.

What part did you not understand? Putting loops inside queries? Union queries? Insert queries without the "values" keyword?

[lovewebdev]

I tried the following:

<cfloop list="#FORM.keywords#" index="i">
<cfquery ...>
INSERT INTO table(keyword,someid)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.keywords#" list="yes">, <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.someid#">)
</cfquery>
</cfloop>

and I get this error:
Error Executing Database Query.
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

I dont get the error when i input a single keyword in the box.

[Dan_Bracuk]

The specific error is probably caused by the commas in your list. That is also a logic problem because you are trying to insert the entire list several times instead of each list element once.

Look at your debugging information to see what sql is being generated.

[lovewebdev]

I removed the list attribute from the cfqueryparam and it works BUT it does it incorrectly. It submits several records but inputs the entire delimited list of keywords in each record rather than one item per record.
It's going in like this??

foreign key id Keyword
1. 235 keyword1,keyword2,keyword3
2. 235 keyword1,keyword2,keyword3
3. 235 keyword1,keyword2,keyword3

[Dan_Bracuk]

Your problem is that you are not using cfloop correctly. That index attribute is there for a reason. If you don't know what it does, output it.

[lovewebdev]

I used it according to your example. How do you do it correctly? I'm not the best coder.

[lovewebdev]

I see what I was doing wrong. I was inserting the entire list again as opposed to the index value. Here is the correct code:

[Tones411]

On a side note, you can do multiple INSERTS in one cfquery tag in two different ways.

1. Put the loop inside the cfquery tag but around the INSERT statement, and put a semicolon after the insert statement. (But it's probably not good practice since I think I ran into a limit when trying it on an MS SQL table.)

2. Perform one INSERT statement with multiple sets of values. (See Andy Jarret's, post at http://www.andyjarrett.co.uk/andy/blog/index.cfm?mode=entry&entry=CC4DC627-3048-28EB-0E8714AA882D342... Also it looks like Pete Freitag tried out "...a couple hundred thousand rows..." with Andy's method on MySQL and got an error. ( http://www.petefreitag.com/item/379.cfm)