Re: [PHP-DB] Getting total results
Joseph Crawford wrote: Actually i thought keeping all the data in one query stored in a class was more efficient than a database connection with each page change, however if i am wrong please explain why more database connections is less efficient... Your class isn't persisting over pages, though, is it? If so, how are you doing it? A singleton class is good for multiple calls to it on the same page / within the same script. It doesn't persist across pages unless you're doing something else with it. Even if you are, serializing/unserializing (I assume) a class with 100 data units in it may not be more efficient than connecting to the database and just retrieving 10 data units at a time... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
actually, i was not doing anything with it because it was just the class. The way i use it is that i would store the object in a session variable for use accross pages :D -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Getting total results
Hello everybody, I am trying to split results comming from a mysql db with php into more html pages. Example: Results 1-10 out of 100 Therefor I limit the sql statement with: limit 1, 10 The sql statement is very complex. So far I have always used the same statement but without the limit and with a count inside to get the total number of results. Now with this statement it would be a huge decrease in performance. Is there a way to work with the limit statement, but to get the total number of results? As far as I know the LIMIT command only limits the visiblity, but mysql queries all the data. Any ideas which could help? Thank you in advance, Merlin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
Merlin wrote: I am trying to split results comming from a mysql db with php into more html pages. Example: Results 1-10 out of 100 Therefor I limit the sql statement with: limit 1, 10 The sql statement is very complex. So far I have always used the same statement but without the limit and with a count inside to get the total number of results. Now with this statement it would be a huge decrease in performance. Is there a way to work with the limit statement, but to get the total number of results? As far as I know the LIMIT command only limits the visiblity, but mysql queries all the data. What version of MySQL? If 4.0+ you could use SELECT SQL_CALC_FOUND_ROWS column1, column2, ... FROM TABLE WHERE ... LIMIT x,y to get the rows you want to retrieve and then use SELECT FOUND_ROWS() to get the total number of rows there would have been without the limit. Still two queries (no way around that, anyhow), but hopefully optimized better by MySQL. Perform your own tests, of course. See the documentation on FOUND_ROWS() at the following URL for more info: http://dev.mysql.com/doc/mysql/en/Information_functions.html -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
this is a good method, however i created a paging class in php 5 and i believe i still have it here locally in php 4 i will get the link soon :D -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
http://www.weberdev.com/get_example-4005.html that is the url to the PHP 5 object i created, it was created to show the use of the singleton pattern. This means you can only use this class to have one pager per page. i can edit the code if you do not know how to make it so you can have more than one per page. the php 4 version i will need to zip up locally here and will post it @ http://codebowl.dontexist.org/pager_php4.zip -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
php 4 version is posted, let me know if you like it or not. Please keep in mind it is a very basic class, you could extend this in many ways, especially to make the nav bar do something like 1 2 3 4 25 26 27 28 -- Joseph Crawford Jr. Codebowl Solutions [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Getting total results
Joseph Crawford wrote: http://www.weberdev.com/get_example-4005.html that is the url to the PHP 5 object i created, it was created to show the use of the singleton pattern. This means you can only use this class to have one pager per page. i can edit the code if you do not know how to make it so you can have more than one per page. So you're proposing he uses this class and loads the _entire_ result of a query (with no LIMIT) into an array, then passes that to your class which chops it up into pages and then displays one of them? Do you really think that's more efficient? The question was about how to make two queries more efficient: SELECT ... FROM table WHERE ... LIMIT x,y SELECT COUNT(*) FROM table WHERE ... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
