Thread: 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

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