Robert Cummings wrote:
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
Yep mysql specific.
Postgres you have to do it the old fashioned way:
$count_query = "SELECT COUNT(...)";
$fetch_query = "SELECT " .....
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
