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">
            <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 the page looks like: (just an HTML table that dynamically displays the records to view before inserting)

      submit.JPG

       

       

      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.