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/I..._functions.html

--

---John Holmes...

Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

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

Joseph Crawford wrote:
> 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 >>
>

Hi Joseph,

thanx for your reply. I do already have some classe which do most of the job
 and
I would rather not like to change the whole system. Johns suggestion seems
sufficent to me. It works excellent in my case:

SELECT FOUND_ROWS()

to get the total number of rows there would have been without the limit. Sti
ll
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/I..._functions.html

Thank you everybody,

Merlin

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