Paging results
am 08.08.2003 22:30:06 von Lynna Landstreet
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
Re: Paging results
am 08.08.2003 23:59:07 von David Busby
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 "|
href='/contents.php?".$qs."offset=".($offset-30)."'>Back 30";
echo "|";
// Query
$rs = pg_exec($db,"select id,name from stuff order by name offset $offset
limit 30;");
/B
----- Original Message -----
From: "Lynna Landstreet"
To:
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
Re: Paging results
am 11.08.2003 19:31:47 von angelo_rigo
I have this script (please adapt the section where the
results are showed)
// Request for parameters.=20
$offset =3D $_REQUEST['offset'];=20
$pgnum =3D $_REQUEST['pgnum'];=20
// Make that they are integer - security.=20
settype($offset, 'integer');=20
settype($pgnum, 'integer');=20
// Open database, i.e PostgreSQL=20
$connuserpostgres");=20
if (!$conn) {=20
echo "An error occured.\n";=20
exit;=20
}=20
// Initialize variables.=20
$limit=3D20; // rows to return=20
$numresults=3Dpg_query("select * from table"); //
PostgreSQL=20
$numrows=3Dpg_num_rows($numresults);=20
// next determine if offset has been passed to
script, if not use 0=20
if (empty($offset)) {=20
$offset=3D0;=20
$pgnum=3D1;=20
}=20
// get results=20
$result=3Dpg_query("select autor, titulo from table
limit $limit offset $offset"); // PostgreSQL=20
// now you can display the results returned=20
//echo "OffSet ".$offset." Page# ".$pgnum."
";
echo "
";
echo "Autor | ";
echo "Titulo | ";
echo "
";
while ($row =3D pg_fetch_array($result, $i)) {
echo "";
//$arr =3D pg_fetch_array($result,($i));
for ($j=3D0; $j < count($row); $j++) {=20
/*
echo " |
";
echo "$row[$j] ";=20
echo " |
";
*/
echo "$row[$j]";
//echo " $j['autor']";=20
=09
=09
}=20
echo "";
//echo "
";=20
}=20
echo "
";
// calculate number of pages needing links=20
$pages=3Dintval($numrows/$limit);=20
// $pages now contains int of pages needed unless
there is a remainder from division=20
if ($numrows%$limit) {=20
// has remainder so add one page=20
$pages++;=20
}=20
echo "";=20
// next we need to do the links to other results=20
if (pages!=3D1)=20
{=20
if ($pgnum==1) {=20
print "
href=3D\"$PHP_SELF?offset=3D0&pgnum=3D1\">PREV
\n";=20
}=20
else=20
{=20
$prevoffset=3D$offset-20;=20
$cpgnum =3D intval($prevoffset/$limit)+1;=20
print "
href=3D\"$PHP_SELF?offset=3D$prevoffset&pgnum=3D$cpgnum\">PR EV
\n";=20
}=20
}=20
for ($i=3D1;$i<=3D$pages;$i++) { // loop thru=20
$newoffset=3D$limit*($i-1);=20
$cpgnum =3D $i;=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">$cp gnum
\n";=20
}=20
// check to see if last page=20
if ($pages!=3D1)=20
{=20
if ($pgnum<$pages) {=20
$newoffset=3D$offset+$limit;=20
$cpgnum =3D intval(($offset+$limit)/$limit)+1;=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">NEX T\n";
}=20
else=20
{=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$pages\">NEXT
\n";
}=20
}=20
// Close database.=20
pg_close($conn);=20
?>=20
--- David Busby escreveu: > Paging
with PostgreSQL is super easy!
> select * from table where (x=3Dy) offset 0 limit 30;
> Gives you the first 30 matches, then do
> select * from table where (x=3Dy) offset 30 limit
> 30;
> This will give the next 30, super easy!
>=20
> Here's a sample of how I use it in a script
>=20
> // Collect offset
> $offset =3D isset($_GET['offset'])?$_GET['offset']:0;
> // Now the links for Prev/Next
> if ($offset >=3D 30) echo "|
>
href=3D'/contents.php?".$qs."offset=3D".($offset-30)."'>Back
> 30";
> echo "|
>
href=3D'/contents.php?".$qs."offset=3D".($offset+30)."'>Next
> 30";
> // Query
> $rs =3D pg_exec($db,"select id,name from stuff order
> by name offset $offset
> limit 30;");
>=20
> /B
>=20
>=20
> ----- Original Message -----=20
> From: "Lynna Landstreet"
> To:
> Sent: Friday, August 08, 2003 13:30
> Subject: [PHP] Paging results
>=20
>=20
> > 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
> > --=20
> > Resource Centre Database Coordinator
> > Gallery 44
> > www.gallery44.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>=20
>=20
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend=20
=====3D
=C2ngelo Marcos Rigo
AMR Inform=E1tica=20
(51) 3348 0870=20
Rua Pe. Alois Kades 400/210=20
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
=20
____________________________________________________________ ___________
Conhe=E7a o novo Cad=EA? - Mais r=E1pido, mais f=E1cil e mais preciso.
Toda a web, 42 milh=F5es de p=E1ginas brasileiras e nova busca por imagen=
s!
http://www.cade.com.br
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Paging results
am 11.08.2003 19:31:48 von angelo_rigo
I have this script (please adapt the section where the
results are showed)
// Request for parameters.=20
$offset =3D $_REQUEST['offset'];=20
$pgnum =3D $_REQUEST['pgnum'];=20
// Make that they are integer - security.=20
settype($offset, 'integer');=20
settype($pgnum, 'integer');=20
// Open database, i.e PostgreSQL=20
$connuserpostgres");=20
if (!$conn) {=20
echo "An error occured.\n";=20
exit;=20
}=20
// Initialize variables.=20
$limit=3D20; // rows to return=20
$numresults=3Dpg_query("select * from table"); //
PostgreSQL=20
$numrows=3Dpg_num_rows($numresults);=20
// next determine if offset has been passed to
script, if not use 0=20
if (empty($offset)) {=20
$offset=3D0;=20
$pgnum=3D1;=20
}=20
// get results=20
$result=3Dpg_query("select autor, titulo from table
limit $limit offset $offset"); // PostgreSQL=20
// now you can display the results returned=20
//echo "OffSet ".$offset." Page# ".$pgnum."
";
echo "
";
echo "Autor | ";
echo "Titulo | ";
echo "
";
while ($row =3D pg_fetch_array($result, $i)) {
echo "";
//$arr =3D pg_fetch_array($result,($i));
for ($j=3D0; $j < count($row); $j++) {=20
/*
echo " |
";
echo "$row[$j] ";=20
echo " |
";
*/
echo "$row[$j]";
//echo " $j['autor']";=20
=09
=09
}=20
echo "";
//echo "
";=20
}=20
echo "
";
// calculate number of pages needing links=20
$pages=3Dintval($numrows/$limit);=20
// $pages now contains int of pages needed unless
there is a remainder from division=20
if ($numrows%$limit) {=20
// has remainder so add one page=20
$pages++;=20
}=20
echo "";=20
// next we need to do the links to other results=20
if (pages!=3D1)=20
{=20
if ($pgnum==1) {=20
print "
href=3D\"$PHP_SELF?offset=3D0&pgnum=3D1\">PREV
\n";=20
}=20
else=20
{=20
$prevoffset=3D$offset-20;=20
$cpgnum =3D intval($prevoffset/$limit)+1;=20
print "
href=3D\"$PHP_SELF?offset=3D$prevoffset&pgnum=3D$cpgnum\">PR EV
\n";=20
}=20
}=20
for ($i=3D1;$i<=3D$pages;$i++) { // loop thru=20
$newoffset=3D$limit*($i-1);=20
$cpgnum =3D $i;=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">$cp gnum
\n";=20
}=20
// check to see if last page=20
if ($pages!=3D1)=20
{=20
if ($pgnum<$pages) {=20
$newoffset=3D$offset+$limit;=20
$cpgnum =3D intval(($offset+$limit)/$limit)+1;=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">NEX T\n";
}=20
else=20
{=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$pages\">NEXT
\n";
}=20
}=20
// Close database.=20
pg_close($conn);=20
?>=20
--- David Busby escreveu: > Paging
with PostgreSQL is super easy!
> select * from table where (x=3Dy) offset 0 limit 30;
> Gives you the first 30 matches, then do
> select * from table where (x=3Dy) offset 30 limit
> 30;
> This will give the next 30, super easy!
>=20
> Here's a sample of how I use it in a script
>=20
> // Collect offset
> $offset =3D isset($_GET['offset'])?$_GET['offset']:0;
> // Now the links for Prev/Next
> if ($offset >=3D 30) echo "|
>
href=3D'/contents.php?".$qs."offset=3D".($offset-30)."'>Back
> 30";
> echo "|
>
href=3D'/contents.php?".$qs."offset=3D".($offset+30)."'>Next
> 30";
> // Query
> $rs =3D pg_exec($db,"select id,name from stuff order
> by name offset $offset
> limit 30;");
>=20
> /B
>=20
>=20
> ----- Original Message -----=20
> From: "Lynna Landstreet"
> To:
> Sent: Friday, August 08, 2003 13:30
> Subject: [PHP] Paging results
>=20
>=20
> > 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
> > --=20
> > Resource Centre Database Coordinator
> > Gallery 44
> > www.gallery44.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>=20
>=20
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend=20
=====3D
=C2ngelo Marcos Rigo
AMR Inform=E1tica=20
(51) 3348 0870=20
Rua Pe. Alois Kades 400/210=20
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
=20
____________________________________________________________ ___________
Conhe=E7a o novo Cad=EA? - Mais r=E1pido, mais f=E1cil e mais preciso.
Toda a web, 42 milh=F5es de p=E1ginas brasileiras e nova busca por imagen=
s!
http://www.cade.com.br
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Re: Paging results
am 11.08.2003 20:14:39 von angelo_rigo
Do you have PEAR?
There is in
http://pear.php.net/package-info.php?package=3DDB_Pager,
the dbpager package, wich works very well
--- Lynna Landstreet escreveu: >
HI there,
>=20
> 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.
>=20
> Now, a new question:
>=20
> 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?
>=20
> Alternatively, would it be difficult to adapt one of
> those to working with
> PostgreSQL instead of MySQL?
>=20
>=20
> Lynna
> --=20
> Resource Centre Database Coordinator
> Gallery 44
> www.gallery44.org
>=20
>=20
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org=20
=====3D
=C2ngelo Marcos Rigo
AMR Inform=E1tica=20
(51) 3348 0870=20
Rua Pe. Alois Kades 400/210=20
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo@yahoo.com.br
=20
____________________________________________________________ ___________
Conhe=E7a o novo Cad=EA? - Mais r=E1pido, mais f=E1cil e mais preciso.
Toda a web, 42 milh=F5es de p=E1ginas brasileiras e nova busca por imagen=
s!
http://www.cade.com.br
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Paging results
am 12.08.2003 09:05:02 von Gerd Terlutter
=C2ngelo Marcos Rigo wrote:
> Do you have PEAR?
>=20
> There is in
> http://pear.php.net/package-info.php?package=3DDB_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=20
different RDBMS, in my case PG and MySQL. nxet week i'll start with=20
transaction on MySQL3.23.51, but don't know what happends. Other=20
functionality is given during use PEAR::DB. This package supports 10 or=20
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=20
changing only the param 'dbtype' e.g. psql or mysql.
excuse my bad english,
Gerd
--=20
--------------------------------------------------------
# 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 #
--------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Re: Paging results
am 12.08.2003 09:15:37 von Gerd Terlutter
=C2ngelo Marcos Rigo wrote:
> Do you have PEAR?
>=20
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=20
variables bin_dir, doc_dir, data_dir and php_dir. Set this to your home,=20
you'll get a running pear-system.
greetings, Gerd
--=20
--------------------------------------------------------
# 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 #
--------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Re: Paging results
am 14.08.2003 01:08:26 von Lynna Landstreet
on 8/11/03 2:14 PM, =C2ngelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote:
> Do you have PEAR?
>=20
> There is in
> http://pear.php.net/package-info.php?package=3DDB_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
--=20
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Re: Paging results
am 14.08.2003 01:12:43 von Lynna Landstreet
on 8/11/03 1:31 PM, =C2ngelo 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= 3D50&pgnum=3D2 =
- the
second page displays correctly, but again the prev and next links don't work
right. The prev page here shows pgnum=3D1, 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=3D&pgnum=3D0. I've looked at the code, but I don't t=
hink
I'm following it well enough to be sure what's going wrong. I've attached a
copy of the file=20
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 =3D $_REQUEST['offset'];
$pgnum =3D $_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;=20
}=20
// Initialize variables.
$limit=3D50; // rows to return
$numresults=3Dpg_exec("SELECT COUNT(*) FROM artists"); //PostgreSQL
// $numrows=3Dpg_numrows($numresults);
// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
$offset=3D0;=20
$pgnum=3D1;=20
}=20
Then comes the display of the results, which is working fine.
Second part:
// calculate number of pages needing links
$pages=3Dintval($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++;
}=20
echo "\n
";
// next we need to do the links to other results
if (pages!=3D1)
{=20
if ($pgnum==1) {
print "
\n";=20
}=20
else=20
{=20
$prevoffset=3D$offset-20;
$cpgnum =3D intval($prevoffset/$limit)+1;
print "
href=3D\"$PHP_SELF?offset=3D$prevoffset&pgnum=3D$cpgnum\">PR EV \=
n";
}=20
}=20
for ($i=3D1;$i<=3D$pages;$i++) { // loop thru
$newoffset=3D$limit*($i-1);
$cpgnum =3D $i;
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">$cp gnum =
\n";
}=20
// check to see if last page
if ($pages!=3D1)
{=20
if ($pgnum<$pages) {
$newoffset=3D$offset+$limit;
$cpgnum =3D intval(($offset+$limit)/$limit)+1;
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$cpgnum\">NEX T
\n";
}=20
else=20
{=20
print "
href=3D\"$PHP_SELF?offset=3D$newoffset&pgnum=3D$pages\">NEXT\n";
}=20
}=20
Many thanks,
Lynna
--=20
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Paging results
am 14.08.2003 16:27:43 von Scott Marlowe
On Wed, 13 Aug 2003, Lynna Landstreet wrote:
> on 8/11/03 2:14 PM, =C2ngelo Marcos Rigo at angelo_rigo@yahoo.com.br wr=
ote:
>=20
> > Do you have PEAR?
> >=20
> > There is in
> > http://pear.php.net/package-info.php?package=3DDB_Pager,
> > the dbpager package, wich works very well
>=20
> I'd never heard of it before - I'm fairly new to PHP. I just looked thr=
ough
> the documentation a bit, but I'm a bit confused - is this something I c=
an
> 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 ow=
n
> server; we're just a on a shared hosting plan.
PEAR, and about 12 or so other packages, are usually distributed with PHP=
=20
and found in the /usr/local/lib/php directory on most Unix boxes.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster