Highlighted

How do I not insert empty value into mySQL loop

New Here ,
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.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

266

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

How do I not insert empty value into mySQL loop

New Here ,
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.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

267

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
Jun 10, 2015 0
Adobe Community Professional ,
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
Reply
Loading...
Jun 10, 2015 0
New Here ,
Jun 11, 2015

Copy link to clipboard

Copied

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
Reply
Loading...
Jun 11, 2015 0