kokopelliama3
asked on
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.
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.
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?
Also, what database - MS SQL, MySQL, ..?
ASKER
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.
The database is MS SQL 5.
(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?
Do you have any flexibility with this app or do you just need to work with what you have?
MS SQL 5.
Do you mean MySQL 5 or SQL Server 2005?
Do you mean MySQL 5 or SQL Server 2005?
ASKER
No flexibility, unfortunately. I have to work with what's there.
Database is SQL Server 2005. Sorry. :)
Database is SQL Server 2005. Sorry. :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
YourComponent.cfc:
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>
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>
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 ;-)
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 ;-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
aqx,
YOU ROCK!! THANKS A MILLION!
KOKO
YOU ROCK!! THANKS A MILLION!
KOKO
Welcome :)