RE: pagination and large recordsets (mysql)
There is a pagination cfc at cflib.org. As for that link...oh my gosh...sql injection anyone? -- Josh -Original Message- From: Mike Little [mailto:m...@nzsolutions.co.nz] Sent: Thursday, June 18, 2009 1:20 PM To: cf-talk Subject: pagination and large recordsets (mysql) hey guys, came across the following blog post... http://www.webveteran.com/blog/index.php/web-coding/coldfusion/mysql-and-col dfusion-pagination-version-2/ it is really great and works very fast indeed on a large amount of data. however i was wondering if anyone has seen a similar concept in cfc format eg. not having to put the WHERE clause in the url. mike ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323665 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: pagination and large recordsets (mysql)
haha - that was my though exactly !! thanks for the link. mike There is a pagination cfc at cflib.org. As for that link...oh my gosh...sql injection anyone? -- Josh hey guys, came across the following blog post... http://www.webveteran.com/blog/index.php/web-coding/coldfusion/mysql-and-col dfusion-pagination-version-2/ it is really great and works very fast indeed on a large amount of data. however i was wondering if anyone has seen a similar concept in cfc format eg. not having to put the WHERE clause in the url. mike ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323667 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: pagination and large recordsets (mysql)
Mike, The idea is pretty good - in fact, the mysql function found_rows() is very intriguing. Now, with the code example, I would say that is really, really bad SQL, putting the entire where clause in the URL - you're right to be suspicious. I am sure you could just rig up the query to use queryparams and it would be fine. Now, having said that, I also think reinventing pagination isn't useful at this point. I have this project at riaforge - http://paginationcfc.riaforge.org/ - a well documented CFC that handles pagination. The problem between my component and using mysql like Jules, is my component uses the recordcount of the query to determine how many pages there are. You can, however, extend it to do just that. Check the docs under extending to see an example: http://www.dopefly.com/projects/pagination/pagination_cfc_documentation.cfm#Section6 It looks like, to do it, you will probably have to make setTotalNumberOfItems() a public method, then also override the private method configureInputs. Let me know if you want any help with that. nathan strutz [Blog and Family @ http://www.dopefly.com/] [AZCFUG Manager @ http://www.azcfug.org/] [Twitter @nathanstrutz] On Thu, Jun 18, 2009 at 1:19 PM, Mike Little m...@nzsolutions.co.nz wrote: hey guys, came across the following blog post... http://www.webveteran.com/blog/index.php/web-coding/coldfusion/mysql-and-coldfusion-pagination-version-2/ it is really great and works very fast indeed on a large amount of data. however i was wondering if anyone has seen a similar concept in cfc format eg. not having to put the WHERE clause in the url. mike ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323668 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: pagination and large recordsets (mysql)
As for that link...oh my gosh...sql injection anyone? +1 The estimation using LIMIT and FOUND_ROWS() seems okay. But I would re-write the where clause logic to make it safer, and definitely use cfqueryparam. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323669 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: pagination and large recordsets (mysql)
http://paginationcfc.riaforge.org/ Works like a charm. On Thu, Jun 18, 2009 at 4:19 PM, Mike Little m...@nzsolutions.co.nz wrote: hey guys, came across the following blog post... http://www.webveteran.com/blog/index.php/web-coding/coldfusion/mysql-and-coldfusion-pagination-version-2/ it is really great and works very fast indeed on a large amount of data. however i was wondering if anyone has seen a similar concept in cfc format eg. not having to put the WHERE clause in the url. mike ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323671 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: pagination and large recordsets (mysql)
that's great thanks nathan. i too was intrigued by his code - and it really does work fast (i connect to mysql from a local development server for testing at the moment and the query works faster than anything else on the site ha!) i will check out your project for sure. cheers guys. i think i have what i need now. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323672 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4