paginating with php/postgresql

angelorigo

this line:
$sql.= " LIMIT $pagesize, $offset ";

only work well when i do let $offset, out of the code, so the script shows the number of pages, but do not make a link to the others pages, if $offset stay in this line it give me the folowing erros:

pg_query() query failed: ERROR: LIMIT #,# syntax not supported. Use separate LIMIT and OFFSET clauses. in /var/www/html/paginar.php on line 38

pg_num_fields(): supplied argument is not a valid PostgreSQL result resource in /var/www/html/paginar.php on line 52

pg_fetch_row(): supplied argument is not a valid PostgreSQL result resource in /var/www/html/paginar.php on line 54

Why this happens?
how to make it work?

Below is the complete code:

<?php
global $limit, $sql, $url, $pagesize, $pagecount, $absolutepage, $recordcount;
// Configuration...
$db = pg_connect("dbname=db user=user");
$sql= "SELECT FROM table";
$pagesize = 20 ;
$url = $_SERVER['PHP_SELF'];
if (!isset($absolutepage)) { // Set offset and absolutepage if not set
$absolutepage = 1;
$offset = 0;
} else {
$offset = (( $absolutepage - 1 ) $pagesize );
}
if (!isset($total_rows)) { // Get total_rows at startup
$result = pg_query($sql);
$recordcount = ( pg_numrows($result) ) -1;
$pagecount = intval($recordcount / $pagesize);
if ($recordcount % $pagesize ) $pagecount++;
}
if ($recordcount > 0) {
$sql.= " LIMIT $pagesize, $offset "; //Get record set = pagesize every time here is the trouble!
$result = pg_query($sql);
if ($absolutepage == 1) {
$pag1 = $absolutepage;
} else {
$pag1 = (($absolutepage - 1) * $pagesize) + 1;
}
$pag2 = $pag1 + ($pagesize - 1);
if ($pag2 > $recordcount)
$pag2 = $recordcount;
echo "<hr>";
echo "Exibindo registros $pag1 á $pag2 (of " . $recordcount.")<hr>";
PagNav($pagesize, $pagecount, $absolutepage, $recordcount, $sql, $url) ;
echo "<hr>";
$fields = pg_num_fields($result);
for ($i=0; $i < $pagesize; $i++) {
$data = pg_fetch_row($result);
for ($j=0; $j < $fields; $j++)
{
echo $data[$j] . " | ";
}
echo " \n";
}
} else {
echo "Registros não encontrados.";
}
echo "</body></html>";
function PagNav($pagesize, $pagecount, $absolutepage, $recordcount, $sql, $url)
{
global $sql, $url, $pagesize, $pagecount, $absolutepage, $recordcount;
$pad="" ;
$maxpages = $pagecount ;
if ( ($absolutepage % 10) == 0) {
$counterstart = $absolutepage - 9 ;
} else {
$counterstart = $absolutepage - ($absolutepage % 10) + 1 ;
}
$counterend = $counterstart + 9 ;
echo "Página ".$absolutepage." de ".$pagecount.": ";
if ($counterend > $maxpages) $counterend = $maxpages ;
if ($counterstart != 1) {
$ref = "<a href='" . $url ;
$ref .= "?absolutepage=" . "1" ;
$ref .= "'>Primeiro</a> | " ;
echo $ref ;
$ref = "<a href='" . $url ;
$ref .= "?absolutepage=" . ($counterstart - 1) ;
$ref .= "'>10 Páginas anteriores</a> " ;
echo $ref ;
}
echo "[ " ;
for ($counter = $counterstart; $counter < $counterend +1;$counter++ ) {
if ($counter >= 10) $pad="" ;
if (! ($counter == $absolutepage)) {
$ref = "<a href='" . $url ;
$ref .= "?absolutepage=" . $counter ;
$ref .= "'>" . $pad . $counter . "</a>" ;
} else {
$ref = "" . $pad . $counter . "" ;
}
echo $ref ;
if (!($counter == $counterend)) print " " ;
}
echo " ]" ;
if ($counterend != $maxpages) {
$ref = " <a href='" . $url ;
$ref .= "?absolutepage=" . ($counterend + 1) ;
$ref .= "'>Próximas 10 Páginas</a>" ;
echo $ref ;
$ref = " | <a href='" . $url ;
$ref .= "?absolutepage=" . $maxpages ;
$ref .= "'>Ùltima</a>" ;
echo $ref ;
}
echo "";
}
?>

