How do I not insert empty value into mySQL loop

New Here ,
Jun 10, 2015 Jun 10, 2015

Copy link to clipboard

Copied

How do I prevent my loop from trying to insert an empty value into mySQL database? The form I am using has four rows automatically created. If they only fill in three rows than I get problems with the fourth row being empty.  Below is my code that works fine as long as they don't leave a field empty.

<cfloop from="1" to="#form.numba#" index="idx">

    <cfset getqty = form["qty" & idx]>

    <cfset getitem = form["item" & idx]>

    <cfset getunit = form["unit" & idx]>

    <cfset gettotal = form["total" & idx]>

<cfquery name="insertItems" datasource="#application.dsn#">

INSERT into items

                (orderID,

                qty,

                item,

                unit,

                total

            )

VALUES(

<cfqueryparam value="#getoid#" CFSQLType="CF_SQL_INTEGER" >,

<cfqueryparam value="#getqty#" CFSQLType="CF_SQL_INTEGER" >,

<cfqueryparam value="#getitem#" CFSQLType="CF_SQL_VARCHAR" >,

<cfqueryparam value="#getunit#" CFSQLType="CF_SQL_INTEGER" >,

<cfqueryparam value="#gettotal#" CFSQLType="CF_SQL_INTEGER" >

)

</cfquery>

</cfloop>   

Thanks in advance.

Views

270

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
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Jun 10, 2015 Jun 10, 2015
The user may omit filling any of the rows, not necessarily the last. In fact, he may omit filling the entire form.The best solution is to validate the data before doing the database insert. For example, you could check for empty rows as follows<cfif isDefined("form.numba")><cfloop from="1" to="#form.numba#" index="row">    <cfset getqty = form["qty" & row]>    <cfset getitem = form["item" & row]>    <cfset getunit = form["unit" & row]>    <cfset gettotal = form["total" & row]>    <cfif (getqty i...

Likes

Translate

Translate
Adobe Community Professional ,
Jun 10, 2015 Jun 10, 2015

Copy link to clipboard

Copied

The user may omit filling any of the rows, not necessarily the last. In fact, he may omit filling the entire form.

The best solution is to validate the data before doing the database insert. For example, you could check for empty rows as follows

<cfif isDefined("form.numba")>

<cfloop from="1" to="#form.numba#" index="row">

    <cfset getqty = form["qty" & row]>

    <cfset getitem = form["item" & row]>

    <cfset getunit = form["unit" & row]>

    <cfset gettotal = form["total" & row]>

    <cfif (getqty is "") and (getitem is "") and (getunit is "") and (gettotal is "")>

        <!--- Row is empty --->

        <!--- Do something. For example, break out of loop, inform user and get him to resubmit --->

        <!--- Alternatively, skip insertion of this particular row --->

    <cfelse>

        <!--- Row is non-empty --->

    </cfif>

</cfloop>   

</cfif>

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
community guidelines
New Here ,
Jun 11, 2015 Jun 11, 2015

Copy link to clipboard

Copied

LATEST

Thanks BKBK, that did it.

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
community guidelines