CODEWITHSUNDEEP
phppostgresql
Hristian
Hristian

Reputation: 23

Paginating with php/postgresql

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.

Upvotes: 0

Views: 122

Answers (2)

Rajdeep Paul
Rajdeep Paul

Reputation: 16963

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");

Upvotes: 1

Farsay
Farsay

Reputation: 312

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

Upvotes: 0

Related Questions