php postgres SQL pagination

Question

I need a pagination based on php/postgres.

With the code below, I can break de records but only shows the page 1 (link).

Any idea to fix this?

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * 5;
$query = pg_query($dbconn,"select * from my table limit 5 offset 0") or die(pg_result_error($dbconn));
$total_query = pg_num_rows($query);
$total_pages = ceil($total_query / 5);

the query result:

while($row = pg_fetch_assoc($query)){
     ...
}

for the pagination:

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='index.php?page=".$i."' class=\"textPagina\">".$i."</a>&nbsp;&nbsp;"; 
    }

You can give negative feedback to questions, but at least add something useful to those negative questions so they do not repeat themselves, it would be more helpful! — user4760387
– user4760387, Commented Jul 11, 2017 at 1:23

PotatoManager · Accepted Answer · 2017-07-11 01:19:14Z

6

I don't understand your code very well but the basic logic for a pagination sql query is

 Select * from pages limit $page_size offset $page_size*($page_no-1);

answered Jul 11, 2017 at 1:19

PotatoManager

1,7751 gold badge19 silver badges36 bronze badges

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

1 Comment

PotatoManager Over a year ago

You're welcome. Please accept the answer if you are satisfied.

user4760387 · Accepted Answer · 2017-07-11 02:52:08Z

I've fix it.

The code:

if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 

$recortds = 10; // change here for records per page

$start_from = ($page-1) * $records;

$qry = pg_query($dbconn,"select count(*) as total from table"); 
$row_sql = pg_fetch_row($qry); 
$total_records = $row_sql[0]; 
$total_pages = ceil($total_records / $records);

$select = pg_query($dbconn,"select * from table limit $records offset $start_from");

the select result:

while($row = pg_fetch_assoc($select )){
    echo $row['col1'].' | '.$row['col2'].' | '.$row['col3'].'<br />';
}

the pagination links:

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='index.php?page=".$i."' class='yourclass'>".$i."</a>&nbsp;&nbsp;"; 
}

Collectives™ on Stack Overflow

php postgres SQL pagination

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related