how to populate a cfselect box depending upon the selection on raido button

[nagkar]

Hi

I am new to coldfusion.I dont know much about

cold fusion.I have a problem like there are two radio

buttons and a select box.Depending upon the slection of

the radio button the select box should be populated.i.e

if first radio button is selected then select box should

populate some set of values and if second radio button is

slected then selct box should be populated with other set

of values

Thanks in Advance

[wblakenc]

You have to do a few different things. First ColdFusion being a Server Side Programming language, there is no way to do what you want using ColdFusion directly. You will have to use Javascript to dynamically link the drop down menus to an array that is populated by ColdFusion.

Another way to do it would be to have CF populate both dropdown menus and use javascript to control which is visible (using CSS) depending on which radio button you select.

I hope this puts you in the right direction.

[nagkar]

Thanks for the reply can you give me some example using code.......

[wblakenc]

Like I said there are a few different ways to do this. The following is what put together for you.

Put the following in the document head:

<cfquery datasource="yourDatasource" name="menu1">
SELECT value, option
from SOME TABLE THAT HAS MENU1 DATA
</cfquery>

<cfquery datasource="yourDatasource" name="menu2">
SELECT value, option
from SOME TABLE THAT HAS MENU2 DATA
</cfquery>


<script type="text/javascript">

function makeChange(form) {
	if (form.radio1[0].checked) {
		document.getElementById("showMenu1").style.visibility="visible";
		document.getElementById("showMenu2").style.visibility="hidden";
		form.menu2.selectedIndex = 0;

	}
	if (form.radio1[1].checked) {
		document.getElementById("showMenu2").style.visibility="visible";
		document.getElementById("showMenu1").style.visibility="hidden";
		form.menu1.selectedIndex = 0;		

	}
}


</script>
<style type="text/css">
#showMenu1 {
visibility:hidden;
}
#showMenu2 {
visibility:hidden;
}
</style>

The above code does a few things. First it gets the data for the drop down menus by polling your database. If the data for these two menus are in the same table (which would make sense) you will need to include a WHERE clause in the above queries. These values are put into two drop down menus that appear or disappear depending on which radio button is checked. The following code will be found in the document body:

<form action="" method="post">
<input type="radio" id="radio1" name="radio1" value="Yes" onClick="makeChange(this.form)" />Yes <input type="radio" id="radio1" name="radio1" value="No" onClick="makeChange(this.form)" />No
<div id="showMenu1">

<select name="menu1">
<option value="" selected="selected">Select One</option>
<cfoutput query="menu1">
<option value="#value#">#option#</option>
</cfoutput>
</select>
</div>

<div id="showMenu2">

<select name="menu2">
<option value="" selected="selected">Select One</option>
<cfoutput query="menu2">
<option value="#value#">#option#</option>
</cfoutput>
</select>
</div>
</form>

I hope this helps.

[nagkar]

Thank u very much.....it solved my problem,.,,,,,,,now i have another
problem ....

I am displaying a dynamic table which consits of four columns named as Ingredient,Lot,d_amount,d_unit......out of which Ingredient,d_amount,d_unit are taken from one

query named as "qrecipes" i.e from one table and Lot Cloumn is taken from another table.Now the problem is the data that is to be displayed in lot column is dependent on the

data present in ingredient.i.e If the first row ingredient column has "XYZ" then i need to pick the id of "XYZ" from second table and display in lot column.

Ingredient Lot d_amount d_unit

1 xyx 345 is id
for XYZ which
should be
displayed from
Second table

Below is my code ....please say what is wrong .....please suggest me if there are any other methods or any kind of solution to solve this problem

Queirs

<cfquery name="qrecipes" datasource="testmsexcell">
SELECT *
FROM Recipes WHERE product='#form.product#' 

</cfquery>


<cfquery name="qlot"  datasource="testmsexcell">
SELECT Lots.id 
FROM Lots WHERE ingredient='#qrecipes.ingredient#'
</cfquery>

Table

table border="2px" id="maketable">
                                    <tr>
                                      
                                      <td>Ingredient</td>
                                      <td>Lot</td>
                                      <td>d_amount</td>
                                      <td>d_unit</td>
                                    </tr>
                                    <cfoutput query="qrecipes">
                                      <tr>
                                       
                                        <td>#qrecipes.ingredient#</td>
                                        <td><cfselect name="lotid" id="lotid" query="qlot" value="id" display="id"></cfselect></td>
                                        <td>#qrecipes.d_amount#</td>
                                        <td>#qrecipes.d_unit#</td>
                                      </tr>
                                    </cfoutput>
                                  </table>

[wblakenc]

Can you not just join the two tables using an inner join?
I suspect you can do what you need with one query, but I have to know more about your table design with the primary and foreign keys.

[nagkar]

Please neglete those dots.i have kept those for displaying tables properly
First Table is Recipes

Product...........ingredient.........d_amount.......... d_unit id
abc................xyz...............78................... lb.....
....................uvw...........................................
efg..................hig
......................lkm

In the first table the id is the primarykey which is autoincremented.

Second Table is Lot:The id is primarykey which is not related to first table which is recipes.

id.............ingredient

5t6y.............xyz
7yh8.............xyz
9jnu.............uvw

Now i am trying to diaplay to user something like this by taking product as input from the user.if the user selects product as "abc"

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
...................7yh8

uvw.................9jnu

I am able to display the ingredient,d_amount,d_unit correctly but i am unable to display the correct lotids for the repective ingredient.I am using CFSELECT tag to display the

lotids

[wblakenc]

Let me re-phrase what you are saying to see if I get it.
You have a table called recipes and a table called lots. These two tables are linked by ingredients. What you want is the user to be able to select a product and get from the database what the lot id is for the ingredient(s) that goes into the recipe.

This should be a pretty simple inner join.

Try the following query (making changes to the column names as needed):

SELECT *
from recipes r inner join lots l on l.lot_ingredient = r.ingredient

If that works, let me know and I can get you the rest of the way.

[nagkar]

Thanks the given sql is working ...it gave me correct data ..but now the problem is that it is displaying same data multipile times.i.e

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
xyz................7yh8
xyz................98uj

uvw.................9jnu

instead of that i need to display it as

xyz...............5t6y
..................7yh8
...................98uj

uvw.................9jnu

[wblakenc]

Humm... Maybe I do not understand what you are trying to do, because if you have two products with the same ingredient you will get duplication of that ingredient. I am not sure that is avoidable, however you should not be getting different lot_ids.

I am having trouble understanding your example tables. So I attached a screen shot of what I put together and maybe you can tell me what I am doing differently.

[cartman714]

thx

[macslayer]

Thanks the given sql is working ...it gave me correct data ..but now the problem is that it is displaying same data multipile times.i.e

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
xyz................7yh8
xyz................98uj

uvw.................9jnu

instead of that i need to display it as

xyz...............5t6y
..................7yh8
...................98uj

uvw.................9jnu

You are going to have to control the grouping of the xyz data by either testing for changes in some control variable assigned that value, or using built-in's of whatever flavor of sql your are using.
For instance, if your using mySql you could use group_concat
something like:
select table.*, GROUP_CONCAT(concat('Lot:',lotids,'Amt:',d_amount,'unit:',d_unit),'||') as big_lots
from table
left outer join ....
where ....
group by ingredient
having (table.whateverfield like 'whatever')

that give you one row per ingredient with a field called big_lots aggregating all the lots into it, delimited by || which you have to parse it out of to display nicely etc.. Anyway, it's an idea for which there are lots of solutions..

Bob

[gklandes]

in the sql, try replacing "inner join" (which will return all matching rows from both tables) to "LEFT OUTER JOIN" which will essentially focus on the 1st table.

The problem, really might be that you have a "Many to Many" relationship in the DB. If the Recipe table links to the Lot table on ingredient and there are multiply rows for the same ingredient in both tables, your tables will never join properly. The solution is for the Recipe table to refer to a unique id in the lot table or add a table or two for better "normalization".

Assuming you are storing Products, Recipes, Ingredients and Lots, this is a possible schema:

tblProduct

• productID (PK)
• productName

tblRecipe

• recipeID (PK)
• productID (refers to tblProduct.productID)
• ingredientID (refers to tblIngredient.ingredientID)
• quantity

tblIngredient

• ingredientID (PK)
• ingredientName
• lotID (refers to tblLot.lotID)
• unit

tblLot

• lotID (PK)
• lotNumber

Then you could query one recipe with this SQL:

SELECT r.quantity, i.ingredientName, i.unit, l.lotNumber
FROM tblProduct p LEFT OUTER JOIN 
tblRecipe r ON r.productID = p.productID LEFT OUTER JOIN
tblIngredient i ON i.ingredientID = r.ingredientID LEFT OUTER JOIN
tblLot l on l.lotID = i.lotID
WHERE p.productID = **some_product_id**

[macslayer]

I think his sql is working perfectly, as I read is that he is having a presentation issue, not relationship issue. His relationship looks like a one-to-many on ingredients to lots. He just does not know how to handle the repeating left hand column data joined to the right hand. So, he was looking for methods to not repeat the ingredient on the 1+nth lot. Anyway, that is how I read it.