• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

Number of results

I was wondering if you could help me...

how can you let someone choose how many results he/she wants to see on the screen?

he/she just types a number in a textfield en pushes "enter"..

Can you help me?

Greetz
0
sausmaster
Asked:
sausmaster
  • 4
  • 4
  • 4
  • +3
1 Solution
 
hartCommented:
I think you are trying to do paging..

All you have to do is let the user enter the number of results in the textbox

then submit the page to the same page and and hit the query with that many number records (restriction rownum/top etc..)


hope u got the logic

Regards
Hart
0
 
TallerMikeCommented:
To elaborate on hart's comment, there are a few ways that you can do this:

Lets say you have a form input called 'maxRows', and you submit that to a page with a query. You can have the query return that many row like so:

SELECT TOP #form.maxRows# *
FROM myTable

Or, you can do it within the cfquery tag like so:

<cfquery maxrows="#maxRows#">
  SELECT *
  FROM myTable
</cfquery>

******************************************************************************************************************
******************************************************************************************************************

Of course sometimes what you really want to do is to show them x number of records, and then give them buttons for the next and previous sets (called paging, or pagination). For that you will use ethe same query, but put the maxRows in the CFOUTPUT or CFLOOP tags. You'll also need to supply a startRow.

It should be also noted that CFLOOP and CFQUERY don't handle paging the same. CFOUTPUT for a query wants to know the startRow and the maxRows, where as CFLOOP wants a startRow and an endRow. Odd huh?

Anyhow, Hart is correct.
0
 
crosenblumCommented:
or in sql server you can do

set rowcount 10
Select *
from mytable

it is always better for performance to never allow anyone to choose the max row value.

it is much better to pick how many to display on a page, then use a previous/next page system.

There are a lot of free custom tags to do that.

http://devex.macromedia.com/developer/gallery

Above is where all the custom tags from Macromedia are.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
WasistdasCommented:
Actual code taken from cfm-resources.com, tuned for your needs (default records per page is 10) sample.cfm:

<cfif isdefined("form.i_pp")>
<cfset session.i_pp=#i_pp#>
<cfelse>
<cfset session.i_pp=10>
</cfif>
<cfparam name="page" default="1">
<cfset i_pp=#session.i_pp#>
<cfset startrow=1+(int(page-1)*int(i_pp))>
<cfset totalpages=ceiling(Yourquery.recordcount/i_pp)>

<cfoutput query="Yourquery" startrow="#startrow#" maxrows="#i_pp#">
#Yourdata#
</cfoutput>

<cfif Yourquery.recordcount>
<cfif page gt 1>
<cfif page-1 is 1>
<cfset prevpage="?">
<cfelse>
<cfset prevpage="?page=#evaluate(page-1)#">
</cfif>
<cfoutput>
<a href="sample.cfm#prevpage#">&lt;</a>
</cfoutput>
<cfloop index="i" from="1" to="#evaluate(page-1)#">
<cfif i gte evaluate(page-5)>
<cfif i is 1>
<cfoutput>
<a href="sample.cfm">#i#</a>
</cfoutput>
<cfelse>
<cfoutput>
<a href="sample.cfm?page=#i#">#i#</a>
</cfoutput>
</cfif>
<cfelseif i is 1>
<cfoutput><a href="sample.cfm">1</a>
</cfoutput>
<cfelseif fix(evaluate(i/10)) is evaluate(i/10)>
<cfoutput>
<a href="sample.cfm?page=#i#">#i#</a>
</cfoutput>
</cfif>
</cfloop>
</cfif>

<cfoutput>
<b> #page# </b>
<cfif page lt #totalpages#>
<cfloop index="i" from="#evaluate(page+1)#" to="#totalpages#">
<cfif i lte evaluate(page+5)>
<a href="sample.cfm?page=#i#">#i#</a>
<cfelseif fix(evaluate(i/10)) is evaluate(i/10)>
<a href="sample.cfm?page=#i#">#i#</a>
<cfelseif i is #totalpages#>
<a href="sample.cfm?page=#i#">#i#</a>
</cfif>
</cfloop>
<a href="sample.cfm?page=#evaluate(page+1)#">&gt;</a>
</cfif>

<form name="ssearch" method="post" action="sample.cfm">
<input type="text" name="i_pp" size="4">
<input type="submit" name="Submit" value="Set records per page">
</form>
0
 
crosenblumCommented:
That's ugly code. Don't you know you do not need to put # in cfif or cfset statements?

Evaluate is a powerful function, but can be bad for performance if you are looping thru it.

0
 
TallerMikeCommented:
"ColdFusion is not about elegance or pretty or beautiful"

I see you're changing your tune crosenblum? Lol...

Anyhow, I agree with crosenblum, that's some REALLY ugly code. Not just inefficient, but rather ugly. And since you didn't write it, I can say (hopefully without offending) that it was written by a rather inexperienced programmer. It was written line by line, each problem with a new solution. Instead of carefully thought out.
0
 
