find next alpha record

[Guest]

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>

[Dan_Bracuk]

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.

[Guest]

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.

[Dan_Bracuk]

Keep the query and the currentrow in the session state. Use the session variable with your currentrow in your anchor tag.

[Guest]

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?

[efecto747]

Here's a suggestion:

[Guest]

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?

[Dan_Bracuk]

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.

[Guest]

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

[Dan_Bracuk]

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.

[Dan_Bracuk]

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?

[Guest]

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?

[Dan_Bracuk]

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.

[Guest]

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

[Guest]

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>