MySQL limit cffunction for Query and QoQ's

Query of a Query functionality was introduced in ColdFusion 5 and it gives the developer the ability to re-query a returned recordset. While it does implement the core set of SQL SELECT commands the one bit of functionality I miss is MySQL's Limit function (I miss this in ANY relational database thats not MySQL as it makes paginiation a doddle).

Limit in MySQL works as such:SELECT * FROM myTable LIMIT 0, 10

The above code will display the first 10 results from your your (table is 0 indexed)

SELECT * FROM myTable LIMIT 5, 5

Starting from the 5th record this will bring back rows 6, 7, 8, 9, and 10

The idea behind the my <cffunction> is to mimic Limit with a passed in query. Of course this function can be used on any returned recordset but a lot of DB's have there own way of handling this which you might want to investigate first. var outQry = arguments.inQry; var a1 = arguments.arg1-1; var a2 = arguments.arg2-1; if(arg1 GT 1){ outQry.RemoveRows(JavaCast( "int", 0 ), JavaCast( "int", arg1 )); } outQry.RemoveRows(JavaCast( "int", arg2 ),JavaCast( "int", outQry.recordcount-arg2)); return outQry;

comments powered by Disqus