crosenblumCommented:
I know, but after having cleaned up the code of other coders, I'd rather encourage cleaner, more readable, more stable code.

0
 
WasistdasCommented:
For instance?
0
 
crosenblumCommented:
Coding for readability, imagines that you plan for the future when you or some other have to modify, update your code. If you think of this when you code, and adapt some standards for coding, it will be much smoother for you.

No #'s in cfset or cfif
Indenting for logic flow
Stick to coding in Lower Case except when the text being displayed is not lower cased.
Proper Database Usage
Specific select statements grabbing specific fields from specific tables,views or whatnot.
Lots and lots of clear commenting.
Source Control

That's a starting point.
0
 
TallerMikeCommented:
For instance, your code usesd Evaluate() to add 2 numbers together when it should use Val(). Your variable names are not named to allow someone to be able to understand what they do. There are no comments. To allow the user to be able to change the number of rows to be displayed dynamically, you would have to change the value in multiple places. Is there a way to display all records? What if there were criteria in the URL or posted through a form that would need to be sent along as well? Consider the number of steps to modify this code before you can copy and paste it to another page. Would it be possible to create a module out of this code to allow you to simply reference this code without having to add it and modify it on every page that uses it? There is no tabbing. What if the user intends to use CFLOOP instead of CFOUTPUT to output the records? Why are you setting a session variable?
0
 
WasistdasCommented:
Philosophy is good but practical things are much much better. Offer a good solution and you will be rewareded with points by me.
0
 
substandCommented:
here is a practical and better solution, offered in the CF help by pressing F1 over the cfquery tag:

<!--- This example shows the use of CFQUERY --->

<HTML>
<HEAD>
    <TITLE>CFQUERY Example</TITLE>
</HEAD>

<BODY>
<H3>CFQUERY Example</H3>

<!--- define startrow and maxrows to facilitate
    'next N' style browsing --->
<CFPARAM NAME="MaxRows" DEFAULT="10">
<CFPARAM NAME="StartRow" DEFAULT="1">

<!--- query database for information --->
<CFQUERY NAME="GetParks" DATASOURCE="cfsnippets">
SELECT      PARKNAME, REGION, STATE
FROM         Parks
ORDER by ParkName, State
</CFQUERY>

<!--- build HTML table to display query --->
<TABLE cellpadding=1 cellspacing=1>
<TR>
    <TD colspan=2 bgcolor=f0f0f0>
    <B><I>Park Name</I></B>
    </TD>
    <TD bgcolor=f0f0f0>
    <B><I>Region</I></B>
    </TD>
    <TD bgcolor=f0f0f0>
    <B><I>State</I></B>
    </TD>
</TR>

<!--- Output the query and define the startrow and maxrows
      parameters. Use the query variable CurrentCount to
      keep track of the row you are displaying. --->
<CFOUTPUT QUERY="GetParks" StartRow="#StartRow#" MAXROWS="#MaxRows#">
<TR>
    <TD valign=top bgcolor=ffffed>
    <B>#GetParks.CurrentRow#</B>
    </TD>
    <TD valign=top>
    <FONT SIZE="-1">#ParkName#</FONT>
    </TD>
    <TD valign=top>
    <FONT SIZE="-1">#Region#</FONT>
    </TD>
    <TD valign=top>
    <FONT SIZE="-1">#State#</FONT>
    </TD>
</TR>
</CFOUTPUT>

<!--- If the total number of records is less than or equal
to the total number of rows, then offer a link to
the same page, with the StartRow value incremented by
MaxRows (in the case of this example, incremented by 10) --->
<TR>
    <TD colspan=4>
    <CFIF (StartRow + MaxRows) LTE GetParks.RecordCount>
        <a href="cfquery.cfm?startrow=<CFOUTPUT>#Evaluate(StartRow +
        MaxRows)#</CFOUTPUT>">See next <CFOUTPUT>#MaxRows#</CFOUTPUT>
        rows</A>
    </CFIF>
   
    </TD>
</TR>
</TABLE>
</BODY>
</HTML>


0
 
substandCommented:
does that work for points from both of you (sausmaster and Wasistdas)

=)
0
 
substandCommented:
now if you want to allow the user to select, then just set the <cfparam> name to "form.maxrows" instead of "maxrows" and make a form field that asks before submitting to that page.

0
 
WasistdasCommented:
No. Simple next/previous is of no interest.
0
 
sausmasterAuthor Commented:
Thanx everybody
0
 
substandCommented:
Wasistdas:

if "next" / "previous" is defined as current+maxrows and current-maxrows, then you simply need to have more links where the "startrow" on each is defined as

for the next one after "next" define as current+2*maxrows

for the one before the previous one define as currentrow-2*maxrows.

then apply induction each way until you are at the 1st and last page.

its not hard to extrapolate.  

is that good enough, or do you want me to repost the code?

I figured the designers of the language probably know the most about it, so I posted thier code.

sausmaster:  thanks, and I'm glad you got your question answered.


0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now