6 Replies Latest reply on May 31, 2006 6:21 PM by karla

    display a record IF

      Hello all!
      I am trying to display one record at a time from my query of multiple tables if the username/password entries match the entries in the DB but am unsuccessful. The sequence starts from a list of names, where names is hyperlink to the page where the result of the query is displayed, but before getting to the results page, the user has to login.

      My question is how can I tell the hyperlink to go to this specific record (ie. contactID = 34 or whatever the user's ID is) only after entering a valid username/password. I tried

      1. from the login.cfm page: if successful, go to mypage.cfm?#contactID# (OR mypage.cfm?contactID)
      2. mypage.cfm?#contactID# and mypage.cfm?contactID as the link and both don't display the specific record. Any suggestions on what I am missing?

        • 1. Re: display a record IF
          SafariTECH Level 1
          First, if you need to verify login has completed first, you should run the check in the application.cfm file so there is no way for anyone to access anyone else's data. ... that's slightly another topic however.

          To grab a user's info based on a passed URL variable, you should use:


          Your examples above have no relational pairs, just the variable or the result, but not both.

          In "mypage" you would then run a query like

          SELECT *
          FROM ThisTable
          WHERE TableColumn = #url.contactID#

          • 2. Re: display a record IF
            karla Level 1
            Hi Glen,
            I'm sorry if I wasn't clear enough. I have tried what you suggested but what I forgot to mention is that I am querying from multiple tables bu using INNER JOIN. These tables have their own unique IDs. I have a main table, TableContactList, with the primary key contactID. The other tables are related to the main table and they contain names from the TableContactList, where contactID = Table2ID, Table3ID, Table4ID. So if I use contactID=#contactID#, how will the query know to get/display the record where contactID = Table2ID = Table3ID = Table4ID.

            If I use the URL syntax, the same error comes up where contactID is undefined. I also tried this:

            In addition, my main table, ContactList, should I put the hyperlink from there and just put a login restriction on that query page before displaying this: mypage.cfm?contactID=#contactID# I did this as well, but still didn't get to display the specific record I want.

            Any other suggestions, please let me know.

            • 3. display a record IF
              SafariTECH Level 1
              Well, query aside, you need to use a proper URL setup to pass the ID to the query, which would be " mypage.cfm?variable=ID"

              As long as you do that, then your issue will lie in the construction of your query

              If you post your actual query code (that is found in mypage.cfm) then there are a lot of DB junkies here that can likely see what the issue may be in getting the actual output you want.

              I would start the testing process by using a hard-coded URL with an ID that you know what to expect for output from. After you get the result pages to work properly you can add back in the login process etc., since that will not really change anything. Multiple joins can sometimes be a headache to figure out the issue.

              • 4. Re: display a record IF
                karla Level 1
                Hello All,
                Here is a more detailed explanation. Sorry it took too long. Anyway,
                not sure if I need cfparam because I only want to display one record at a time and not the entire query:

                <cfparam name="MaxRows" default="1"> <cfparam name ="StartRow" default="1">

                I actually have about 7 tables that I need to get information from, displaying several columns from each of them. Is INNER JOIN a good choice to use to display all of the info I need (all tables except the contact list contain dates)? Anyway, here's the code I've started:

                <cfquery name="qryIndividual" datasource="dshmat">
                SELECT ContactList.ContactID, ContactList.FirstName, ContactList.LastName, BIO.InitialRef2006, BIO.Bsfty2005, HWG.hwg2006, HWG.hwg2005
                FROM (ContactList INNER JOIN BIO on ContactList.ContactID=BIO.BioID) INNER JOIN HWG on ContactList.ContactID=HWG.hwgID
                ORDER BY ContactList.LastName

                This is the output that I am trying to display. But I'm stuck as to how to tell the DB to display a specific record if username and password are valid entries. If username is jdoe, then display jane doe's record only.

                FirstName LastName | InitialRef2006 | Bsfty2005 | hwg2006 | hwg2005
                jane doe 4/1/2006 6/5/2005 2/5/2006 8/6/2005

                I have login page, and this is where I want to initiate the query to display the right record of that individual.

                Any suggestions on where to start?

                • 5. Re: display a record IF
                  Dan Bracuk Level 5
                  Well karla, I got two words for ya.

                  where clause
                  • 6. Re: display a record IF
                    karla Level 1
                    Hi all,
                    I still need your help in figuring out how I would present his/her data if his/her login matches his/her record...do i initiate this on the login page or the results page? help! Dan B mentioned 'where' clause . I already have where clause on the results page about displaying the information if all their IDs match each other...

                    The #url.columname# give me an undefined error on the URL. not sure why this is...help!

                    my setup:
                    "Firstname_Lastname" is hyperlinked to the myrecord.cfm hoping to display Firstname_Lastname's record only. But before getting to his/her record, I need to authenticate that person...

                    any other suggestion on what I can do to display that person's personal record...*yes i am new at coldfusion*

                    karla Text