Dropdown Menu (Noob)

[USC_ZEUS]

I have two tables linked together by an ID field. The first table contains
reviewers. The second table contains reviews. I also have a drop down that I
want to use. The idea is to select a reviewer, and have their reviews populate
below. I'm having trouble getting this to happen.

I have this as my queries:

<cfquery datasource="mydata" name="getData">
SELECT *
FROM reviewers, reviews
WHERE reviewers.ID = reviews.ID
</cfquery>

<cfquery datasource="mydata" name="getReviewers">
SELECT ID, Reviewer
FROM reviewer
ORDER BY reviewer
</cfquery>

I have a drop down and it shows all Reviewers like I want it to:

<form name="form1">

<script language="JavaScript">
function MM_jumpMenu(targ,selObj,restore){
eval(targ+".location='"+"index.cfm?ID="+selObj.opt ions.value+"'");
if (restore) selObj.selectedIndex=0;
}</script>

<select name="menu1" onChange="MM_jumpMenu('parent',this,1)">
<cfset thisID = ID>
<cfoutput query="getReviewers">
<cfif ID eq thisID>
<option selected value="#ID#">#Reviewer#</option>
<cfelse>
<option value="#ID#">#Reviewer#</option>
</cfif>
</cfoutput>
</select>
</form>

And then I have this showing the results:

<cfoutput query="getData" Group="ID">
#Reviewer#<br>
<cfoutput>#Review#
</cfoutput>
</cfoutput>

Here's the problem I have. My results show all reviewers on ONE page. The drop
down selector does nothing. I want to be able to show one reviewer with all
their reviews below them at a time and the selector to change reviewers.
:confused;

[Dan Bracuk]

You have to submit the form to run a query for the selected reviewer.

[The ScareCrow]

<!--- enclose query in an if to make sure that the url.id has been passed
before the query is executed --->
<cfif IsDefined("url.ID")>
<cfquery datasource="mydata" name="getData">
SELECT *
FROM reviewers INNER JOIN reviews ON reviewers.ID = reviews.ID
WHERE reviewers.ID = #url.ID#
</cfquery>
</cfif>

<!--- also enclose the cfoutput until the url.id has been passed --->
<cfif IsDefined("url.ID")>
<cfoutput query="getData" Group="ID">
#Reviewer#<br>
<cfoutput>#Review#
</cfoutput>
</cfoutput>
</cfif>

Ken

[USC_ZEUS]

Thanks ScareCrow. I changed your suggestion what I have below and it works
like I want it to. I don't really understand how and would like to if anyone
wants to explain to me.

<cfif IsDefined("ID")>
<cfquery datasource="mydata" name="getData">
SELECT *
FROM reviewers INNER JOIN reviews
ON reviewers.ID = reviews.ID
reviewers.ID = #ID#
</cfquery>
</cfif>

<cfquery datasource="mydata" name="getReviewers">
SELECT ID, Reviewer
FROM reviewer
ORDER BY reviewer
</cfquery>

<form name="form1">

<script language="JavaScript">
function MM_jumpMenu(targ,selObj,restore){
eval(targ+".location='"+"index.cfm?ID="+selObj.opt ions.value+"'");
if (restore) selObj.selectedIndex=0;
}</script>

<select name="menu1" onChange="MM_jumpMenu('parent',this,1)">
<cfset thisID = ID>
<cfoutput query="getReviewers">
<cfif ID eq thisID>
<option selected value="#ID#">#Reviewer#</option>
<cfelse>
<option value="#ID#">#Reviewer#</option>
</cfif>
</cfoutput>
</select>
</form>

<cfif IsDefined("ID")>
<cfoutput query="getData" Group="ID">
#Reviewer#<br>
<cfoutput>#Review#
</cfoutput>
</cfoutput>
</cfif>

[The ScareCrow]

Okay, an explination.

But first, I think it's just a typo as you said it works but
<cfif IsDefined("ID")>
<cfquery datasource="mydata" name="getData">
SELECT *
FROM reviewers INNER JOIN reviews
ON reviewers.ID = reviews.ID
reviewers.ID = #ID#
</cfquery>
</cfif>

You don't have the "WHERE" before reviewers.ID = #ID#

I assume you want the explination for the bold parts ?

Initially the page loads, as the variable "ID" is not defined any code
contained in the cfif will not be executed.
Note: You should scope you variables so "ID" should be "URL.ID"

Now when the user select an option from the select list, the page is reloaded
But this time the variable "ID" is defined, so the code in the cfif is
executed.

"INNER JOIN" "ON" is a more standards compliant why to join table than using
the comma and where clause.
But this is usually a personal preference.

Ken

[USC_ZEUS]

I do have a WHERE in there, just a typo indeed. Thanks again.

[Lossed]

Would structkeyexists(url,'ID') be faster than isdefined()?
Also, safer to scope the search in case there is another var named ID in
another scope somewhere on the page?
Also, safer and faster to use WHERE=<cfqueryparam value="#url.ID#"
cfsqltype="cf_sql_integer">?




"The ScareCrow" <info@krcaldwell.com> wrote in message
news:dsdt66$h4i$1@forums.macromedia.com...

> Okay, an explination.
>
> But first, I think it's just a typo as you said it works but
> <cfif IsDefined("ID")>
> <cfquery datasource="mydata" name="getData">
> SELECT *
> FROM reviewers INNER JOIN reviews
> ON reviewers.ID = reviews.ID
> reviewers.ID = #ID#
> </cfquery>
> </cfif>
>
> You don't have the "WHERE" before reviewers.ID = #ID#
>
> I assume you want the explination for the bold parts ?
>
> Initially the page loads, as the variable "ID" is not defined any code
> contained in the cfif will not be executed.
> Note: You should scope you variables so "ID" should be "URL.ID"
>
> Now when the user select an option from the select list, the page is
> reloaded
> But this time the variable "ID" is defined, so the code in the cfif is
> executed.
>
> "INNER JOIN" "ON" is a more standards compliant why to join table than
> using
> the comma and where clause.
> But this is usually a personal preference.
>
> Ken
>
>

[The ScareCrow]

I could not tell you if it is faster, but the move is towards using
structkeyexists instead of isdefined
and yes, you should be using cfqueryparam
you will just find people will not include this in their posts as it's quicker
to just put the var.

Ken