Thread: Paging results
HI there, Thanks to everyone who helped with my keyword problem - I think I thanked them all individually but I thought I should mention it here too. Now, a new question: Does anyone know if there's a PHP class anywhere out there for paging results from a PostgreSQL query, similar to Paginator or ezResults which do that for MySQL? Or do I have to do the code for that from scratch? Alternatively, would it be difficult to adapt one of those to working with PostgreSQL instead of MySQL? Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
Paging with PostgreSQL is super easy! select * from table where (x=y) offset 0 limit 30; Gives you the first 30 matches, then do select * from table where (x=y) offset 30 limit 30; This will give the next 30, super easy! Here's a sample of how I use it in a script // Collect offset $offset = isset($_GET['offset'])?$_GET['offset']:0; // Now the links for Prev/Next if ($offset >= 30) echo "|<a href='/contents.php?".$qs."offset=".($offset-30)."'>Back 30</a>"; echo "|<a href='/contents.php?".$qs."offset=".($offset+30)."'>Next 30</a>"; // Query $rs = pg_exec($db,"select id,name from stuff order by name offset $offset limit 30;"); /B ----- Original Message ----- From: "Lynna Landstreet" <lynna@gallery44.org> To: <pgsql-php@postgresql.org> Sent: Friday, August 08, 2003 13:30 Subject: [PHP] Paging results > HI there, > > Thanks to everyone who helped with my keyword problem - I think I thanked > them all individually but I thought I should mention it here too. > > Now, a new question: > > Does anyone know if there's a PHP class anywhere out there for paging > results from a PostgreSQL query, similar to Paginator or ezResults which do > that for MySQL? Or do I have to do the code for that from scratch? > > Alternatively, would it be difficult to adapt one of those to working with > PostgreSQL instead of MySQL? > > > Lynna > -- > Resource Centre Database Coordinator > Gallery 44 > www.gallery44.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
I have this script (please adapt the section where the
results are showed)
<?php
// Request for parameters.
$offset = $_REQUEST['offset'];
$pgnum = $_REQUEST['pgnum'];
// Make that they are integer - security.
settype($offset, 'integer');
settype($pgnum, 'integer');
// Open database, i.e PostgreSQL
$connuserpostgres");
if (!$conn) {
echo "An error occured.\n";
exit;
}
// Initialize variables.
$limit=20; // rows to return
$numresults=pg_query("select * from table"); //
PostgreSQL
$numrows=pg_num_rows($numresults);
// next determine if offset has been passed to
script, if not use 0
if (empty($offset)) {
$offset=0;
$pgnum=1;
}
// get results
$result=pg_query("select autor, titulo from table
limit $limit offset $offset"); // PostgreSQL
// now you can display the results returned
//echo "OffSet ".$offset." Page# ".$pgnum."<br><br>";
echo "<table><tr>";
echo "<td><b>Autor</b></td>";
echo "<td><b>Titulo</b></td>";
echo "</tr>";
while ($row = pg_fetch_array($result, $i)) {
echo "<tr><td align=\"left\">";
//$arr = pg_fetch_array($result,($i));
for ($j=0; $j < count($row); $j++) {
/*
echo "<tr><td align=\"left\" colspan=\"2\">";
echo "$row[$j] ";
echo "</td></tr>";
*/
echo "$row[$j]";
//echo " $j['autor']";
}
echo "</td></tr>";
//echo "<BR>";
}
echo "</table>";
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless
there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
echo "<font size=1>";
// next we need to do the links to other results
if (pages!=1)
{
if ($pgnum==1) {
print "<a
href=\"$PHP_SELF?offset=0&pgnum=1\">PREV</a>
\n";
}
else
{
$prevoffset=$offset-20;
$cpgnum = intval($prevoffset/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$prevoffset&pgnum=$cpgnum\">PREV</a>
\n";
}
}
for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
$cpgnum = $i;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">$cpgnum</a>
\n";
}
// check to see if last page
if ($pages!=1)
{
if ($pgnum<$pages) {
$newoffset=$offset+$limit;
$cpgnum = intval(($offset+$limit)/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">NEXT</a><p>\n";
}
else
{
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$pages\">NEXT</a><p>\n";
}
}
// Close database.
pg_close($conn);
?>
--- David Busby <busby@pnts.com> escreveu: > Paging
with PostgreSQL is super easy!
> select * from table where (x=y) offset 0 limit 30;
> Gives you the first 30 matches, then do
> select * from table where (x=y) offset 30 limit
> 30;
> This will give the next 30, super easy!
>
> Here's a sample of how I use it in a script
>
> // Collect offset
> $offset = isset($_GET['offset'])?$_GET['offset']:0;
> // Now the links for Prev/Next
> if ($offset >= 30) echo "|<a
>
href='/contents.php?".$qs."offset=".($offset-30)."'>Back
> 30</a>";
> echo "|<a
>
href='/contents.php?".$qs."offset=".($offset+30)."'>Next
> 30</a>";
> // Query
> $rs = pg_exec($db,"select id,name from stuff order
> by name offset $offset
> limit 30;");
>
> /B
>
>
> ----- Original Message -----
> From: "Lynna Landstreet" <lynna@gallery44.org>
> To: <pgsql-php@postgresql.org>
> Sent: Friday, August 08, 2003 13:30
> Subject: [PHP] Paging results
>
>
> > HI there,
> >
> > Thanks to everyone who helped with my keyword
> problem - I think I thanked
> > them all individually but I thought I should
> mention it here too.
> >
> > Now, a new question:
> >
> > Does anyone know if there's a PHP class anywhere
> out there for paging
> > results from a PostgreSQL query, similar to
> Paginator or ezResults which
> do
> > that for MySQL? Or do I have to do the code for
> that from scratch?
> >
> > Alternatively, would it be difficult to adapt one
> of those to working with
> > PostgreSQL instead of MySQL?
> >
> >
> > Lynna
> > --
> > Resource Centre Database Coordinator
> > Gallery 44
> > www.gallery44.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
=====
Ângelo Marcos Rigo
AMR Informática
(51) 3348 0870
Rua Pe. Alois Kades 400/210
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
_______________________________________________________________________
Conheça o novo Cadê? - Mais rápido, mais fácil e mais preciso.
Toda a web, 42 milhões de páginas brasileiras e nova busca por imagens!
http://www.cade.com.br
I have this script (please adapt the section where the
results are showed)
<?php
// Request for parameters.
$offset = $_REQUEST['offset'];
$pgnum = $_REQUEST['pgnum'];
// Make that they are integer - security.
settype($offset, 'integer');
settype($pgnum, 'integer');
// Open database, i.e PostgreSQL
$connuserpostgres");
if (!$conn) {
echo "An error occured.\n";
exit;
}
// Initialize variables.
$limit=20; // rows to return
$numresults=pg_query("select * from table"); //
PostgreSQL
$numrows=pg_num_rows($numresults);
// next determine if offset has been passed to
script, if not use 0
if (empty($offset)) {
$offset=0;
$pgnum=1;
}
// get results
$result=pg_query("select autor, titulo from table
limit $limit offset $offset"); // PostgreSQL
// now you can display the results returned
//echo "OffSet ".$offset." Page# ".$pgnum."<br><br>";
echo "<table><tr>";
echo "<td><b>Autor</b></td>";
echo "<td><b>Titulo</b></td>";
echo "</tr>";
while ($row = pg_fetch_array($result, $i)) {
echo "<tr><td align=\"left\">";
//$arr = pg_fetch_array($result,($i));
for ($j=0; $j < count($row); $j++) {
/*
echo "<tr><td align=\"left\" colspan=\"2\">";
echo "$row[$j] ";
echo "</td></tr>";
*/
echo "$row[$j]";
//echo " $j['autor']";
}
echo "</td></tr>";
//echo "<BR>";
}
echo "</table>";
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless
there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
echo "<font size=1>";
// next we need to do the links to other results
if (pages!=1)
{
if ($pgnum==1) {
print "<a
href=\"$PHP_SELF?offset=0&pgnum=1\">PREV</a>
\n";
}
else
{
$prevoffset=$offset-20;
$cpgnum = intval($prevoffset/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$prevoffset&pgnum=$cpgnum\">PREV</a>
\n";
}
}
for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
$cpgnum = $i;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">$cpgnum</a>
\n";
}
// check to see if last page
if ($pages!=1)
{
if ($pgnum<$pages) {
$newoffset=$offset+$limit;
$cpgnum = intval(($offset+$limit)/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">NEXT</a><p>\n";
}
else
{
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$pages\">NEXT</a><p>\n";
}
}
// Close database.
pg_close($conn);
?>
--- David Busby <busby@pnts.com> escreveu: > Paging
with PostgreSQL is super easy!
> select * from table where (x=y) offset 0 limit 30;
> Gives you the first 30 matches, then do
> select * from table where (x=y) offset 30 limit
> 30;
> This will give the next 30, super easy!
>
> Here's a sample of how I use it in a script
>
> // Collect offset
> $offset = isset($_GET['offset'])?$_GET['offset']:0;
> // Now the links for Prev/Next
> if ($offset >= 30) echo "|<a
>
href='/contents.php?".$qs."offset=".($offset-30)."'>Back
> 30</a>";
> echo "|<a
>
href='/contents.php?".$qs."offset=".($offset+30)."'>Next
> 30</a>";
> // Query
> $rs = pg_exec($db,"select id,name from stuff order
> by name offset $offset
> limit 30;");
>
> /B
>
>
> ----- Original Message -----
> From: "Lynna Landstreet" <lynna@gallery44.org>
> To: <pgsql-php@postgresql.org>
> Sent: Friday, August 08, 2003 13:30
> Subject: [PHP] Paging results
>
>
> > HI there,
> >
> > Thanks to everyone who helped with my keyword
> problem - I think I thanked
> > them all individually but I thought I should
> mention it here too.
> >
> > Now, a new question:
> >
> > Does anyone know if there's a PHP class anywhere
> out there for paging
> > results from a PostgreSQL query, similar to
> Paginator or ezResults which
> do
> > that for MySQL? Or do I have to do the code for
> that from scratch?
> >
> > Alternatively, would it be difficult to adapt one
> of those to working with
> > PostgreSQL instead of MySQL?
> >
> >
> > Lynna
> > --
> > Resource Centre Database Coordinator
> > Gallery 44
> > www.gallery44.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
=====
Ângelo Marcos Rigo
AMR Informática
(51) 3348 0870
Rua Pe. Alois Kades 400/210
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
_______________________________________________________________________
Conheça o novo Cadê? - Mais rápido, mais fácil e mais preciso.
Toda a web, 42 milhões de páginas brasileiras e nova busca por imagens!
http://www.cade.com.br
Do you have PEAR? There is in http://pear.php.net/package-info.php?package=DB_Pager, the dbpager package, wich works very well --- Lynna Landstreet <lynna@gallery44.org> escreveu: > HI there, > > Thanks to everyone who helped with my keyword > problem - I think I thanked > them all individually but I thought I should mention > it here too. > > Now, a new question: > > Does anyone know if there's a PHP class anywhere out > there for paging > results from a PostgreSQL query, similar to > Paginator or ezResults which do > that for MySQL? Or do I have to do the code for that > from scratch? > > Alternatively, would it be difficult to adapt one of > those to working with > PostgreSQL instead of MySQL? > > > Lynna > -- > Resource Centre Database Coordinator > Gallery 44 > www.gallery44.org > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ===== Ângelo Marcos Rigo AMR Informática (51) 3348 0870 Rua Pe. Alois Kades 400/210 Porto Alegre /RS/Brasil http://amr.freezope.org angelo_rigo@yahoo.com.br _______________________________________________________________________ Conheça o novo Cadê? - Mais rápido, mais fácil e mais preciso. Toda a web, 42 milhões de páginas brasileiras e nova busca por imagens! http://www.cade.com.br
Ângelo Marcos Rigo wrote: > Do you have PEAR? > getting started with pear: root@home#lynx -source http://go-pear.org | php you can run for test as normal user, than you have to configure the variables bin_dir, doc_dir, data_dir and php_dir. Set this to your home, you'll get a running pear-system. greetings, Gerd -- -------------------------------------------------------- # Gerd Terlutter | Mueller+Blanck Software GmbH # # gerd@MplusB.de | Gutenbergring 38 # # gerd.terlutter@web.de | D-22848 Noderstedt # # tel:0171/6992579 | tel:+49 40 500 171-1 # # Buero:040/500171-17 | fax:+49 40 500 171-71 # --------------------------------------------------------
Ângelo Marcos Rigo wrote: > Do you have PEAR? > > There is in > http://pear.php.net/package-info.php?package=DB_Pager, > the dbpager package, wich works very well >>Now, a new question: >> >>Does anyone know if there's a PHP class anywhere out >>there for paging >>results from a PostgreSQL query, similar to >>Paginator or ezResults which do >>that for MySQL? Or do I have to do the code for that >>from scratch? >> >>Alternatively, would it be difficult to adapt one of >>those to working with >>PostgreSQL instead of MySQL? >> >> >>Lynna Hi all, PEAR is general a good idea. I use PEAR::DB as wrapper for access do different RDBMS, in my case PG and MySQL. nxet week i'll start with transaction on MySQL3.23.51, but don't know what happends. Other functionality is given during use PEAR::DB. This package supports 10 or 12 RDBMS. To convert MySQL to PG try this: http://ns2.ziet.zhitomir.ua/~fonin/projects/my2pg/my2pg_man.html Another good tipp is this: http://www.rot13.org/~dpavlin/sql.html If you have a good config-file for your source, you can switch by changing only the param 'dbtype' e.g. psql or mysql. excuse my bad english, Gerd -- -------------------------------------------------------- # Gerd Terlutter | Mueller+Blanck Software GmbH # # gerd@MplusB.de | Gutenbergring 38 # # gerd.terlutter@web.de | D-22848 Noderstedt # # tel:0171/6992579 | tel:+49 40 500 171-1 # # Buero:040/500171-17 | fax:+49 40 500 171-71 # --------------------------------------------------------
on 8/11/03 2:14 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote: > Do you have PEAR? > > There is in > http://pear.php.net/package-info.php?package=DB_Pager, > the dbpager package, wich works very well I'd never heard of it before - I'm fairly new to PHP. I just looked through the documentation a bit, but I'm a bit confused - is this something I can actually install on my own, in the gallery's home directory, or is it something that our web host would have to install? We don't have our own server; we're just a on a shared hosting plan. Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
on 8/11/03 1:31 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote: > I have this script (please adapt the section where the > results are showed) Thank you very much - I've implemented this on one of my results pages as a test, and it seems to be partially working - the results are limited properly, and there are no errors, but the next and previous links aren't quite working right. On the first page, the prev link points to offset 0 and page 1, which I guess is correct, but the next link leaves offset blank, and has page 0, instead of having offset 50 (that's what I set $limit to) and page 2 as I would think it should. If I enter those values manually in the URL - http://www.gallery44.org/db/artists_browse_paged.php?offset=50&pgnum=2 - the second page displays correctly, but again the prev and next links don't work right. The prev page here shows pgnum=1, which is correct, but it shows offset as 30 when it should be 50. And the next link shows exactly what it did on page 1 - offset=&pgnum=0. I've looked at the code, but I don't think I'm following it well enough to be sure what's going wrong. I've attached a copy of the file I kept the code pretty much as it was except for the part where the results are displayed, and a couple of very minor tweaks (changed the pg_query and pg_num_rows to pg_exec and pg_numrows because I'm using PHP 4.1, changed the part where it selects the whole table and uses pg_numrows to count it to a select count because it seemed more efficient, and changed $conn to the database connection I'd already opened, $db). If you or anyone else could suggest what might be going wrong, I'd very much appreciate it. Here's what I've got: First part (before displaying query results): // Paging script - request for parameters. $offset = $_REQUEST['offset']; $pgnum = $_REQUEST['pgnum']; // Make that they are integer - security. settype($offset, 'integer'); settype($pgnum, 'integer'); // Open database, i.e PostgreSQL if (!$db) { echo "An error occured - no database connection exists.\n"; exit; } // Initialize variables. $limit=50; // rows to return $numresults=pg_exec("SELECT COUNT(*) FROM artists"); //PostgreSQL // $numrows=pg_numrows($numresults); // next determine if offset has been passed to script, if not use 0 if (empty($offset)) { $offset=0; $pgnum=1; } Then comes the display of the results, which is working fine. Second part: // calculate number of pages needing links $pages=intval($numrows/$limit); // $pages now contains int of pages needed unless there is a remainder from division if ($numrows%$limit) { // has remainder so add one page $pages++; } echo "\n<p class=\"small\">"; // next we need to do the links to other results if (pages!=1) { if ($pgnum==1) { print "<a href=\"$PHP_SELF?offset=0&pgnum=1\">PREV</a> \n"; } else { $prevoffset=$offset-20; $cpgnum = intval($prevoffset/$limit)+1; print "<a href=\"$PHP_SELF?offset=$prevoffset&pgnum=$cpgnum\">PREV</a> \n"; } } for ($i=1;$i<=$pages;$i++) { // loop thru $newoffset=$limit*($i-1); $cpgnum = $i; print "<a href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">$cpgnum</a> \n"; } // check to see if last page if ($pages!=1) { if ($pgnum<$pages) { $newoffset=$offset+$limit; $cpgnum = intval(($offset+$limit)/$limit)+1; print "<a href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">NEXT</a></p>\n"; } else { print "<a href=\"$PHP_SELF?offset=$newoffset&pgnum=$pages\">NEXT</a></p>\n"; } } Many thanks, Lynna -- Resource Centre Database Coordinator Gallery 44 www.gallery44.org
On Wed, 13 Aug 2003, Lynna Landstreet wrote: > on 8/11/03 2:14 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote: > > > Do you have PEAR? > > > > There is in > > http://pear.php.net/package-info.php?package=DB_Pager, > > the dbpager package, wich works very well > > I'd never heard of it before - I'm fairly new to PHP. I just looked through > the documentation a bit, but I'm a bit confused - is this something I can > actually install on my own, in the gallery's home directory, or is it > something that our web host would have to install? We don't have our own > server; we're just a on a shared hosting plan. PEAR, and about 12 or so other packages, are usually distributed with PHP and found in the /usr/local/lib/php directory on most Unix boxes.