Best I post the code I sent so you can see it in context
Its for paging
<CFSCRIPT>
if (IsDefined("URL.Start") AND IsNumeric(URL.Start))
iStart = URL.Start;
else
iStart = 1;
//How many messages displayed per page ?
iMessagesPerPage = 5;
//Work out the next and prev pages to display things
iNext = iStart + iMessagesPerPage;
iPrev = iStart - iMessagesPerPage;
</CFSCRIPT>
Change the query code to
<cfoutput query="query" STARTROW="#iStart#"
MAXROWS="#iMessagesPerPage#">
Below code is your paging code and would need to appear at the bottom -
<!--- paging --->
<table align="center" cellpadding="0" cellspacing="0" border="0"
width="96%"> <CFOUTPUT> <tr> <CFIF iPrev GT 0> <td align="left"
class="XXXXXXX" width="20%"><A HREF="XXXXXXX&Start=#iPrev#"
Class="XXXXXX"> « PREV</A></td>
<CFELSE><td class="XXXXXXXX" width="20%"> </td></CFIF>
<td height="20" align="center" class="XXXXXXX" width="60%" > Displaying
<strong>#iStart#</strong> - <CFIF iNext LTE
query.RecordCount><strong>#Evaluate(iNext - 1)#</strong>
<CFELSE><strong>#query.RecordCount#</strong>
</CFIF> of <strong>#query.RecordCount#</strong></td>
<CFIF iNext LTE query.RecordCount>
<td align="right" class="xxxxxxxxx" width="20%"><A
HREF="xxxxxxxxxx&Start=#iNext#"
class="xxxxxxx">NEXT » </A></td>
<CFELSE><td class="table_subheader" width="20%"> </td></CFIF> </tr>
</CFOUTPUT> </table>
-----Original Message-----
From: Tim Blair [mailto:[EMAIL PROTECTED]
Sent: 06 October 2003 10:43
To: [EMAIL PROTECTED]
Subject: RE: [ cf-dev ] Rownumber in query
> <cfoutput query="query" STARTROW="#iStart#"
> MAXROWS="#iMessagesPerPage#">
Does this not actually perform the query "unrestricted" and then CF does
the limiting? So that method's not so good if you've got a query qhich
would return 1,000,000 rows and you only want to see 10 of them...
I like the MySQL LIMIT clause - you specify the number of rows to
retrieve and the offset to start the retrieve from which puts the work
onto the DB server not the CF server and also means you're not pushing
1,000,000 rows of data across the network etc:
>From http://www.mysql.com/doc/en/SELECT.html :
------------------------------8<--------------------------
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must be integer constants. With one argument, the value specifies the
number of rows to return from the beginning of the result set. With two
arguments, the first specifies the offset of the first row to return,
the second specifies the maximum number of rows to return. The offset of
the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL
also supports the syntax: LIMIT row_count OFFSET offset.
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result
set, you can use -1 for the second parameter:
mysql> SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
If one argument is given, it indicates the maximum number of rows to
return:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.
------------------------------8<--------------------------
Tim.
-------------------------------------------------------
RAWNET LTD - Internet, New Media and ebusiness Gurus.
Visit our new website at http://www.rawnet.com for
more information about our company, or call us free
anytime on 0800 294 24 24.
-------------------------------------------------------
Tim Blair
Web Application Engineer, Rawnet Limited
Direct Phone : +44 (0) 1344 393 441
Switchboard : +44 (0) 1344 393 040
-------------------------------------------------------
This message may contain information which is legally
privileged and/or confidential. If you are not the
intended recipient, you are hereby notified that any
unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such
notification notwithstanding, any comments, opinions,
information or conclusions expressed in this message
are those of the originator, not of rawnet limited,
unless otherwise explicitly and independently indicated
by an authorised representative of rawnet limited.
-------------------------------------------------------
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
