Highlighted

Insert results of my cfquery into MySQL database

Contributor ,
Jan 31, 2017

Copy link to clipboard

Copied

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.

Views

159

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Insert results of my cfquery into MySQL database

Contributor ,
Jan 31, 2017

Copy link to clipboard

Copied

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.

Views

160

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 31, 2017 0

Have something to add?

Join the conversation