How to do multiple inserts
Just recently I was been working on an application, where at one stage the user had to enter multiple records from one form into one table, ideally and for ease of use, in one go. In the following I will be describing how I accomplished this.
The best way to explain this is by way of an example (note: the sample Database and code can be downloaded at the end of this tutorial). First off we create a form where a user can specify his name and the Operating systems he knows, for which the code looks like this:
<cfquery datasource="test" name="SelectOS"> Select OS_ID, OS_Name From OS </cfquery> <html> <head> <title>Multiple Inserts</title> </head> <body> <form action="multipleinserts.cfm?Insert=Yes" method="post"> <table> <tr align="center"><td colspan="2"><b>Enter details</b></td></tr> <tr> <td align="right">User Name:</td> <td align="left"><input type="text" name="User_Name" size="15"></td> </tr> <tr> <td align="right" valign="top">Select the OS(s) you know:</td> <td align="left"><select multiple name="OS_ID"> <cfoutput query="SelectOS"> <option value="#OS_ID#">#OS_Name#</option> </cfoutput> </select> </td> </tr> <tr align="center"> <td colspan="2"> <pre> <input type="submit" value="Insert Multiple Records"> </pre> </td> </tr> </table> </form> </body> </html>
Let’s take a quick look at this code. First off I run a query that retrieves the list of OSs stored in the database, in order to populate the drop down list from which the user will make his selection. Then I have added the form tag, which as an action calls the same page again and specifies the parameter Insert=”yes” (more about this in a moment). Next for estical reasons I create a table. The first row represents the table header. The second row enables the user to enter his name. The third row is the drop down menu, which in turn is populated the results of the query I ran at the beginning of the page. In order to specify multiple selects you have to specify the option multiple, when you first define your select tag. The remaining parameters are the same as normally used. The last row is for the submit button.
So far so good, the user specifies his name and selects the OSs he knows by clicking on the relevant ones and pressing the Ctrl key at the same time. The he hits the submit button. Now we turn our attention to the structure of our page and the Insert=”yes” parameter. Take a look at the following section of code:
<cfif #ParameterExists(Insert)# IS "Yes"> The Insert statement will go here <cfelse> The form code discussed above goes here </cfif>
What I have done here is made use of the <cfif> tag. If the parameter Insert exists the page will run the code underneath that statement, else it will display the form. I.e. when you first load the page it will display the form, because the URL will look something like this http://127.0.0.1/multipleinserts.cfm. However once the user has submitted his information, the URL will look something like this http://127.0.0.1/multipleinserts.cfm?Insert=yes. The URL passes the parameter Insert to the page called and specifies its value as yes. This is then interpreted by the <cfif> tag, which in turn executes the code stored there. So far everything clear? (Probably not, but you can read up on the <cfif> tag in the Cold Fusion documentation and I might even do a small tutorial on it sometime soon). Onwards to our Insert code.
<CFLOOP INDEX="ListElement" LIST="#Form.OS_ID#">
<CFQUERY Name="#ListElement#" DataSource="test">
INSERT INTO User_Details (User_Name,OS_ID)
VALUES ('#Form.User_Name#','#ListElement#')
</CFQUERY>
</CFLOOP>
<html>
<head>
<title>Multiple Inserts</title>
</head>
<body>
<center><h3>Your details have successfully been entered!</h3></center>
</body>
</html>
Right now we come to the interesting bit. Here we make use of th <cfloop> tag, because we are going to loop over our insert statement for as many times as OSs have been specified (for more information on <cfloop>, please consult your Cold Fusion documentation). OK, so what happens? First off we define an index for our <cfloop> and this index will be composed of a list of items specified by the selection. If we were to look at the code at this point in time it would look something like this (depending on the selection of course):
<CFLOOP INDEX="ListElement" LIST="1,2,4,6">
1,2,4,6 are the OS_ID values which I have specified in the drop down list from the form to be stored in the table. In this case we have four inserts to carry out. Next you’ll find the Insert query, whose name has to be defined dynamically as it has to be unique. You can also see that it takes its name from the list element that it is currently processing, so in the first instance its name will be 1. The insert statement will insert the user name specified in the form in the User_Name field and the list element in the OS_ID field in the table User_Details. This will continue until all list elements have been processed. In this example this will result in four records being added to the User_Details table. The following HTML code simply announces that the information has been processed.
Well that’s it. I hope you found this information useful, all that is left now is to provide you with the link to download the sampe code and database. MultipleInserts.Zip