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