Best regards

Sxooter

The syntax for postgresql is different, and more easily understood

select * from table order by somefield limit 1 offset 10

angelorigo

Originally posted by Sxooter
The syntax for postgresql is different, and more easily understood

select * from table order by somefield limit 1 offset 10

Thank´s Sxooter

It work!!

but still do not link to the next pages, as seen on my script is in this url:
http://www.colegioanchieta.g12.br/sitenovo/biblioteca/paginar.php

I am still looking by what can it be
do you have any idea

Sxooter

Feel free to post the php code here (in PHP tags is best, it color codes it for you). It's probably just something simple.

Also note that on many apache/PHP machines, you can copy / link the file to whatevername.phps and get a color coded version as well.

angelorigo

Originally posted by Sxooter
Feel free to post the php code here (in PHP tags is best, it color codes it for you). It's probably just something simple.

Also note that on many apache/PHP machines, you can copy / link the file to whatevername.phps and get a color coded version as well.

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

// Configuration... 
$db = pg_connect("dbname=biblioteca user=postgres"); 
$sql= "SELECT * FROM aquisicao order by id desc ";  

$pagesize = 20 ; 
$url = $_SERVER['PHP_SELF']; 
if (!isset($absolutepage)) { // Set offset and absolutepage if not set 
	$absolutepage = 1; 
	$offset = 0; 
} else { 
	$offset = (( $absolutepage - 1 ) * $pagesize ); 
} 
if (!isset($total_rows)) { // Get total_rows at startup 
	$result = pg_query($sql); 
	$recordcount = ( pg_numrows($result) ) -1; 
	$pagecount = intval($recordcount / $pagesize); 
	if ($recordcount % $pagesize ) $pagecount++; 
	} 
	if ($recordcount > 0) { 
			$sql.= " LIMIT $pagesize OFFSET $offset"; //  Get record set = pagesize every time 
			$result = pg_query($sql);

			if ($absolutepage == 1) { 
			$pag1 = $absolutepage; 
			} else { 
				$pag1 = (($absolutepage - 1) * $pagesize) + 1; 
				} 
			$pag2 = $pag1 + ($pagesize - 1); 
			if ($pag2 > $recordcount) 
			$pag2 = $recordcount; 
			echo "<hr><font face='verdana, arial' size=2>"; 
			echo "Exibindo registros $pag1 á $pag2 (of " . $recordcount.")<hr>"; 

PagNav($pagesize, $pagecount, $absolutepage, $recordcount, $sql, $url) ; 
echo "<hr>"; 
$fields = pg_num_fields($result); 
for ($i=0; $i < $pagesize; $i++) { 
	$data = pg_fetch_row($result); 
for ($j=0; $j < $fields; $j++) 
{ 
	echo $data[$j] . " | "; 
} 
echo "<br>\n"; 
} 
} else { 
echo "Registros não encontrados."; 
} 

echo "</body></html>"; 


