?
Solved

Number of results

Posted on 2003-04-01
17
Medium Priority
?
130 Views
Last Modified: 2013-12-24
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
Comment
Question by:sausmaster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
  • +3
17 Comments
 
LVL 11

Expert Comment

by:hart
ID: 8245424
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
 
LVL 8

Expert Comment

by:TallerMike
ID: 8245801
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
 
LVL 4

Expert Comment

by:crosenblum
ID: 8248282
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 1

Expert Comment

by:Wasistdas
ID: 8249582
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
 
LVL 4

Expert Comment

by:crosenblum
ID: 8253639
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
 
LVL 8

Expert Comment

by:TallerMike
ID: 8253756
"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
 
LVL 4

Expert Comment

by:crosenblum
ID: 8256278
I know, but after having cleaned up the code of other coders, I'd rather encourage cleaner, more readable, more stable code.

0
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8256401
For instance?
0
 
LVL 4

Expert Comment

by:crosenblum
ID: 8257284
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
 
LVL 8

Expert Comment

by:TallerMike
ID: 8257336
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
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8257998
Philosophy is good but practical things are much much better. Offer a good solution and you will be rewareded with points by me.
0
 
LVL 10

Expert Comment

by:substand
ID: 8258209
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
 
LVL 10

Expert Comment

by:substand
ID: 8258211
does that work for points from both of you (sausmaster and Wasistdas)

=)
0
 
LVL 10

Accepted Solution

by:
substand earned 75 total points
ID: 8258216
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
 
LVL 1

Expert Comment

by:Wasistdas
ID: 8259183
No. Simple next/previous is of no interest.
0
 

Author Comment

by:sausmaster
ID: 8259205
Thanx everybody
0
 
LVL 10

Expert Comment

by:substand
ID: 8259312
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question