[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Compare form entry and database values; Also need loop through 14 tables

I consider myself a beginner/intermediate developer. I am using Coldfusion MX.

I am developing a form that will allow the user to enter between 1 and 6 catalog numbers. I need to do the following:

1. Make sure that at least one catalog number has been entered on the form.
2. Ensure that the catalog numbers entered by the user exist in the database.
3. Loop thorugh 14 tables to find the matching catalog number.

What is the best way to go about this?

Thanks in advance.
0
kokopelliama3
Asked:
kokopelliama3
  • 9
  • 4
2 Solutions
 
_agx_Commented:
Normally I'd suggest something like a text box with auto-suggest for looking up catalog numbers. But 14 tables is a lot.  I'd expect just one.  Why so many tables?
0
 
_agx_Commented:
Also, what database - MS SQL, MySQL, ..?
0
 
kokopelliama3Author Commented:
Why 14 Tables: The 14 tables represent the 14 main categories that our catalog numbers are in. There are two levels of subcategories in each main category. I didn't design the website, so I'm not sure if that's the best way to do it. I think it was designed that way to optimize the search engine, but I'm not sure.

The database is MS SQL 5.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
_agx_Commented:
(Maybe there were other reasons .. ) Usually separate tables isn't the best approach.  Entities of the same type should generally be stored in a single table. So you'd have one "Category" table.  If the only difference between a "Category" and "Subcategory" is the level, then subcategories could be stored in the "Category" table too.  With an additional "ParentID" column indicating which is which. There are other structures too, but that's a common one for storing your typical hierarchy like a menu or ORG Chart.

Do you have any flexibility with this app or do you just need to work with what you have?
0
 
_agx_Commented:
MS SQL 5.

Do you mean MySQL 5 or SQL Server 2005?
0
 
kokopelliama3Author Commented:
No flexibility, unfortunately. I have to work with what's there.

Database is SQL Server 2005. Sorry. :)
0
 
_agx_Commented:
Ok. The simplest way is probably to use UNION to merge the results of the 14 tables together.  If you put it in a VIEW, you can reuse it.  It'll be like everything really actually in a single table

CREATE VIEW  AllCatalogNumbers
AS
SELECT CatalogName FROM Table1 UNION ALL
SELECT CatalogName FROM Table2 UNION ALL
..... repeat for other tables
SELECT CatalogName FROM Table14 UNION ALL

Assuming you're running CF8+ you could then create a form with your 6 text boxes. Using autosuggest to make it easier for users to select catalog numbers.  Using the VIEW it should be pretty simple:

     http://tutorial7.learncf.com/
0
 
_agx_Commented:
Not tested but the form code might be something like below. Keep in mind you don't have to use autosuggest. It's just a nice-to-have feature. Once you have that working, adding the validation is simple.

Your Form
<cfset numOfFields = 6>
<cfform>
	<cfloop from="1" to="#numOfFields#" index="row">
		<cfinput name="catalogNum#row#" autoSuggest="cfc:YourComponent.getCatalogNum({cfautosuggestvalue})"><hr>
	</cfloop>
	<input type="submit">
	<cfinput type="hidden" id="numOfFields" name="numOfFields" value="#numOfFields#">
</cfform>

Open in new window


YourComponent.cfc:
<cfcomponent output="false">
	<cffunction name="getCatalogNum" access="remote" returntype="Array">
		<cfargument name="num" type="string" required="yes">
		<cfset var q = "">
		<cfquery name="q" datasource="YourDSNNameHere">
                       <!--- find all catalog numbers that _start with_ whatever the user entered --->
			SELECT CatalogNumber 
                        FROM   AllCatalogNumbers
                        WHERE CatalogNumber LIKE <cfqueryparam value="#arguments.num#%" cfsqltype="cf_sql_varchar">
		</cfquery>
		 <cfreturn listToArray(valueList(q.CatalogNumber))>    
	</cffunction>
</cfcomponent>

Open in new window

0
 
_agx_Commented:
I am using Coldfusion MX.

Oh crap ... stupid me.  I'd forgotten it was MX. Scratch the autosuggest stuff, it's CF8+ only  (or use jquery's autosuggest stuff instead).  Give me a minute to regroup ;-)
0
 
_agx_Commented:
Ok. Take #2 ;-) Keep in mind you can improve a lot of this with ajax. But let me outline the basic idea first.  

Assuming catalogNum's can't contain commas, your basic form could have the 6 text boxes, all w/the same name. Then use cfform's javascript validation to ensure at least 1 number was entered.  

<script type="text/javascript">
	function atLeastOneNum(frm, fld, value) {
		// get all catalogNum text boxes 
		var arr = document.getElementsByName('catalogNum');
		var totalNonEmpty = 0;
		for (var x = 0; x < arr.length; x++) {
			// get value of current text box
			var text = arr[x].value.replace(/\s+/g, '');
			if (text.length > 0) {
				// record NON empty box
				totalNonEmpty++;
			}
		}
		// return true if one or boxes was NOT empty
		return totalNonEmpty > 0;
	}
</script>

<cfset numOfFields = 6>
<cfform>
	<cfloop from="1" to="#numOfFields#" index="row">
		<cfinput type="text" name="catalogNum"><hr>
	</cfloop>
	<input type="submit">
	
	<cfinput type="hidden" name="numOfFields" 
			value="#numOfFields#"
			validateAt="onSubmit" 
			onValidate="atLeastOneNum"
			message="Enter at least 1 catalog number!">
</cfform>

Open in new window


On the server side you'd want to re-validate the entries. Once to ensure 1 or more numbers was entered

<cfparam name="form.catalogNum" default="">

<cfset uniqueCatalogNumbers = "">
<cfloop list="#form.catalogNum#" index="num">
	<!--- track unique numbers entered --->
	<cfif not listFindNoCase(uniqueCatalogNumbers, num)>
		<cfset uniqueCatalogNumbers = listAppend(uniqueCatalogNumbers, trim(num))>
	</cfif>
</cfloop>

<!--- verify at least 1 number was entered --->
<cfif not listLen(uniqueCatalogNumbers)>
	no numbers were entered. show error ....
</cfif>

Open in new window



... and once to ensure those values are in the db.  I'd use IN (..) to find the matching catalog records.  Then compare the list count to the query.recordCount. If they're not the same, then one of the user's values isn't in the db.  

<cfquery name="verifyNumbers" datasource="yourDSN">
	SELECT CatalogNumber 
    FROM   AllCatalogNumbers
    <!--- adjust data type if needed --->
    WHERE  CatalogNumber IN 
	( <cfqueryparam value="#uniqueCatalogNumbers#" cfsqltype="cf_sql_varchar" list="true"> )
</cfquery>

<!--- the query count should be the same as the number of items in our list.
if it isn't then one of the entries *isn't* in the database  --->
<cfif verifyNumbers.recordCount lt listLen(uniqueCatalogNumbers)>
	one or more of the numbers weren't found. show error
</cfif>

Open in new window


You can use valueList and ListCompare to display which values *aren't* in the db.



0
 
kokopelliama3Author Commented:
aqx,

Thank you so much!!!!!! I will give this a try today and see what I come up with. I'll post any questions as I go along.
0
 
kokopelliama3Author Commented:
aqx,

YOU ROCK!!  THANKS A MILLION!
KOKO
0
 
_agx_Commented:
Welcome :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now