Re: Re(2): [GENERAL] Limit postgresql query results - Mailing list pgsql-general

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 *
> **************************************************************************
>