(1) download from multiple tables in a database to a page including repeat regions, update info, & upload to dbase, (2) add completely new records, AND (3) delete old records

I am
(1) dynamically filling a form on a webpage (including the use of repeat regions) from multiple tables in a database, make changes to that information, then upload the updated material back into the database tables,
(2) be able to add a completely new record to the database tables, AND
(3) delete old records from the database tables.

I am using Dreamweaver 8 with a Coldfusion server and an extension from WebAssist called DataAssist.

I have used the WebAssist extension to create admin pages that allow me to update a single table in a database.  However, all the information about a single product is spread across 5 tables in our database.  The extension only allows modification to 1 table at a time -- we want to combine it all into one set of 5 admin pages: search, results, update, add, and delete.

The site we are creating is for a company that sells Bible study materials in the forms of courses.  The tables are:
courses (contains a uniue courseID and any general info about the course -- e.g., title, number of lessons, instructor, etc),
categories (contains a unique categoryID and information about all of the 24 available categories),
courseCategories (desiginates which categoryID's a specific courseID fits into),
volumes (contains a unique volumeID and specific information for each volume (product) available in a specific courseID including its formatID, title, price, etc., and
formats (contains a unique formatID & title for all 9 available formats, i.e. 01, DVD; 02,Audio; etc.)

The challenge is that each courseID can have more than one categoryID attached, and have more than one volume (and each volume will have a single format that may be different from each other volume in a course).

I have been able to modify the search & results pages to allow access to all courses.
I have modified the update & details pages to display all information from the dbase on a specifc course AND modified the update page to generate text boxes & drop down menues that are prepopulated with the dbase info.  I do this by using repeat regions for the categories and volumes sections.

Now I am at a loss on how to (1) move this information back to the database, (2) create an entirely new record that will pull any autogenerated numbering from the database being used, and (3) how to remove an entire record (course), and all of its attendant information from every applicable table.

I am using thiscode to do my repeat for the categories (it is 1 row out of a 3 columnn table).

<cfset variables.catSelect = rsCourseCategories.categoryID>                          <!---this cfset is to make a variable for my cfloop filtered by courseID--->
<cfloop query="rsCourseCategories">                                                           <!---recordset to get the categories from table courseCategories--->
    <cfif catSelect NEQ rsCourseCategories.categoryID>                                   <!---adjusts the cfset variable to the next categoryID for the courseID--->
        <cfset variables.catSelect = rsCourseCategories.categoryID>
        <th class="WADADataTableHeader">Categories:</th>
        <td align="left" class="WADADataTableCell"><cfoutput>#rsCourseCategories.name#</cfoutput></td>           <!---outputs the name for the categoryID--->
        <td class="WADADataTableCell">
            <select name="categoryID" id="categoryID">                                                                                           <!---selectable menu--->
                <cfoutput query="rsCategoriesSelection">                                                                                            <!---dynamic menu labels/values--->
                        <option value="#rsCategoriesSelection.categoryID#" <cfif (isDefined("catSelect") AND rsCategoriesSelection.categoryID EQ catSelect)>selected="selected"</cfif>>#rsCategoriesSelection.name#</option>                                                                   <!---dynamic bind for menu selection--->

I am using this code to do my repeat for the volumes (it is a merged row containing a repeated 4 row, 8 column table).

    <th colspan="3" class="WADADataTableHeader">
    <cfset variables.intLastFormatID = rsVolumes.FormatID>                         <!---this cfset is to make a variable for my cfloop filtered by courseID--->
    <cfloop query="rsVolumes">                                                                  <!---recordset to get the categories from table courseCategories--->
        <cfif intLastFormatID NEQ rsVolumes.FormatID>
           <cfset variables.intLastFormatID = rsVolumes.FormatID>                 <!---adjusts the cfset variable to the next categoryID for the courseID--->
        <table width="100%" cellspacing="3">
            <tr align="left">                                                                            <!---table headers--->
                <td width="8%">VOLUME ID</td>
                <td width="13%">Format</td>
                <td width="11%">Display order </td>
                <td width="7%">Pieces</td>
                <td width="9%">Price</td>
                <td width="31%">Title</td>
                <td width="16%">Author</td>
                <td width="5%">Pages</td>
            <tr align="left">                                                                        <!---dynamic text from dbase--->
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.volumeID#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.label#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.displayOrder#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.pieces#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.price#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.title#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.author#</cfoutput></span></td>
              <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.pages#</cfoutput></span></td>
            <tr align="left">                                                                       <!---textboxes & menus containing dynamic info--->
                <td><span class="WADADataTableCell"><cfoutput>#rsVolumes.volumeID#</cfoutput></span></td>
                <td><span class="WADADataTableCell">
                    <select name="formanottID" id="formanottID">
                        <option value="" <cfif (isDefined("intLastFormatID") AND "" EQ intLastFormatID)>selected="selected"</cfif>>(select)</option>
                        <cfoutput query="rsFormatsSelection">
                            <option value="#rsFormatsSelection.formatID#" <cfif (isDefined("intLastFormatID") AND rsFormatsSelection.formatID EQ intLastFormatID)>selected="selected"</cfif>>#rsFormatsSelection.label#</option>
                <td><span class="WADADataTableCell">
                    <input name="text" type="text" value="<cfoutput>#rsVolumes.displayOrder#</cfoutput>" size="5"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="pieces" type="text" id="pieces" value="<cfoutput>#rsVolumes.pieces#</cfoutput>" size="3"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="price" type="text" id="price" value="<cfoutput>#rsVolumes.price#</cfoutput>" size="7"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="title" type="text" id="title" value="<cfoutput>#rsVolumes.title#</cfoutput>" size="50"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="author" type="text" id="author" value="<cfoutput>#rsVolumes.author#</cfoutput>" size="20"></span></td>
                <td><span class="WADADataTableCell">
                    <input name="pages" type="text" id="pages" value="<cfoutput>#rsVolumes.pages#</cfoutput>" size="4"></span></td>
            <tr align="left">
                <td colspan="8"><hr></td>                                                <!---spacer line--->

How do I (1) update the record in the database, (2) delete the record from the database, (3) add a new record to the database
Who is Participating?
well that's a lot of info. To add, update, and delete form multiple tables you will need to have multiple cfqueries, one for each table. But most of the db code will be done through sql. To have one form where the user can decide what action to perform, read below...

What I do is create an update button (type=submit), delete button (type=submit), and add button (type=submit), and if you really want to create a done button or cancel button, but that is besides the point.

Update button:
<input type="submit" name="updatebutton" value="update info">

Delete Button:
<input type="submit" name="deletebutton" value="delete info">

Add button:
<input type="submit" name="addbutton" value="add info">

Now since all these buttons are of type submit, and you can only have one page that the form will redirect the user, you need to have some if statements on the action page. e.g. <form name="form1" method="post" action="modifyDB.cfm">

This is the form that will perform the update, deletion, or present the user with a form to add a new row into the tables. You also want to have a clause that ensures a bit of security, so that someone who just typed in a random url won't make any db changes.

<cfif isdefined("form.updatebutton")>
    Place update query here

<cfelseif isdefined("form.deletebutton")>
    Place delete code here

<cfelseif isdefined("form.addbutton")>
    Place another form here for users to fill out

    No action performed. Return user to main page...


For the update query you can break down the updates into multiple queries if neccesary, one for each table. Then update the tables with their respective information from the form.
Same with delete section.

Um... Let me know if I'm on the right track...

UpekrithenAuthor Commented:
Figured it out. Thanks for your input - have some points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.