mass insert using textfield? MySQL

[stillwaiting]

Is it possible to insert multiple records at once using a textfield? One of the problems I see first off is the use of tabs in a webform. Any
thoughts? Is there a more efficient way?

stillwaiting

[cf_menace]

Do you mean, "enter a list of values into this field" and each value is a new
record in the db? It's possible, just depends on how you do it. You can have
people enter a list of things, seperated by a comma, semi-colon, etc into an
<input type="text"> field. Or you can have people enter data into a <textarea>,
puting each item on a new line.

Either way, you just have to read the value of the field in and parse it as a
list using the delimeter you set. In the case of the <textarea>, the delimeter
would be the newline character.

HTH

[stillwaiting]

Okay, that is the general idea but how do I tell MySQL that the comma(or whatever) separates the data and what is the SQL that is used in the insert
statement?
I looked through the MySQL manual and they only tell how to specify the delimiter when doing LOAD DATA LOCAL INFILE.

I have a table with 4 fields, the first being an auto number that doesn't get a value from the form. I want the user to be able to write the
information in the text box like this:

link1, link1b, link1description
link2, link2b, link2description
link3, link3b, link3description

each line needs to be a new record with the commas specifying a new column.

I guess I just need a little more to go on. Must be short on brain cells.

stillwaiting

p.s. Thanks for the help so far.

> Do you mean, "enter a list of values into this field" and each value is a new
> record in the db? It's possible, just depends on how you do it. You can have
> people enter a list of things, seperated by a comma, semi-colon, etc into an
> <input type="text"> field. Or you can have people enter data into a <textarea>,
> puting each item on a new line.
>
> Either way, you just have to read the value of the field in and parse it as a
> list using the delimeter you set. In the case of the <textarea>, the delimeter
> would be the newline character.
>
> HTH
>

[mxstu]

>how do I tell MySQL that the comma(or whatever) separates the data and

>what is the SQL that is used in the insert statement?

As cf_menace mentioned, you will need to read data in the form variable
yourself. Then split the variable data into rows and columns and construct
your sql statements. Here is a simple example using a text area, it needs
validation etc, but you should get the idea...







<!--- form code --->
<form method="post">
<textarea name="userData" cols="80" rows="25">
link1, link1b, link1description
link2, link2b, link2description
link3, link3b, link3description
</textarea>
<input type="submit">
</form>


<!--- action page code --->
<cfset colDelim = ",">
<cfset rowDelim = chr(10)>
<cfloop list="#form.userData#" index="row" delimiters="#rowDelim#">
<cfset columns = listToArray(row, colDelim)>
<cfif arrayLen(columns) eq 3>
<cfset linkAddress = trim(columns[1])>
<cfset linkTitle = trim(columns[2])>
<cfset linkDescription = trim(columns[3])>
<cfquery name="insertData" datasource="#yourDSN#">
INSERT INTO yourTable (address, title, description)
VALUES
(
<cfqueryparam value="#linkAddress#" cfSqlType="cf_sql_varchar">,
<cfqueryparam value="#linkTitle#" cfSqlType="cf_sql_varchar">,
<cfqueryparam value="#linkDescription#" cfSqlType="cf_sql_varchar" >
)
</cfquery>
</cfif>
</cfloop>

[stillwaiting]

Thanks mxstu. That was really helpful.

stillwaiting

mxstu wrote:

>>how do I tell MySQL that the comma(or whatever) separates the data and

>

> >what is the SQL that is used in the insert statement?

>
> As cf_menace mentioned, you will need to read data in the form variable
> yourself. Then split the variable data into rows and columns and construct
> your sql statements. Here is a simple example using a text area, it needs
> validation etc, but you should get the idea...
>
>
>
>
>
>
>
> <!--- form code --->
> <form method="post">
> <textarea name="userData" cols="80" rows="25">
> link1, link1b, link1description
> link2, link2b, link2description
> link3, link3b, link3description
> </textarea>
> <input type="submit">
> </form>
>
>
> <!--- action page code --->
> <cfset colDelim = ",">
> <cfset rowDelim = chr(10)>
> <cfloop list="#form.userData#" index="row" delimiters="#rowDelim#">
> <cfset columns = listToArray(row, colDelim)>
> <cfif arrayLen(columns) eq 3>
> <cfset linkAddress = trim(columns[1])>
> <cfset linkTitle = trim(columns[2])>
> <cfset linkDescription = trim(columns[3])>
> <cfquery name="insertData" datasource="#yourDSN#">
> INSERT INTO yourTable (address, title, description)
> VALUES
> (
> <cfqueryparam value="#linkAddress#" cfSqlType="cf_sql_varchar">,
> <cfqueryparam value="#linkTitle#" cfSqlType="cf_sql_varchar">,
> <cfqueryparam value="#linkDescription#" cfSqlType="cf_sql_varchar" >
> )
> </cfquery>
> </cfif>
> </cfloop>
>