0 Replies Latest reply on Jan 31, 2017 2:07 PM by jlig

    Insert results of my cfquery into MySQL database

    jlig Level 1

      I have the following query that calculates accrued leave time for the month:

      SELECT    username AS ltUser,
       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      tblusers
      WHERE     cl_ccrual = '1' AND hiredate  IS NOT NULL
      ORDER BY  hiredate


      I then display the results on my page like this:

      <form action="" method="post">    
      <table border="1" cellpadding="1" cellspacing="1">
              <td><input type="checkbox" name="checkbox" id="checkbox" title="Check this box and click Submit button to add Accruals to database." /></td>
            <cfoutput query="rsLeaveTimeManagerAccruals">
      <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 the 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


      Note: There can be a varying number of records output by the query.