Display Results on page, then Insert Multiple Records into MySQL table using ColdFusion

Question

I created the following page that displays which managers have accrued time for the current month. - First, I need to display the records for reviewing, - Then check the box, hit "Submit" & add them to the database. - All from the same page or action page if required.

I've seen examples of using CFLOOP to insert multiple records, but not sure how to implement into my page since I'm displaying a "varying number" of records?

<!--- Create the Manager Monthly Accruals (Mgt) dataset --->
  <cfquery name="rsLeaveTimeManagerAccruals" datasource="care">
   SELECT    username AS ltUser, accrued AS ltHours
     FROM      tblusers
   WHERE     clr_accrual = '1' AND hiredate  IS NOT NULL
  </cfquery>

I then display the results on my page like this:

<form action="" method="post">    
<table border="1" cellpadding="1" cellspacing="1">
      <tr>
        <td><input type="checkbox" name="checkbox" id="checkbox" title="Check this box and click Submit button to add Accruals to database." /></td>
        <td>ltUser</td>
        <td>ltHours</td>
      </tr>
      <cfoutput query="rsLeaveTimeManagerAccruals">
        <tr>
          <td>#rsLeaveTimeManagerAccruals.ltUser#</td>
          <td>#rsLeaveTimeManagerAccruals.ltHours#</td>
        </tr>
      </cfoutput>
</table>
</form>
<input type="submit" name="Submit" id="Submit" value="Submit" title="Check box & Submit to insert the Hours listed above for each User."/>

Here is what my page looks like: (just an HTML table that dynamically displays the records to view before inserting)

Question: How do I Insert the "x" number of query results for (ltUser & ltHours) into tblLeaveTime WHERE "checkbox" = 1 ?

Answer 1

The simplest option is to make the checkbox value the unique record ID. I would also recommend giving the checkbox a more descriptive name.

 <input type="checkbox" name="userIDList" value="#ID#" ...>

The selected id's will then be passed to the action page as a comma delimited list. You can then use that list to insert the selected records into another table with an INSERT / SELECT. (Obviously add validation to ensure something was selected first.)

   INSERT INTO OtherTable ( UserID, AccruedHours )
   SELECT ID, Accrued 
   FROM   tblUsers 
   WHERE  ID IN 
          (
            <cfqueryparam value="#FORM.userIDList#" cfsqltype="cf_sql_integer" list="true"> 
          )

Answer 2

Thanks to Leigh and your excellent help! The checkbox tip was the fix (name="userIDList" value="#ID#")

Here is the final working Insert.cfm (some html stripped out)

    <!--- Create the Manager Monthly Accruals (Mgt) dataset --->
      <cfquery name="rsLeaveTimeManagerAccruals" datasource="care">
    SELECT    tblusers.ID, username as ltUser, status, active, email, hiredate, tblUsers.picture AS eP
    , TIMESTAMPDIFF(YEAR, hiredate, NOW()) AS year_passed
    , TIMESTAMPDIFF(MONTH, hiredate, NOW()) MOD 12 AS month_passed
    , TIMESTAMPDIFF(DAY, hiredate, NOW()) MOD 365 AS day_passed
    , CONCAT(TIMESTAMPDIFF(YEAR, hiredate, NOW())," years, ",TIMESTAMPDIFF(MONTH, hiredate, NOW()) MOD 12," months, ",TIMESTAMPDIFF(DAY, hiredate, NOW()) MOD 365," days") AS EmployLengthActive
    , TIMESTAMPDIFF(YEAR, hiredate, NOW()) AS monthly_accrual_time

    , CASE WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) < 1
     THEN '0'
     WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) <= 5
     THEN '14'
     WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) >= 6
     THEN '18'
     END AS ltHours


    FROM      v_reports_to_mgt_list LEFT OUTER JOIN tblusers ON tblusers.ID = v_reports_to_mgt_list.id
    WHERE     clr_accrual = '1' AND hiredate  IS NOT NULL
    ORDER BY  hiredate
    </cfquery>

    <!-- Add a checkbox to Select / Deselect All -->
    <script type="text/javascript">
    checked=false;
    function checkedAll (frm1) {
        var aa= document.getElementById('form1');
         if (checked == false)
              {
               checked = true
              }
            else
              {
              checked = false
              }
        for (var i =0; i < aa.elements.length; i++) 
        {
         aa.elements[i].checked = checked;
        }
          }
    </script>
    <!-- Script by hscripts.com -->
.....html....
<form action="leave_time_manager_accruals_insert.cfm" method="post" id="form1">    
<table border="1" cellpadding="1" cellspacing="1">
      <tr>
        <td><input type='checkbox' name='checkall' onclick='checkedAll(form1);'> Check All?</td>
        <td>Username</td>
        <td>Status</td>
        <td>Active</td>
        <td>Hiredate</td>
        <td>Employee Length</td>
        <td>Management_Accrual</td>
      </tr>
      <cfoutput query="rsLeaveTimeManagerAccruals">
        <tr class="#iif(currentrow MOD 2,DE('lightrow'),DE('darkrow'))#">
          <td><img src="../images/employees/#eP#" alt="" width="24" height="28" title=""/>
            <input type="checkbox" name="userIDList" value="#ID#" title="Check this box and click Submit button to add Leave Time Accruals to database." /></td>
          <td>#rsLeaveTimeManagerAccruals.ltUser#</td>
          <td>#rsLeaveTimeManagerAccruals.status#</td>
          <td>#rsLeaveTimeManagerAccruals.active#</td>
          <td>#rsLeaveTimeManagerAccruals.hiredate#</td>
          <td>#rsLeaveTimeManagerAccruals.EmployLengthActive#</td>
          <td><div align="center">#rsLeaveTimeManagerAccruals.ltHours#</div></td>
        </tr>
      </cfoutput>
   <td><input type="submit" name="Submit" id="Submit" value="Submit" title="Clicking this button will add all checked Managers Leave Time Accruals to database."/></td>
</table></form>

Here is the final working Action.cfm (some html stripped out)

<cfparam name="form.ltUser" default="">
<cfparam name="form.ltHours" default="">
<cfparam name="form.userIDList" default="1">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Leave Time Added</title>
</head>

<body>

<cfquery name="rsInsertLeaveTimeManagerAccruals" datasource="care">
INSERT INTO tblLeaveTime ( ltUser, ltHours, ltCreated, ltType, ltCreatedBy)
   SELECT  v_reports_to_mgt_list.reports_to, 
   CASE WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) < 1
 THEN '0'
 WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) <= 5
 THEN '14'
 WHEN FLOOR(DATEDIFF(NOW(), hiredate) / 365) >= 6
 THEN '18'
 END AS ltHours, NOW(), "Accrual", "cf_admin"
   FROM      v_reports_to_mgt_list LEFT OUTER JOIN tblusers ON tblusers.ID = v_reports_to_mgt_list.id
   WHERE  v_reports_to_mgt_list.ID IN 
          (
            <cfqueryparam value="#FORM.userIDList#" cfsqltype="cf_sql_integer" list="true"> 
          )
</cfquery>
<h1>Management Leave Time successfully added to  database! </h1>
Click <a href="leave_time_mgt_l01.cfm">here</a> to return to Admin page
</body>
</html>