Paginating with php/postgresql

Question

i'm trying to create paging pages but i have got stuck here:

<?php  
    global $limit, $sql, $url, $pagesize, $pagecount, $absolutepage, $recordcount;  

    // Configuration...  
    $db = pg_Connect("dbname=Test user=postgres password=1111");

// If current page number, use it 
// if not, set one! 

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

// Define the number of results per page 
$max_results = 10; 

// Figure out the limit for the query based 
// on the current page number. 
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page number's results 

$sql = pg_query("SELECT * FROM import.mock_data LIMIT $from OFFSET $max_results"); //LIMIT $pagesize OFFSET $offset

while($row = pg_fetch_array($sql)){ 
// Build your formatted results here. 
echo $row['first_name']."<br />"; 
} 

// Figure out the total number of results in DB: 
$total_results = pg_result(pg_query("SELECT COUNT(*) as Num FROM import.mock_data"),0); 

// Figure out the total number of pages. Always round up using ceil() 
$total_pages = ceil($total_results / $max_results); 

// Build Page Number Hyperlinks 
echo "<center>Selecione uma página<br />"; 

// Build Previous Link 
if($page > 1){ 
$prev = ($page - 1); 
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\"><<Previous</a>&nbsp;"; 
} 

for($i = 1; $i <= $total_pages; $i++){ 
if(($page) == $i){ 
echo "$i&nbsp;"; 
} else { 
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a>&nbsp;"; 
} 
} 

// Build Next Link 
if($page < $total_pages){ 
$next = ($page + 1); 
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">Next>></a>"; 
} 
echo "</center>"; 
?>

At this time i have this problems: 1- do not shows nothing at the beggining, just shows at the page 2 and on ... 2 - it just not pagionate but add the next ten records of the page 3 to the previous 10 records from the page 2.

Rajdeep Paul · Accepted Answer · 2017-07-24 07:31:28Z

1

The problem lies in the first SELECT query,

$sql = pg_query("SELECT * FROM import.mock_data LIMIT $from OFFSET $max_results");
                                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

With the current logic when you hop from page to page, your OFFSET would be same and the LIMIT would be getting increased from 0, 10, 20 and so on. Whereas in reality it should be the other way around. So your SELECT query should be like this:

$sql = pg_query("SELECT * FROM import.mock_data LIMIT $max_results OFFSET $from");

edited Jul 24, 2017 at 7:31

answered Jul 24, 2017 at 7:25

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

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

2 Comments

Hristian Over a year ago

Thank you :) That was shocking for me. I need more carefully to examine the code.

Rajdeep Paul Over a year ago

@Hristian You're welcome! :-) Glad I could help. Cheers!

Farsay · Accepted Answer · 2017-07-24 07:13:58Z

0

You got too many errors.

first you have to calculate offset like this

if(isset($_GET['page']))
{
    $page = $_GET{'page'} + 1;
    $from = $max_results * $page ;
}
else 
{
            $page = 0;
            $from = 0;
}

Please follow this link https://www.tutorialspoint.com/php/mysql_paging_php.htm

answered Jul 24, 2017 at 7:13

Farsay

3121 silver badge9 bronze badges

1 Comment

Rajdeep Paul Over a year ago

There's no issue with the OFFSET here. OP has already calculated OFFSET in the following way, $from = (($page * $max_results) - $max_results);

Collectives™ on Stack Overflow

Paginating with php/postgresql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related