function PagNav($pagesize, $pagecount, $absolutepage, $recordcount, $sql, $url) 
{ 
global $sql, $url, $pagesize, $pagecount, $absolutepage, $recordcount; 
$pad="" ; 
$maxpages = $pagecount ; 

if ( ($absolutepage % 10) == 0) { 
	$counterstart = $absolutepage - 9 ; 
} else { 
	$counterstart = $absolutepage - ($absolutepage % 10) + 1 ; 
} 
$counterend = $counterstart + 9 ; 
echo "<font size=1><b>Página ".$absolutepage." de ".$pagecount.": </b>"; 
if ($counterend > $maxpages) $counterend = $maxpages ; 
if ($counterstart != 1) { 
	$ref = "<a href='" . $url ; 
	$ref .= "?absolutepage=" . "1" ; 
	$ref .= "'>Primeiro</a>&nbsp;|&nbsp;" ; 
	echo $ref ; 
	$ref = "<a href='" . $url ; 
	$ref .= "?absolutepage=" . ($counterstart - 1) ; 
	$ref .= "'>10 Páginas anteriores</a>&nbsp;" ; 
	echo $ref ; 
	} 
	echo "<b>[ </b>" ; 
for ($counter = $counterstart; $counter < $counterend +1;$counter++ ) { 
if ($counter >= 10) $pad="" ; 
if (! ($counter == $absolutepage)) { 
	$ref = "<a href='" . $url ; 
	$ref .= "?absolutepage=" . $counter ; 
	$ref .= "'>" . $pad . $counter . "</a>" ; 
	} else { 
	$ref = "<b>" . $pad . $counter . "</b>" ; 
	} 
	echo $ref ; 
if (!($counter == $counterend)) print " " ; 
} 
echo "<b> ]</b>" ; 
if ($counterend != $maxpages) { 
	$ref = "&nbsp;<a href='" . $url ; 
	$ref .= "?absolutepage=" . ($counterend + 1) ; 
	$ref .= "'>Próximas 10 Páginas</a>" ; 
	echo $ref ; 
	$ref = "&nbsp;|&nbsp;<a href='" . $url ; 
	$ref .= "?absolutepage=" . $maxpages ; 
	$ref .= "'>Ùltima</a>" ; 
	echo $ref ; 
} 
echo "</font>"; 
} 
?>

[/COLOR]

Sxooter

Try printing out variables along the way to see if they're what you think they should be.

Are register globals on?

angelorigo

Originally posted by Sxooter
Try printing out variables along the way to see if they're what you think they should be.

Are register globals on?

No the register globals are off i am using php 4.2.2, i will use var_dump along the script

Sxooter

If reg globals are off, then you'll have to "suck in" the vars from the $_GET variable (I think that's the name) individually (or foreach them)

angelorigo

Originally posted by Sxooter
If reg globals are off, then you'll have to "suck in" the vars from the $_GET variable (I think that's the name) individually (or foreach them)

like in $SERVER['PHP_SELF'] ?
my doubt is if only $absolutepage must be changed that way or thereis another $GET do you see any other $_GET variable?

Sxooter

$absolutepage?

angelorigo

Originally posted by Sxooter
$absolutepage?

I think i need to review to identify wich variables are from $_GET variable

Do can see wich variables are from $_GET

Sxooter

Yep, use this fun trick on any variable:

foreach($_GET as $key=>$value){
  print $key.":".$value."<BR>";
}

Sxooter

If you just want a list of all the keys of a var, try this:

print implode(":",array_keys($arrayname));

angelorigo

Originally posted by Sxooter
Yep, use this fun trick on any variable:
foreach($_GET as $key=>$value){
 print $key.":".$value." ";
}
[/B]

how do it work ?
i take two variables and compare them ?

Sxooter

Nah, you just run it, and it will print out all the key:value pairs of the array $_GET.

angelorigo

Originally posted by Sxooter
Nah, you just run it, and it will print out all the key:value pairs of the array $_GET.

what a magic !!
i will run exactli as it is

angelorigo

Originally posted by angelorigo
what a magic !!
i will run exactli as it is

it print the absolutepage and the absolutepage number
i try to change &absolutepage for $_GET['$absolutepage']
but get an error . I do have make this change?

Sxooter

OK, the $_GET array is what is created by PHP when it parses the form. It's covered HERE:

http://www.php.net/manual/en/reserved.variables.php#reserved.variables.get

So, to change it, you have to actually change the request string:

http://server.com/phpfile.php?variable=value

so the change goes there to see it. You can also just edit the location window on your browser to force the change there.

angelorigo

Originally posted by Sxooter
OK, the $_GET array is what is created by PHP when it parses the form. It's covered HERE:

http://www.php.net/manual/en/reserved.variables.php#reserved.variables.get

So, to change it, you have to actually change the request string:

http://server.com/phpfile.php?variable=value

so the change goes there to see it. You can also just edit the location window on your browser to force the change there.

Hi , this code below do paginate, but:
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

<?php

// Database Connection 
include 'db.php'; 

// 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 paginar LIMIT $from OFFSET $max_results"); //LIMIT $pagesize OFFSET $offset

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

// Figure out the total number of results in DB: 
$total_results = pg_result(pg_query("SELECT COUNT(*) as Num FROM paginar"),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>";