SELECT * FROM reviewers INNER JOIN reviews ON reviewers.ID = reviews.ID WHERE reviewers.ID = #url.ID# #Reviewer#
#Review#
Ken [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => The ScareCrow [ip] => info@krcaldwell [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Dropdown Menu (Noob) - Coldfusion Database Access

Dropdown Menu (Noob) - Coldfusion Database Access

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 ...

  1. #1

    Default Dropdown Menu (Noob)

    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;

    USC_ZEUS Guest

  2. #2

    Default Re: Querying with a Dropdown

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

  3. #3

    Default Re: Querying with a Dropdown

    <!--- 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

    The ScareCrow Guest

  4. #4

    Default Re: Querying with a Dropdown

    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>

    USC_ZEUS Guest

  5. #5

    Default Re: Querying with a Dropdown

    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 Guest

  6. #6

    Default Re: Querying with a Dropdown

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

  7. #7

    Default Re: Querying with a Dropdown

    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" <infokrcaldwell.com> wrote in message
    news:dsdt66$h4i$1forums.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
    >
    >

    Lossed Guest

  8. #8

    Default Re: Querying with a Dropdown

    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

    The ScareCrow Guest

Similar Threads

  1. List/Menu Form hides dropdown Menu
    By Lerbekmo in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: January 5th, 10:56 PM
  2. another dropdown menu post
    By scatteringflame in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: July 1st, 08:42 PM
  3. dropdown menu
    By shyrocker in forum Macromedia Dynamic HTML
    Replies: 9
    Last Post: June 19th, 01:27 AM
  4. add the dropdown menu on overlay?
    By UC01 in forum Macromedia Director 3D
    Replies: 0
    Last Post: May 10th, 01:28 PM
  5. Need Help Using Flash Menu Tempates (noob)
    By Existentialism webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 2
    Last Post: August 30th, 10:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •