Gary,
The other way you could do this is by using the $iStart and $iEnd in the SQL
query rather than the for loop. Like so:
if (!isset($iStart)) {
$iStart = 0;
}
if (!isset($iEnd)) {
$iEnd = $iStart + 20;
}
if (!$conn = pg_Connect("Localhost", "5432", "", "", "contacts")) {
echo "An error occurred.\n";
exit;
}
if (!$result = pg_Exec($conn, "SELECT * FROM contacts_list LIMIT $iStart,
$iEnd")) {
echo "An error occurred.\n";
exit;
}
for ($iWalk = 0; $iWalk < pg_numrows($result); $iWalk++) {
// Do your stuff
}
<a href=\"$PHP_SELF?iStart=$iEnd\">Next 20</a>
> Dan,
> This is a good technique, but it seems wasteful to me to SELECT every row
> with each web page request, then only use a chunk of them to display on
> the screen. Wouldn't it be better to control the walk within the SELECT
> statement? Or does this require some knowledge of state between web pages?
> Cheers,
> Gary
>
>
>
> dan_wilson@geocities.com writes:
> >Blake,
> >
> >Try this:
> >
> ><SCRIPT language="PHP">
> >
> >if (!isset($iStart)) {
> > $iStart = 0;
> >}
> >if (!isset($iEnd)) {
> > $iEnd = $iStart + 20;
> >}
> >
> >if (!$conn = pg_Connect("Localhost", "5432", "", "", "contacts")) {
> > echo "An error occurred.\n";
> > exit;
> >}
> >
> >if (!$result = pg_Exec($conn, "SELECT * FROM contacts_list")) {
> > echo "An error occurred.\n";
> > exit;
> >}
> >
> >for ($iWalk = $iStart; $iWalk < $iEnd; $iWalk++) {
> > $resArray = pg_fetch_array($result, $iWalk);
> > echo "
> > <TR>
> > <TD>
> > $resArray["personal_id"]
> > </TD>
> > <TD>
> > $resArray["name"]
> > </TD>
> > <TD>
> > $resArray["address"]
> > </TD>
> > <TD>
> > $resArray["city"]
> > </TD>
> > <TD>
> > $resArray["state"]
> > </TD>
> > <TD>
> > $resArray["zip_code"]
> > </TD>
> > <TD>
> > <center>
> > $resArray["phone_number"];
> > </center>
> > </TD>
> > </TR>
> > ";
> >}
> >
> >echo "<a href=\"$PHP_SELF?iStart=$iEnd\">Next 20</a>";
> >
> > pg_FreeResult($result);
> >pg_Close($conn);
> ></script>
> >
> >Hope that helps. You control everything with your for loop. And you
pass
> >the start and ending row in the URL.
> >
> >-Dan
> >
> >
> >
> >----- Original Message -----
> >From: Blake Starkenburg <blake_star@email.msn.com>
> >To: <pgsql-general@hub.org>
> >Sent: Monday, August 16, 1999 10:34 AM
> >Subject: [GENERAL] Limit postgresql query results
> >
> >
> >> Hello, I am using PostgreSQL 6.3 and PHP. I am trying to build a query
> >that
> >> only returns 20 results at a time, with a "next" link at the bottom of
> >the
> >> query results for the next 20. Someone suggested to me that I use the
> >below
> >> block of code (pg_Exec ($conn, "set QUERY_LIMIT='20'");). How would I
> >use
> >> this in the below code I'm already using to connect to and query the
DB?
> >And
> >> does anyone maybe have any better ideas of going about this task?
> >>
> >> CODE I WAS GIVEN TO USE
> >> ---------------------------------------------------------------------
> >> pg_Exec ($conn, "set QUERY_LIMIT='20'");
> >> ---------------------------------------------------------------------
> >>
> >> THE CODE I AM USING NOW
> >> ---------------------------------------------------------------------
> >> <SCRIPT language="PHP">
> >>
> >> $conn = pg_Connect("Localhost", "5432", "", "", "contacts");
> >> if (!$conn) {
> >>
> >> echo "An error occurred.\n";
> >> exit;
> >> }
> >>
> >> $result = pg_Exec($conn, "SELECT* FROM contacts_list");
> >>
> >> if (!$result) {
> >>
> >> echo "An error occurred.\n";
> >> exit;
> >> }
> >>
> >> $num = pg_NumRows($result);
> >> $i = 0;
> >>
> >> while ($i < $num) {
> >>
> >> echo "<TR><TD><font>";
> >> echo pg_Result($result, $i, "personal_id");
> >> echo "</font></TD><TD><font>";
> >> echo pg_Result($result, $i, "name");
> >> echo "</font></TD><TD><font>";
> >> echo pg_Result($result, $i, "address");
> >> echo "</font></TD><TD><font>";
> >> echo pg_Result($result, $i, "city");
> >> echo "</font></TD><TD><font>";
> >> echo pg_Result($result, $i, "state");
> >> echo "</font></TD><TD><font>";
> >> echo pg_Result($result, $i, "zip_code");
> >> echo "</font></center></TD><TD><center><font>";
> >> echo pg_Result($result, $i, "phone_number");
> >> echo "</font></center></TD></TR>";
> >> $i++;
> >> }
> >>
> >> pg_FreeResult($result);
> >> pg_Close($conn);
> >> </script>
> >> ---------------------------------------------------------------------
> >>
> >> Thank you everyone for your help
> >>
> >>
> >>
> >>
> >
> >
>
>
>
> **************************************************************************
> * Gary B. Hoffman, Computing Services Manager e-mail: ghoffman@ucsd.edu *
> * Graduate School of International Relations and Pacific Studies (IR/PS) *
> * University of California, San Diego (UCSD) voice: (858) 534-1989 *
> * 9500 Gilman Dr., La Jolla, CA 92093-0519 USA fax: (858) 534-3939 *
> **************************************************************************
>