14 Replies Latest reply on Jul 9, 2007 10:55 AM by JohnGree

    find next alpha record

    JohnGree Level 1
      Hi i have a query, which is below, just pulls info and orders by Surname.

      my page displays one record at a time, so what i need is a next and previous button to go to the next surname in the table

      but i am not sure how i would do this maybe an array?, can someone help.

      <cfquery name="Getenq" datasource="#application.ds#">
      SELECT *
      FROM customerenquiryaddresses
      Order By EqSurname
      </cfquery>
        • 1. Re: find next alpha record
          Dan Bracuk Level 5
          You don't need an array. There are tutorials on pagination at easycfm.com that give you the general idea of what you want to do.

          If it were me, I'd be making the query a session variable and using one of the variables that come with cfquery to keep track of what record I was on.
          • 2. Re: find next alpha record
            JohnGree Level 1
            ok thanks, that works well,

            only one issue i have, next to the prev page and next page links, i have a surname search

            this is a text box with a submit btn, lets say the surname was "SMITH", when submitted it goes to a new results page listing all matches

            from this list the user can click a surname "SMITH" to go back to the main page with "SMITHS" details.

            BUT

            then if i click on next link, the page goes back to Adams (first in the list) instead of going to the next SMITH in the list.

            so how do i click on SMITH and go back to the main page so i can use the next and previous links?

            as the link i have
            <a href="Page.cfm?ID=#Gets.ID#">#Gets.Surname#</a>

            which is the id of the record but this obvoiulsy dosn't match the new "next and previous" code i have just put in.

            Any ideas what i need to do.
            • 3. Re: find next alpha record
              Dan Bracuk Level 5
              Keep the query and the currentrow in the session state. Use the session variable with your currentrow in your anchor tag.

              • 4. Re: find next alpha record
                JohnGree Level 1
                ok many thanks Dan,

                i have attached my query as i am not sure what to set as a session

                could you please let me know what sessions i need to set?
                • 5. Re: find next alpha record
                  Dan Bracuk Level 5
                  you don't need the first query. One of the variables that comes with cfquery makes it unnecessary.

                  take the limit stuff away from your 2nd query and make it a session variable. That way you never have to run it again.

                  • 6. Re: find next alpha record
                    JohnGree Level 1
                    ok thanks,

                    but still not sure what you mean?, what sessions do i add to the query?

                    and what would i add so i can remove the first query?

                    thanks once again
                    • 7. Re: find next alpha record
                      Dan Bracuk Level 5
                      Does this look like it might be usefule?

                      <cfquery name="session.yourquery"

                      What do you add so you can remove the first query? A trip to the cfquery pages of the cfml reference manual. If you don't have one, the internet does. Pay particular attention to the variables that are available after you run the query.
                      • 8. Re: find next alpha record
                        efecto747 Level 1
                        Here's a suggestion:
                        • 9. find next alpha record
                          JohnGree Level 1
                          ok thanks, i have added the session to the query, what do i now need to link my text link, so the query knows what record to show.

                          so a user types in a surname and clicks submit which send the user to a new page which displays a list of all matches.

                          the user then click on view record (which is next to each surname),

                          this then takes the user back to the main page and displays the record.

                          the problem is that how does the session query know where to display the surname match?

                          I have tried this query, on the results list page, so i could use currentrow to go back to the main page WHICH WORKS..

                          <cfquery name="Getsur23" datasource="#application.ds#">
                          SELECT *
                          FROM customerenquiryaddresses
                          ORDER BY EqSurname
                          LIMIT #start_record#, #records_per_page#
                          </cfquery>

                          <a href="EnquiryResultsInner.cfm?ID=#Getsur23.currentrow#">View Record</a>

                          But inside the query i need to have
                          WHERE EqSurname LIKE '#form.Surname#%'

                          so this changes the currentrow number which doesnt match the same query in the main page

                          any ideas?

                          • 10. Re: find next alpha record
                            Dan Bracuk Level 5
                            If these are your surnames:

                            ADAMS
                            ANDERSON
                            ANDERSON
                            SMITH

                            and the user does a search on ANDERSON.

                            Is there a requirement for your previous/next links to show him ADAMS or SMITH?
                            • 11. find next alpha record
                              JohnGree Level 1
                              ok if the user searches ANDERSON, on the DISPLAY PAGE, this then submits to the LIST PAGE which will show any matches that start with ANDERSON.

                              then the user will click the record they want, and it will go back to the DISPLAY PAGE showing the details.

                              and yes the NEXT and PREV links then need to go through alphabetically by each record right though to A or X

                              i cant work out if this is possible?
                              • 12. Re: find next alpha record
                                Dan Bracuk Level 5
                                quote:

                                Originally posted by: JohnGree
                                ok if the user searches ANDERSON, on the DISPLAY PAGE, this then submits to the LIST PAGE which will show any matches that start with ANDERSON.

                                then the user will click the record they want, and it will go back to the DISPLAY PAGE showing the details.

                                and yes the NEXT and PREV links then need to go through alphabetically by each record right though to A or X

                                i cant work out if this is possible?

                                Of course it's possible. It's hard, but it's possible.

                                At some point, run a query of all applicable users. That becomes your session variable.

                                For the search by name, do the pre version 5 edition of Query of Queries. It includes the cfloop and cfif tags. This enables you to display the records you want, complete with cfquery rownumber. Now your previous and next links can simply use the numbers 1 above and 1 below this number.

                                You've already added the logic for the first and last records of your query I assume.
                                • 13. Re: find next alpha record
                                  JohnGree Level 1
                                  Hi Dan,

                                  i have added my code below, so you can see where i am at.

                                  can you just let me know what code i need to change

                                  many thanks
                                  John

                                  • 14. Re: find next alpha record
                                    JohnGree Level 1
                                    ok i have decieded to just have the surname search return to the main page, and forget the results page.

                                    so when a user types in a surname it just finds the first surname from the query, and then the next and previous buttons will fing the next previuos surname.

                                    i have tried this code but it wont work any ideas?

                                    <cfquery name="Sget_names" datasource="#application.ds#">
                                    SELECT *
                                    FROM customerenquiryaddresses
                                    ORDER BY EqSurname
                                    LIMIT #start_record#, #records_per_page#
                                    </cfquery>

                                    <CFIF ISDEFINED ("form.Surname")>
                                    <cfquery name="get_names" dbtype="query">
                                    SELECT *
                                    FROM Sget_names
                                    WHERE EqSurname LIKE '#form.Surname#'
                                    </cfquery>
                                    <cfelse>
                                    <cfquery name="get_names" dbtype="query">
                                    SELECT *
                                    FROM Sget_names
                                    </cfquery>
                                    </CFIF>