What is the best way to dynamically assign field names? 2

programmher · Jan 12, 2001

I need to allow my users to create their own field names. The users are limited to 2 choices (i.e. - City, State). My problem is that as the user creates the new fields, I need the database to convert the field names to "City1", "State1", "City2", "State2", etc. The list and combinations of cities and states could be as short as one or as long as 99999.

Will an array accomplish this for me? If so, do I reference the array in my query that updates the database?

Here is what I have so far: (This does not perform ANY of the dynamic numbering, etc.)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<form action="AddingFields.cfm" method="post">New City:

<input type="text" name="New City">

<br>

<input type="button" value="add it now">

</form>

<cfquery name="addmynewfield" datasource="MyDatabase">

ALTER TABLE tbl_City

ADD NewCity1 VARCHAR(50) NULL

</cfquery>

I would appreciate ANY insight!

GunJack · Jan 12, 2001

I'm not sure I understand what the problem is exactly. Are you asking how to do a dynamic alter statement or how to convert a field name the user enters into a standard format?

A dynamic alter statement would just be something like

ALTER TABLE tbl_City ADD #NewCity1# VARCHAR(50) NULL

but I suspect this isn't what you're asking about. If you can give me a specific scenario, that would help as I don't see anything that appears to be difficult to do.

GJ

programmher · Jan 15, 2001

GJ,

My requirements have now changed. My client wants the user to click the "add" button and have a set of fields ("city" and "state&quot

appear on the form - one form. I have never done this - I (and my users) have always known exactly how many fields are needed.

What is the best way to do this? Below is what I have so far:

<th align="left"><font size="-2" color="Black">First City/State:</font></th>
<td><font size="-1" color="Red">#First_city#</font></td>
<td><font size="-1" color="Red">#First_state#</font></td>

</tr>

<cfform action="Add.cfm" method="POST" enablecab="Yes">

<table align="center"><input type="submit" value="Add" width="1" height="1">

</cfform>

When the "Add" button is selected, another set of city/state fields should appear under the first set.

iza · Jan 15, 2001

"When the "Add" button is selected, another set of city/state fields should appear under the first set. " --> you mean, each time you want to display : first the "input" for both state and city, then the already existing states and cities - if any. And if your user adds a city(state) in the inputs, you want to refresh the page with the newly added city(state). Right ? or did i get it all wrong ? let me know, if i understood what you want to do, it's not tough at all

GunJack · Jan 15, 2001

If you mean how do you let them add an unknown number of fields to a form, try something like this on the main page.

For the starting form..

<cfparam name="fieldCount" default="1">

<cfloop index="x" from="1" to=#fieldCount#>

<form ....
<cfoutput>
<input name="city#x#" ....>
<input name="state#x#" ....>
</cfoutput>
</form>

</cfloop>

Then to add more...
<form ....>
<input type="hidden" value="#evaluate(fieldCount+1)#">
<input type="submit" value="add">
</form>

If you are wanting to know how to process an unknown number of fields, just label them in a consistent manner such as "city1", "city2", "city3"..... Then, in the receiving script, you could check these several ways but here's one that I think is fairly simple.

<cfset x=1>
<cfloop condition="isdefined("city#x#&quot

">

.... code here to use each form variable in succession...
<cfset myNewVariable = evaluate("city#x#&quot

>
..............

<cfset x=x+1>

Optional code to prevent runaway loops
<cfif x gt 100><cfbreak></cfif>
</cfloop>

Let me know if this helps,
GJ

programmher · Jan 15, 2001

Iza,

You are correct - that is what I want to do. Does your method greatly differ from GJ's?

GJ,

I am trying your suggestion now. I will post my progress.

Many thanks to you both!

programmher · Jan 15, 2001

Iza,

What is your suggested method of refreshing the same form after each added field? So far, I have been unable to achieve this...

iza · Jan 16, 2001

i'm sorry i'm running out of time so i'll try to briefly explain - hope this will remain clear !!!!
the idea here is to submit the form to the same page it's on, and to run the "add" query only if we have values from the form. The query to read all existing cities and states have to be done every time, and the form and this query results as well.
the code is like this (not tested, ie only, but should work)

<cfif isdefined("form.city&quot

and isdefined("form.state&quot

> //then we know the form was submitted and we have values to insert
<cfquery name="add_one">
here is the query to ADD form.city and form.state in the db
</cfquery>
// you can add error handling stuff here
</cfif>

// now do the query to get existing cities & states if any - this will read what has just been added as well !
<cfquery name="read_all">
this one is supposed to READ the table with cities / states - as it is always needed (even the first time) we do it every time
</cfquery>

// here is the form - your_page.htm is the page we are coding right now !!!!
<form name="the_form" action="your_page.htm" method=... >
<input type=text name=city ...>
<input type=text name=state ...>
<input type=submit value="add">
</form>

// now display the existing states and cities
<cfif the_query.recordcount gt 0> //this is for the "if any"
<cfoutput query="read_all">
output the way you want
</cfoutput>
</cfif>

and that's all

programmher · Jan 16, 2001

Many, many thanks, Iza!!

iza · Jan 16, 2001

glad if it helps you

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

What is the best way to dynamically assign field names? 2

programmher

Programmer

GunJack

ISP

programmher

Programmer

iza

Programmer

GunJack

ISP

programmher

Programmer

programmher

Programmer

iza

Programmer

programmher

Programmer

iza

Programmer

Similar threads

Part and Inventory Search

Sponsor