Dynamic select box

[droberts2001]

All,

Please help, I have very limited experience with CF. I have a feedback form that I have built and wish to report back form the data that users submitted to it. I would like a dropdown box that shows feedback of a specific field that has been entered, which once selected then recalls all associated data.

An example of what I mean - Users enter in details about their pet’s name, weight, date of birth etc.

How do I create a select box, so that when it drops down it shows me cats, dogs, mice etc, and once selected all the records for mice from the 'type of animal' field in my DB are shown.

The problem that I've thought of is that when I create the select box and list the type of animals in my CF code it will produce - (duplicate entries)

Cats
Dogs
Mice
Dogs

Thanks for any help - you all seem helpful in the other threads that I have read.

Regards

Dave

 

[Bramvg]

Hi droberts2001,

This can do the trick:


cfquery name=view_pets datascource=pets
select distinct pets_ID, pets_type from pets
order by pets_type asc

<select name=&quot;pets_to_show&quot;>
<cfoutput query=view_pets>
<option value=pets_id> #pets_type#
</cfoutput>
</select>


On the 'results' page you do:

cfquery name=show_pets datascource=pets
select * from pets
where petsID= form.pets_id

Hope this helps

With kind regards
Bram

 

[droberts2001]

Thanks for the speedy response Bramvg. I hav another question for you.

From the select box, once a person has chosen their pet e.g Dog, how can I call up details from two different tables in the CFQUERY statement.

Quick example I select dog from the dropdown box and I wish to see all the dog details as well as the owners details from the Owners Table.

Thanks in advance

Dave

 

[Bramvg]

Hi Dave,

You must have a link between these two tables, let's say you call this link DogID.

The best thing to do is to work with 3 tables, I explain:

Table 1: Your table with dogs 'types':
-----------------------------------------------

e.g.
AutoID= 1
DogID = 2
Name = Danish Dog
Description = ....

Table 2: Table with owners
---------------------------------
AutoID_owner = 10
DogID = 2
Name_owner = Sam
.....


Table 3: 'in between table'
------------------------------
Because different owners can own the 'same type' of dog you only want to add 1 type of dog and not repeat it for every person. (no duplicates make it easier to maintain your info).

Your table would look like:

AutoID = 3
DogID = 2
AutoID_owner = 10

Now link the different ID's in your 'relationships'.
Now it's fairly easy to make a query in e.g. ms access once the tables are linked.

In your query select all the tablenames you want to display.
Now you get to see all the owners which own a dog + you see the dogs specifications.
+ it's also possible that one owner has 2 different types of dogs, so it's very easy to add another dog. Just add a record in your third table like:

AutoID = 4
DogID = 3
AutoID_owner = 10

If you would not understand this 100% just mail me at bram@vangrimbergen.com and I could send you an MS Access database.

To retrieve more than one table in ColdFusion use this easy code:


<cfquery name=&quot;x&quot; datasource=&quot;dog&quot;>
select description, owner FRom Table1, table2
where DogID = #dogID#

DogID would be part of the table Table 1
and owner would be part of table 2



Regards
Bram