Pagination using php and postgresql

Question

I have a postgresql query in the php file that returns over a hundred records. I would like to view only 10 records per page while using the same query. The query I am using is as below:

SELECT
    to_char(a.CALLDATE,'yyyymm')            AS month,
    min(a.calldate)                         AS start_time,
    max(a.calldate)                         AS end_time,
    ceil(sum(a.CALLDURATION::integer) / 60) AS minutes,
    count(DISTINCT a.IDENTIFIANT)           AS distinct_callers,
    a.zoneiddest                            AS country_code,
    b.country
FROM cdr_data a,
     COUNTRY_CODES b
WHERE
    a.CALLSUBCLASS = '002'
AND
    a.CALLCLASS = '008'
AND
    a.zoneiddest::integer > 0
AND
    substr(a.CALLEDNUMBER, 1, 2) NOT IN ('77', '78', '75', '70', '71', '41', '31', '39', '76','79')
AND
    NOT substr(a.zoneiddest , 1 ,3) IN ('254','255','256','211','257','250','256')
AND
    trim(a.zoneiddest) = trim(b.country_code)
GROUP BY
    to_char(a.CALLDATE,'yyyymm'),
    a.zoneiddest,
    b.country
ORDER BY 1

I have tried using JavaScript but it wont work. Is there a way I can edit this query or the do something in php to be able to achieve what I want?

I would be grateful if someone out there help because there seem to be numerous solutions if its pagination with php and mysql but not so much with php and postgresql.

Erwin Brandstetter · Accepted Answer · 2013-02-14 10:53:52Z

2

Simple solution would be to append:

OFFSET 20
LIMIT 10

answered Feb 14, 2013 at 10:53

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

roykasa Over a year ago

you mean like this: order by 1, OFFSET 20 ,LIMIT 10

Erwin Brandstetter Over a year ago

@roykasa: Yes, just without ,.

roykasa Over a year ago

But what would be a long solution in case i want to view past records without diving back into the code and changing the offset and limit values?

Erwin Brandstetter Over a year ago

@roykasa: I don't quite follow. For different results you have to change the query somehow. You could use a cursor for consistent results within a session/transaction (depends). But that's rarely useful for web use where you'd normally use connection pools or don't want to keep connections open and exclusive for one client indefinitely ...

Collectives™ on Stack Overflow

Pagination using php and postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related