CFM codesnippet: navigation through large datasets with mySQL (SQL_CALC_FOUND_ROWS)
By Ruben on Feb 1, 2007 | In ColdFusion, MySQL 5 | Send feedback »
Codesnippet:
Following code uses CFM + MySQL to select a part of all available records from a table. But also use 'SQL_CALC_FOUND_ROWS' to obtain the total amount of records in the table.
This way you can make page navigation through large datasets.
Requires:
An installed mysql-connector-java-3.x (jar)
Following codesnippet is provided by BehindThe.net. Thanks Ernst!
<!--- use transaction for isolating foundRows --->
<cftransaction isolation="read_uncommitted">
<!--- get records from Database --->
<cfquery name="getMany" datasource="#application.datasources[1].name#">
SELECT SQL_CALC_FOUND_ROWS
job_id
,job_code
FROM jobs
ORDER BY #orderBy#
<cfif isNumeric(arguments.maxRows)>
LIMIT #firstRowLimit#,#arguments.maxRows#
</cfif>
</cfquery>
<cfquery name="getFoundRows" datasource="#application.datasources[1].name#">
SELECT found_rows() AS foundRows
</cfquery>
</cftransaction>
<!--- add a column to getMany query and set first row with value of foundRows --->
<cfset newColumnValues[1] = getFoundRows.foundRows>
<cfset newColumn = QueryAddColumn(getMany, "foundRows", newColumnValues)>
No feedback yet
Comments are closed for this post.
« Speciale Joomla! hostingdienst gestart | New tutorials site for Red5 » |