Re(2): [GENERAL] Limit postgresql query results

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(at)geocities(dot)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(at)email(dot)msn(dot)com>
>To: <pgsql-general(at)hub(dot)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(at)ucsd(dot)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 *
**************************************************************************