On Mon, 2006-10-23 at 10:13 +0200, Ivo F.A.C. Fokkema wrote:
> On Fri, 20 Oct 2006 12:06:26 -0400, Robert Cummings wrote:
>
> > On Fri, 2006-10-20 at 17:22 +0200, Ivo F.A.C. Fokkema wrote:
> >> On Fri, 20 Oct 2006 17:04:35 +0200, Fourat Zouari wrote:
> >>
> >> > I have PHP/PostgreSQL application were i got a search page with some
> >> > items
> >> > to search, am building the search query on server side.
> >> >
> >> > I need to display a paginated search and for this i need to get the total
> >> > count of lines matching the search before OFFSET/LIMITing my page, am i
> >> > obliged to repeat the query twice ??? first to get the total count,
> >> > second
> >> > to get my page.
> >> >
> >> > it's very heavy
> >> >
> >> > Any one's suggesting better doing ?
> >>
> >> As far as I know, this is the only way. The first query, you don't need to
> >> sort your data though, and you might be able to drop a join, depending on
> >> whether or not you use the joined table in your WHERE clause.
> >>
> >> But I think due to caching the database will not take a long time for the
> >> second query, since it just recently had (almost) the same query - YMMV.
> >
> > Hell no, don't use the same query twice. Use a count in the first query
> > that only returns 1 row... the count. The second query can return the
> > records (which may be less than the count returns since you're paging).
>
> There must have been a reason why I started doing this... I used to use
> COUNT(*) first too, then run the full query but somehow this must have not
> worked for me when searching though a complex set of JOIN'ed tables or
> so... after which I have my query builder run the query first without
> the order clause. I'm going to look into this, see if I can track that
> down.
>
> But you're right, I should've mentioned that in his case a COUNT(*)
> could've been possible, since I didn't know his table structure or query.
You can also use this dirty little sucker that's specific to MySQL
(AFAIK):
SQL_CALC_FOUND_ROWS
Just add it right after the SELECT keyword:
SELECT SQL_CALC_FOUND_ROWS ...
Then afterwards you issue another query:
SELECT FOUND_ROWS() AS YeeeeeeeeeeHaaaaaaaaaw
And you're all set. it works regardless of the complexity of joins and
other stuff.
Cheers,
Rob.
--
.------------------------------------------------------------.
| InterJinn Application Framework - http://www.interjinn.com |
:------------------------------------------------------------:
| An application and templating framework for PHP. Boasting |
| a powerful, scalable system for accessing system services |
| such as forms, properties, sessions, and caches. InterJinn |
| also provides an extremely flexible architecture for |
| creating re-usable components quickly and easily. |
`------------------------------------------------------------'
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
