display query results

I am trying to learn postgresql with psql - using FreeBS D7.0,
postgresql 8.3.3 php 5.2.6, apache 2.2.9

How can I echo or print on screen the results; all I get from this code
is this:
Resource id #3
item_idglossary_idnamedescription
which is the field names run together

I am expecting to see several long text sentences.

Here is the snippet and the connection does work and the database is
there and functioning.

$db = pg_connect("host=localhost port=5432 dbname=med user=med
password=0tscc71");

if (!$db)
{
die("Could not open connection to database server");
}

// generate and execute a query
$query = "SELECT description FROM glossary_item WHERE
name='Alcohol'";
$result = pg_query($db, $query) or die("Error in query: $query.
" . pg_last_error($db));

// Print result on screen
echo "$result";

pg_close($db);

What am I doing wrong?


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

This is a multi-part message in MIME format.

------_=_NextPart_001_01C8F261.C3BEEC29
Content-Type: text/plain;
charset="iso-8859-1"
content-transfer-encoding: quoted-printable

You need to fetch the results. See the pg_fetch_* family of functions for t=
he one of your liking.

$myarray =3D pg_fetch_all($results);
print_r($myarray);

Greg

________________________________

From: pgsql-php-owner@postgresql.org on behalf of PJ
Sent: Wed 7/30/2008 10:18 AM
To: pgsql-php@postgresql.org
Subject: [PHP] display query results



I am trying to learn postgresql with psql - using FreeBS D7.0,
postgresql 8.3.3 php 5.2.6, apache 2.2.9

How can I echo or print on screen the results; all I get from this code
is this:
Resource id #3
item_idglossary_idnamedescription
which is the field names run together

I am expecting to see several long text sentences.

Here is the snippet and the connection does work and the database is
there and functioning.

$db =3D pg_connect("host=3Dlocalhost port=3D5432 dbname=3Dmed user=
=3Dmed
password=3D0tscc71");

if (!$db)
{
die("Could not open connection to database server");
}

// generate and execute a query
$query =3D "SELECT description FROM glossary_item WHERE
name=3D'Alcohol'";
$result =3D pg_query($db, $query) or die("Error in query: $query.
" . pg_last_error($db));

// Print result on screen
echo "$result";

pg_close($db);

What am I doing wrong?


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php



CONFIDENTIALITY NOTICE: This e-mail communication and any attachments may co=
ntain confidential and privileged information for the use of the designated=
recipients named above. If you are not the intended recipient, you are her=
eby notified that you have received this communication in error and that any=
review, disclosure, dissemination, distribution or copying of it or its con=
tents is prohibited. If you have received this communication in error; plea=
se notify Cranel Incorporated immediately by telephone at 614-431-8000 or 80=
0-288-3475 and destroy all copies of this communication and any attachments.=
=0A=


------_=_NextPart_001_01C8F261.C3BEEC29
Content-Type: text/html;
charset="iso-8859-1"
content-transfer-encoding: quoted-printable

=0A=
=
=0A=
=0A=
=0A=

Re: display query results

Try this:

$db = pg_connect("host=localhost port=5432 dbname=med user=med
password=0tscc71");

if (!$db)
{
die("Could not open connection to database server");
}

// generate and execute a query
$query = "SELECT description FROM glossary_item WHERE
name='Alcohol'";
$result = pg_query($db, $query) or die("Error in query: $query.
" . pg_last_error($db));

// Print result on screen
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
foreach ($line as $col_value) {
echo $col_value."
";
}

pg_close($db);


The result set is an array, You just need to loop through it. The php
manual has some nice examples that helped me get started.
--------------------------------


Matthias Ritzkowski

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

On Wednesday 30 July 2008 12:18:28 PJ wrote:
> I am trying to learn postgresql with psql - using FreeBS D7.0,
> postgresql 8.3.3 php 5.2.6, apache 2.2.9
>
> How can I echo or print on screen the results; all I get from this code
> is this:
> Resource id #3
> item_idglossary_idnamedescription
> which is the field names run together
>
> I am expecting to see several long text sentences.
>
> Here is the snippet and the connection does work and the database is
> there and functioning.
>
> $db = pg_connect("host=localhost port=5432 dbname=med user=med
> password=0tscc71");
>
> if (!$db)
> {
> die("Could not open connection to database server");
> }
>
> // generate and execute a query
> $query = "SELECT description FROM glossary_item WHERE
> name='Alcohol'";
> $result = pg_query($db, $query) or die("Error in query: $query.
> " . pg_last_error($db));
>
> // Print result on screen
> echo "$result";
>
> pg_close($db);
>
> What am I doing wrong?

pg_query just gives you a pointer to the result set, to get the actual
information in the result, you need to use one of the other pg_ functions to
grab that. ie. http://us.php.net/manual/en/function.pg-fetch-result.php (and
about a dozen others)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

I've been looking at the pg_fetch_* functions but do not understand one
little bit.
Using your suggestion gives a blank screen but var_dump($myarray) gives
"bool(false)"
And a var_dump($results) returns "resource(3) of type (pgsql result)
while print_r($results) returns "Resource id#3"
I'm totally lost. :(
Perhaps my query isn't well formulated?
What I am trying to locate (or print out) is the string found the
"description" column whre the row in the "name" column is Alcohol.
item_id || glossary_id || name || description
2 || 2 || Alcohol || One of thetwo major....
blah...blah.. blah

Spiegelberg, Greg wrote:
> You need to fetch the results. See the pg_fetch_* family of functions
> for the one of your liking.
>
> $myarray = pg_fetch_all($results);
> print_r($myarray);
>
> Greg
>
> ------------------------------------------------------------ ------------
> *From:* pgsql-php-owner@postgresql.org on behalf of PJ
> *Sent:* Wed 7/30/2008 10:18 AM
> *To:* pgsql-php@postgresql.org
> *Subject:* [PHP] display query results
>
> I am trying to learn postgresql with psql - using FreeBS D7.0,
> postgresql 8.3.3 php 5.2.6, apache 2.2.9
>
> How can I echo or print on screen the results; all I get from this code
> is this:
> Resource id #3
> item_idglossary_idnamedescription
> which is the field names run together
>
> I am expecting to see several long text sentences.
>
> Here is the snippet and the connection does work and the database is
> there and functioning.
>
> $db = pg_connect("host=localhost port=5432 dbname=med user=med
> password=0tscc71");
>
> if (!$db)
> {
> die("Could not open connection to database server");
> }
>
> // generate and execute a query
> $query = "SELECT description FROM glossary_item WHERE
> name='Alcohol'";
> $result = pg_query($db, $query) or die("Error in query: $query.
> " . pg_last_error($db));
>
> // Print result on screen
> echo "$result";
>
> pg_close($db);
>
> What am I doing wrong?
>

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

Doesn't work... and I feel totally lost. I don't understand the
pg_fetch_* stuff at all.
One would think that my query should return the text that is in the
field. The database is quite correct and does work with php since it
comes from an older web-site that someone else programmed for me. I'm
using it to practice and learn. :)

Matthias Ritzkowski wrote:
> Try this:
>
> $db = pg_connect("host=localhost port=5432 dbname=med user=med
> password=0tscc71");
>
> if (!$db)
> {
> die("Could not open connection to database server");
> }
>
> // generate and execute a query
> $query = "SELECT description FROM glossary_item WHERE
> name='Alcohol'";
> $result = pg_query($db, $query) or die("Error in query: $query.
> " . pg_last_error($db));
>
> // Print result on screen
> while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
> foreach ($line as $col_value) {
> echo $col_value."
";
> }
>
> pg_close($db);
>
>
> The result set is an array, You just need to loop through it. The php
> manual has some nice examples that helped me get started.
> --------------------------------
>
>
> Matthias Ritzkowski
>
>


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

PJ wrote:
> I've been looking at the pg_fetch_* functions but do not understand one
> little bit.
> Using your suggestion gives a blank screen but var_dump($myarray) gives
> "bool(false)"
> And a var_dump($results) returns "resource(3) of type (pgsql result)
> while print_r($results) returns "Resource id#3"
> I'm totally lost. :(
> Perhaps my query isn't well formulated?
> What I am trying to locate (or print out) is the string found the
> "description" column whre the row in the "name" column is Alcohol.
> item_id || glossary_id || name || description
> 2 || 2 || Alcohol || One of thetwo major....
> blah...blah.. blah

You can't print the results of a query directly - the format they are
retrieved in is not just a regular PHP array. That's what the various
"fetch" options do - they pull the data out of the resultset into a
regular variable (single or array, depending on which one you use) so
that you can print it or otherwise treat it as a normal variable.

pg_fetch_result fetches a single value (string, integer, whatever it
might be) and stores it in a variable. You have to tell it exactly which
row and column from the result set you want. If you're only after one
value, use this.

pg_fetch_array and pg_fetch_assoc each fetch a whole row of data, as an
array. The different between them is that with the first one, the array
keys are just numbers, and with the second, it's an associative array
where the keys are the names of the columns in your database. If you've
only retrieved one row, but need more than one value from that row, use
one of these.

pg_fetch_all fetches *all* the rows from your result set, as a
multidimensional array. If your query is likely to have retrieved
multiple rows, use this.

Does that help?


Lynna

--
Spider Silk Design - web design and development
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Cell 416.873.9289
http://www.spidersilk.net


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

On 30/07/2008 18:48, PJ wrote:
> Doesn't work... and I feel totally lost. I don't understand the
> pg_fetch_* stuff at all.

Here's what you need to do:

// Open a connection to the database.
$conn = pg_connect($your_connection_string);

// Execute a query which returns some rows.
$result = pg_query($conn, 'select foo, bar from your_table');

// Loop through the rows one by one.
// pg_fetch_array() returns the row as an array with a numeric index,
// while pg_fetch_assoc() returns the row as an associative array
// keyed on the column names. I usually use the latter. Both return
// false when no more rows are available, so this is the usual idiom:
while ($row = pg_fetch_assoc($result))
{
print 'Some values: ' . $row['foo'] . ', ' . $row['bar'] . "\n";
}

The other pg_fetch_* functions do various other things, such as (for
example) fetching a value from a particular column in a particular row.

HTH,

Ray.



------------------------------------------------------------ ------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------ ------

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

Lynna Landstreet wrote:
> PJ wrote:
>> I've been looking at the pg_fetch_* functions but do not understand
>> one little bit.
>> Using your suggestion gives a blank screen but var_dump($myarray)
>> gives "bool(false)"
>> And a var_dump($results) returns "resource(3) of type (pgsql result)
>> while print_r($results) returns "Resource id#3"
>> I'm totally lost. :(
>> Perhaps my query isn't well formulated?
>> What I am trying to locate (or print out) is the string found the
>> "description" column whre the row in the "name" column is Alcohol.
>> item_id || glossary_id || name || description
>> 2 || 2 || Alcohol || One of thetwo
>> major.... blah...blah.. blah
>
> You can't print the results of a query directly - the format they are
> retrieved in is not just a regular PHP array. That's what the various
> "fetch" options do - they pull the data out of the resultset into a
> regular variable (single or array, depending on which one you use) so
> that you can print it or otherwise treat it as a normal variable.
>
> pg_fetch_result fetches a single value (string, integer, whatever it
> might be) and stores it in a variable. You have to tell it exactly
> which row and column from the result set you want. If you're only
> after one value, use this.
>
> pg_fetch_array and pg_fetch_assoc each fetch a whole row of data, as
> an array. The different between them is that with the first one, the
> array keys are just numbers, and with the second, it's an associative
> array where the keys are the names of the columns in your database. If
> you've only retrieved one row, but need more than one value from that
> row, use one of these.
>
> pg_fetch_all fetches *all* the rows from your result set, as a
> multidimensional array. If your query is likely to have retrieved
> multiple rows, use this.
>
> Does that help?
Well, it does explain things a little. Unfortunately, I have tried about
everything imaginable before posting except the right thing.
I can not visualize what it is that my query is returning. Here is what
the code is:

$db = pg_connect("host=localhost port=5432 dbname=med user=med
password=0tscc71");

if (!$db)
{
die("Could not open connection to database server");
}

// generate and execute a query
$query = "SELECT description FROM glossary_item WHERE
name='Alcohol'";
$results = pg_query($db, $query) or die("Error in query: $query.
" . pg_last_error($db));
var_dump ($results);
$val = pg_fetch_result($results, 1, 3);
echo $val, "\n";
pg_close($db);
?>

Whatever I enter as values for pg_fetch_result, the screen output is :

resource(3) or type (pgsql result)
*Warning*: pg_fetch_result() [function.pg-fetch-result
]: Unable to jump to row 1 on
PostgreSQL result index 3 in
*/usr/local/www/apache22/data/k2/test1_db.php* on line *29*

I don't understand what $resuts is returning - if it is an entire row,
the one that the field is in that I am looking for, then why do I not
get a printout of the text that is in that field? The row in the table
is the second row and the field I am trying to retrieve is the 4th field.
Am I querying correctly? The table is "glossary_item", the row I want is
the one that is unique in containing the word "Alcohol" in the column
"name"

I changed: $query = "SELECT * FROM glossary_item WHERE name= 'Alcohol'";
same result

Picture me tearing out my hair...

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

PJ wrote:
> Lynna Landstreet wrote:
> Well, it does explain things a little. Unfortunately, I have tried
> about everything imaginable before posting except the right thing.
> I can not visualize what it is that my query is returning. Here is
> what the code is:
>
> Whatever I enter as values for pg_fetch_result, the screen output is :
>
> resource(3) or type (pgsql result)
> *Warning*: pg_fetch_result() [function.pg-fetch-result
> ]: Unable to jump to row 1
> on PostgreSQL result index 3 in
> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*

This suggests that there is no row 1 in your result-set. I believe it
is zero-based, so try fetching row 0 if your query only returns 1 row.

> I don't understand what $resuts is returning - if it is an entire row,
> the one that the field is in that I am looking for, then why do I not
> get a printout of the text that is in that field? The row in the table
> is the second row and the field I am trying to retrieve is the 4th field.
$results as explained previously is just a pointer to a recordset. This
analogy isn't brilliant, but think of your database table as a book.
Each row on a page within that book is a record, and the words in that
row are the data in the table's columns.

When you run a query, think of yourself looking at the book's index for
a given word. The index will tell you the pages that word is on.
That's your $results - simply a pointer to your data.

You then need to turn to that page in the book (pg_fetch_* functions) to
start examining the lines for the word you want. Once you've got your
line, you can find the word (column/data, from your array) you're
looking for.

Now turn that into PHP and SQL. You run your query (looking in the
book's index) and the PostgreSQL driver will save the results into a
block of memory in your server's RAM, returning a resource identifier.
This is literally just saying "resource #3 is located at this location
in the computer's memory." When you look up a record from that
result-set, PHP then knows where to look for the data.

I never really use the "or die" syntax, I tend to explicitly check the
return values of the functions. Try this:

$db = pg_connect("host=localhost port=5432 dbname=med user=med
password=0tscc71");

// Note: according to
http://uk2.php.net/manual/en/language.types.boolean.php a resource
always evaluates to true,
// therefore !$db may not evaluate to false when connection fails.
if ($db === false)
{
die("Could not open connection to database server");
}

// generate and execute a query
$query = "SELECT description FROM glossary_item WHERE
name='Alcohol'";
$results = pg_query($db, $query);
var_dump ($results);

if ($results === false)
{
die("SQL query failed: " . pg_last_error($db));
}
else if (pg_num_rows($results) == 0)
{
// Only do this if you were expecting at least 1 row back
die("SQL query returned no rows");
}

$results_formatted = pg_fetch_all($results);
echo "

"; // need this to show output better in a HTML page

       var_dump($results_formatted);

       echo "

Re: display query results

Annotated within text below:

Andy Shellam wrote:
> PJ wrote:
>> Lynna Landstreet wrote:
>> Well, it does explain things a little. Unfortunately, I have tried
>> about everything imaginable before posting except the right thing.
>> I can not visualize what it is that my query is returning. Here is
>> what the code is:
>>
>> Whatever I enter as values for pg_fetch_result, the screen output is :
>>
>> resource(3) or type (pgsql result)
>> *Warning*: pg_fetch_result() [function.pg-fetch-result
>> ]: Unable to jump to row 1
>> on PostgreSQL result index 3 in
>> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
>
> This suggests that there is no row 1 in your result-set. I believe it
> is zero-based, so try fetching row 0 if your query only returns 1 row.
Been there, done that. No change.
>
>
>> I don't understand what $resuts is returning - if it is an entire
>> row, the one that the field is in that I am looking for, then why do
>> I not get a printout of the text that is in that field? The row in
>> the table is the second row and the field I am trying to retrieve is
>> the 4th field.
> $results as explained previously is just a pointer to a recordset.
> This analogy isn't brilliant, but think of your database table as a
> book. Each row on a page within that book is a record, and the words
> in that row are the data in the table's columns.
>
> When you run a query, think of yourself looking at the book's index
> for a given word. The index will tell you the pages that word is on.
> That's your $results - simply a pointer to your data.
>
> You then need to turn to that page in the book (pg_fetch_* functions)
> to start examining the lines for the word you want. Once you've got
> your line, you can find the word (column/data, from your array) you're
> looking for.
>
> Now turn that into PHP and SQL. You run your query (looking in the
> book's index) and the PostgreSQL driver will save the results into a
> block of memory in your server's RAM, returning a resource
> identifier. This is literally just saying "resource #3 is located at
> this location in the computer's memory." When you look up a record
> from that result-set, PHP then knows where to look for the data.
>
> I never really use the "or die" syntax, I tend to explicitly check the
> return values of the functions. Try this:
>
> > $db = pg_connect("host=localhost port=5432 dbname=med user=med
> password=0tscc71");
>
> // Note: according to
> http://uk2.php.net/manual/en/language.types.boolean.php a resource
> always evaluates to true,
> // therefore !$db may not evaluate to false when connection fails.
> if ($db === false)
> {
> die("Could not open connection to database server");
> }
>
> // generate and execute a query
> $query = "SELECT description FROM glossary_item WHERE
> name='Alcohol'";
> $results = pg_query($db, $query);
> var_dump ($results);
>
> if ($results === false)
> {
> die("SQL query failed: " . pg_last_error($db));
> }
> else if (pg_num_rows($results) == 0)
> {
> // Only do this if you were expecting at least 1 row back
> die("SQL query returned no rows");
> }
>
> $results_formatted = pg_fetch_all($results);
> echo "

"; // need this to show output better in a HTML page

>       var_dump($results_formatted);

>       echo "

Re: display query results

Just shooting in the dark here, but I'm thinking there might be some
extra spaces around the column values. For some reason PostgreSQL is
not returning any rows for that query, which is where the root of your
problem lies.

Do you have PgAdmin? If so try the exact same query against the same
database and server.
Also try changing your query as follows:

SELECT description FROM glossary_item WHERE name LIKE '%Alcohol%'

and see what you get.

Regards,

Andy


PJ wrote:
> Annotated within text below:
>
> Andy Shellam wrote:
>> PJ wrote:
>>> Lynna Landstreet wrote:
>>> Well, it does explain things a little. Unfortunately, I have tried
>>> about everything imaginable before posting except the right thing.
>>> I can not visualize what it is that my query is returning. Here is
>>> what the code is:
>>>
>>> Whatever I enter as values for pg_fetch_result, the screen output is :
>>>
>>> resource(3) or type (pgsql result)
>>> *Warning*: pg_fetch_result() [function.pg-fetch-result
>>> ]: Unable to jump to row
>>> 1 on PostgreSQL result index 3 in
>>> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
>>
>> This suggests that there is no row 1 in your result-set. I believe
>> it is zero-based, so try fetching row 0 if your query only returns 1
>> row.
> Been there, done that. No change.
>>
>>
>>> I don't understand what $resuts is returning - if it is an entire
>>> row, the one that the field is in that I am looking for, then why do
>>> I not get a printout of the text that is in that field? The row in
>>> the table is the second row and the field I am trying to retrieve is
>>> the 4th field.
>> $results as explained previously is just a pointer to a recordset.
>> This analogy isn't brilliant, but think of your database table as a
>> book. Each row on a page within that book is a record, and the words
>> in that row are the data in the table's columns.
>>
>> When you run a query, think of yourself looking at the book's index
>> for a given word. The index will tell you the pages that word is
>> on. That's your $results - simply a pointer to your data.
>>
>> You then need to turn to that page in the book (pg_fetch_* functions)
>> to start examining the lines for the word you want. Once you've got
>> your line, you can find the word (column/data, from your array)
>> you're looking for.
>>
>> Now turn that into PHP and SQL. You run your query (looking in the
>> book's index) and the PostgreSQL driver will save the results into a
>> block of memory in your server's RAM, returning a resource
>> identifier. This is literally just saying "resource #3 is located at
>> this location in the computer's memory." When you look up a record
>> from that result-set, PHP then knows where to look for the data.
>>
>> I never really use the "or die" syntax, I tend to explicitly check
>> the return values of the functions. Try this:
>>
>> >> $db = pg_connect("host=localhost port=5432 dbname=med user=med
>> password=0tscc71");
>>
>> // Note: according to
>> http://uk2.php.net/manual/en/language.types.boolean.php a resource
>> always evaluates to true,
>> // therefore !$db may not evaluate to false when connection fails.
>> if ($db === false)
>> {
>> die("Could not open connection to database server");
>> }
>>
>> // generate and execute a query
>> $query = "SELECT description FROM glossary_item WHERE
>> name='Alcohol'";
>> $results = pg_query($db, $query);
>> var_dump ($results);
>>
>> if ($results === false)
>> {
>> die("SQL query failed: " . pg_last_error($db));
>> }
>> else if (pg_num_rows($results) == 0)
>> {
>> // Only do this if you were expecting at least 1 row back
>> die("SQL query returned no rows");
>> }
>>
>> $results_formatted = pg_fetch_all($results);
>> echo "

"; // need this to show output better in a HTML page

>>       var_dump($results_formatted);

>>       echo "

Re: display query results

See, you're working with a database - you execute a query but the data
(result) is still in the database. The '$result' variable represents
just a link to the data in the database - that's why it prints 'Resource
id #3'. That's almost the same as the '$connection' variable - I guess
you don't expect this to print all the data in the database:


$connection = pg_connect('...');
echo $connection;

?>

So you have to fetch the data from resultset from the database process
into PHP, and that's what pg_fetch_* functions are for. Each time you
call pg_fetch_array($result) it fetches and returns the next row (or
FALSE if there are no more rows).

The rows returned by pg_fetch_array are associative arrays, keys being
the field names.

So something like this should work:


$connection = pg_connect('...');
$result = pg_query('SELECT field_a, field_b FROM my_table');

while ($row = pg_fetch_array($result)) {

echo 'field a: ', $row['field_a'],"\n";
echo 'field b: ', $row['field_b'],"\n\n";

}

?>

If it prints nothing, then the resultset is empty - try to run the same
query pro psql or some other SQL interface.

Tomas


> Doesn't work... and I feel totally lost. I don't understand the
> pg_fetch_* stuff at all.
> One would think that my query should return the text that is in the
> field. The database is quite correct and does work with php since it
> comes from an older web-site that someone else programmed for me. I'm
> using it to practice and learn. :)
>
> Matthias Ritzkowski wrote:
>> Try this:
>>
>> $db = pg_connect("host=localhost port=5432 dbname=med user=med
>> password=0tscc71");
>>
>> if (!$db)
>> {
>> die("Could not open connection to database server");
>> }
>>
>> // generate and execute a query
>> $query = "SELECT description FROM glossary_item WHERE
>> name='Alcohol'";
>> $result = pg_query($db, $query) or die("Error in query: $query.
>> " . pg_last_error($db));
>>
>> // Print result on screen
>> while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
>> foreach ($line as $col_value) {
>> echo $col_value."
";
>> }
>>
>> pg_close($db);
>>
>>
>> The result set is an array, You just need to loop through it. The php
>> manual has some nice examples that helped me get started.
>> --------------------------------
>>
>>
>> Matthias Ritzkowski
>>
>>
>
>


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

> Well, it does explain things a little. Unfortunately, I have tried about
> everything imaginable before posting except the right thing.
> I can not visualize what it is that my query is returning. Here is what
> the code is:
>
> > $db = pg_connect("host=localhost port=5432 dbname=med user=med
> password=0tscc71");
>
> if (!$db)
> {
> die("Could not open connection to database server");
> }
>
> // generate and execute a query
> $query = "SELECT description FROM glossary_item WHERE
> name='Alcohol'";
> $results = pg_query($db, $query) or die("Error in query: $query.
> " . pg_last_error($db));
> var_dump ($results);
> $val = pg_fetch_result($results, 1, 3);
> echo $val, "\n";
> pg_close($db);
> ?>
>
> Whatever I enter as values for pg_fetch_result, the screen output is :
>
> resource(3) or type (pgsql result)
> *Warning*: pg_fetch_result() [function.pg-fetch-result
> ]: Unable to jump to row 1 on
> PostgreSQL result index 3 in
> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
> I don't understand what $resuts is returning - if it is an entire row,
> the one that the field is in that I am looking for, then why do I not
> get a printout of the text that is in that field? The row in the table
> is the second row and the field I am trying to retrieve is the 4th field.
> Am I querying correctly? The table is "glossary_item", the row I want is
> the one that is unique in containing the word "Alcohol" in the column
> "name"
>
> I changed: $query = "SELECT * FROM glossary_item WHERE name= 'Alcohol'";
> same result
>
> Picture me tearing out my hair...

Have you tried running the query from psql (or any other SQL interface)?
This warning means there are no rows in the result, so it can't skip to
the second row in it (the first row has index 0).

Tomas

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

I want to thank everyone for their contribution. As usual, the solution
turns out to be simple. Remeber the KISS principle.
see the note within the text: the LIKE condition did it. But I will have
more questions as I grope further in the dark. :))

Andy Shellam wrote:
> Just shooting in the dark here, but I'm thinking there might be some
> extra spaces around the column values. For some reason PostgreSQL is
> not returning any rows for that query, which is where the root of your
> problem lies.
>
> Do you have PgAdmin? If so try the exact same query against the same
> database and server.
> Also try changing your query as follows:
>
> SELECT description FROM glossary_item WHERE name LIKE '%Alcohol%'
Great aim for shooting in the dark. :)) That was it.
Now, could someone explain why I need the LIKE statement?
I will try to check the documentation...
>
> and see what you get.
>
> Regards,
>
> Andy
>
>
> PJ wrote:
>> Annotated within text below:
>>
>> Andy Shellam wrote:
>>> PJ wrote:
>>>> Lynna Landstreet wrote:
>>>> Well, it does explain things a little. Unfortunately, I have tried
>>>> about everything imaginable before posting except the right thing.
>>>> I can not visualize what it is that my query is returning. Here is
>>>> what the code is:
>>>>
>>>> Whatever I enter as values for pg_fetch_result, the screen output
>>>> is :
>>>>
>>>> resource(3) or type (pgsql result)
>>>> *Warning*: pg_fetch_result() [function.pg-fetch-result
>>>> ]: Unable to jump to row
>>>> 1 on PostgreSQL result index 3 in
>>>> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
>>>
>>> This suggests that there is no row 1 in your result-set. I believe
>>> it is zero-based, so try fetching row 0 if your query only returns 1
>>> row.
>> Been there, done that. No change.
>>>
>>>
>>>> I don't understand what $resuts is returning - if it is an entire
>>>> row, the one that the field is in that I am looking for, then why
>>>> do I not get a printout of the text that is in that field? The row
>>>> in the table is the second row and the field I am trying to
>>>> retrieve is the 4th field.
>>> $results as explained previously is just a pointer to a recordset.
>>> This analogy isn't brilliant, but think of your database table as a
>>> book. Each row on a page within that book is a record, and the
>>> words in that row are the data in the table's columns.
>>>
>>> When you run a query, think of yourself looking at the book's index
>>> for a given word. The index will tell you the pages that word is
>>> on. That's your $results - simply a pointer to your data.
>>>
>>> You then need to turn to that page in the book (pg_fetch_*
>>> functions) to start examining the lines for the word you want. Once
>>> you've got your line, you can find the word (column/data, from your
>>> array) you're looking for.
>>>
>>> Now turn that into PHP and SQL. You run your query (looking in the
>>> book's index) and the PostgreSQL driver will save the results into a
>>> block of memory in your server's RAM, returning a resource
>>> identifier. This is literally just saying "resource #3 is located
>>> at this location in the computer's memory." When you look up a
>>> record from that result-set, PHP then knows where to look for the data.
>>>
>>> I never really use the "or die" syntax, I tend to explicitly check
>>> the return values of the functions. Try this:
>>>
>>> >>> $db = pg_connect("host=localhost port=5432 dbname=med user=med
>>> password=0tscc71");
>>>
>>> // Note: according to
>>> http://uk2.php.net/manual/en/language.types.boolean.php a resource
>>> always evaluates to true,
>>> // therefore !$db may not evaluate to false when connection
>>> fails.
>>> if ($db === false)
>>> {
>>> die("Could not open connection to database server");
>>> }
>>>
>>> // generate and execute a query
>>> $query = "SELECT description FROM glossary_item WHERE
>>> name='Alcohol'";
>>> $results = pg_query($db, $query);
>>> var_dump ($results);
>>>
>>> if ($results === false)
>>> {
>>> die("SQL query failed: " . pg_last_error($db));
>>> }
>>> else if (pg_num_rows($results) == 0)
>>> {
>>> // Only do this if you were expecting at least 1 row back
>>> die("SQL query returned no rows");
>>> }
>>>
>>> $results_formatted = pg_fetch_all($results);
>>> echo "

"; // need this to show output better in a HTML page

>>>       var_dump($results_formatted);

>>>       echo "

Re: display query results

Haha, no worries, I've had the same issue before.
It's almost certain that the text in your column does not exactly match
"Alcohol" for one of a few reasons, e.g.

"Alcohol " (right-padding)
" Alcohol" (left-padding)
" Alcohol " (padded both sides)

will not match = 'Alcohol' in the query.

Try trimming the data in that field - from PgAdmin or some other query
tool, run something like:

|UPDATE glossary_item SET "name" = |trim(both ' ' from "name")

WARNING: I've not tried the above query so make sure you have a backup
of your data before you run it ;-)
Then try changing your PHP query back to = again.

Andy

PJ wrote:
> I want to thank everyone for their contribution. As usual, the
> solution turns out to be simple. Remeber the KISS principle.
> see the note within the text: the LIKE condition did it. But I will
> have more questions as I grope further in the dark. :))
>
> Andy Shellam wrote:
>> Just shooting in the dark here, but I'm thinking there might be some
>> extra spaces around the column values. For some reason PostgreSQL is
>> not returning any rows for that query, which is where the root of
>> your problem lies.
>>
>> Do you have PgAdmin? If so try the exact same query against the same
>> database and server.
>> Also try changing your query as follows:
>>
>> SELECT description FROM glossary_item WHERE name LIKE '%Alcohol%'
> Great aim for shooting in the dark. :)) That was it.
> Now, could someone explain why I need the LIKE statement?
> I will try to check the documentation...
>>
>> and see what you get.
>>
>> Regards,
>>
>> Andy
>>
>>
>> PJ wrote:
>>> Annotated within text below:
>>>
>>> Andy Shellam wrote:
>>>> PJ wrote:
>>>>> Lynna Landstreet wrote:
>>>>> Well, it does explain things a little. Unfortunately, I have tried
>>>>> about everything imaginable before posting except the right thing.
>>>>> I can not visualize what it is that my query is returning. Here is
>>>>> what the code is:
>>>>>
>>>>> Whatever I enter as values for pg_fetch_result, the screen output
>>>>> is :
>>>>>
>>>>> resource(3) or type (pgsql result)
>>>>> *Warning*: pg_fetch_result() [function.pg-fetch-result
>>>>> ]: Unable to jump to
>>>>> row 1 on PostgreSQL result index 3 in
>>>>> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
>>>>
>>>> This suggests that there is no row 1 in your result-set. I believe
>>>> it is zero-based, so try fetching row 0 if your query only returns
>>>> 1 row.
>>> Been there, done that. No change.
>>>>
>>>>
>>>>> I don't understand what $resuts is returning - if it is an entire
>>>>> row, the one that the field is in that I am looking for, then why
>>>>> do I not get a printout of the text that is in that field? The row
>>>>> in the table is the second row and the field I am trying to
>>>>> retrieve is the 4th field.
>>>> $results as explained previously is just a pointer to a recordset.
>>>> This analogy isn't brilliant, but think of your database table as a
>>>> book. Each row on a page within that book is a record, and the
>>>> words in that row are the data in the table's columns.
>>>>
>>>> When you run a query, think of yourself looking at the book's index
>>>> for a given word. The index will tell you the pages that word is
>>>> on. That's your $results - simply a pointer to your data.
>>>>
>>>> You then need to turn to that page in the book (pg_fetch_*
>>>> functions) to start examining the lines for the word you want.
>>>> Once you've got your line, you can find the word (column/data, from
>>>> your array) you're looking for.
>>>>
>>>> Now turn that into PHP and SQL. You run your query (looking in the
>>>> book's index) and the PostgreSQL driver will save the results into
>>>> a block of memory in your server's RAM, returning a resource
>>>> identifier. This is literally just saying "resource #3 is located
>>>> at this location in the computer's memory." When you look up a
>>>> record from that result-set, PHP then knows where to look for the
>>>> data.
>>>>
>>>> I never really use the "or die" syntax, I tend to explicitly check
>>>> the return values of the functions. Try this:
>>>>
>>>> >>>> $db = pg_connect("host=localhost port=5432 dbname=med user=med
>>>> password=0tscc71");
>>>>
>>>> // Note: according to
>>>> http://uk2.php.net/manual/en/language.types.boolean.php a resource
>>>> always evaluates to true,
>>>> // therefore !$db may not evaluate to false when connection
>>>> fails.
>>>> if ($db === false)
>>>> {
>>>> die("Could not open connection to database server");
>>>> }
>>>>
>>>> // generate and execute a query
>>>> $query = "SELECT description FROM glossary_item WHERE
>>>> name='Alcohol'";
>>>> $results = pg_query($db, $query);
>>>> var_dump ($results);
>>>>
>>>> if ($results === false)
>>>> {
>>>> die("SQL query failed: " . pg_last_error($db));
>>>> }
>>>> else if (pg_num_rows($results) == 0)
>>>> {
>>>> // Only do this if you were expecting at least 1 row back
>>>> die("SQL query returned no rows");
>>>> }
>>>>
>>>> $results_formatted = pg_fetch_all($results);
>>>> echo "

"; // need this to show output better in a HTML page

>>>>       var_dump($results_formatted);

>>>>       echo "

Re: display query results

I knew it was too good to be true.
Now I am trying to verify the padding on "Alcohol" and in so doing tried
to invoke phpPgAdmin... and you gussed it, I tried to follow all hte
INSTALL instructions and http://localhost/phppgadmin just gives "cannot
find on this server"
On to of it, the same script I ran yesterday now give me "Fatal error"
Call to undefined function pg_connect() on line 10. Worked fine yesterday.
Wonder what it is that I screwed up?

Andy Shellam wrote:
> Haha, no worries, I've had the same issue before.
> It's almost certain that the text in your column does not exactly
> match "Alcohol" for one of a few reasons, e.g.
>
> "Alcohol " (right-padding)
> " Alcohol" (left-padding)
> " Alcohol " (padded both sides)
>
> will not match = 'Alcohol' in the query.
> Try trimming the data in that field - from PgAdmin or some other query
> tool, run something like:
>
> |UPDATE glossary_item SET "name" = |trim(both ' ' from "name")
>
> WARNING: I've not tried the above query so make sure you have a backup
> of your data before you run it ;-)
> Then try changing your PHP query back to = again.
>
> Andy
>
> PJ wrote:
>> I want to thank everyone for their contribution. As usual, the
>> solution turns out to be simple. Remeber the KISS principle.
>> see the note within the text: the LIKE condition did it. But I will
>> have more questions as I grope further in the dark. :))
>>
>> Andy Shellam wrote:
>>> Just shooting in the dark here, but I'm thinking there might be some
>>> extra spaces around the column values. For some reason PostgreSQL
>>> is not returning any rows for that query, which is where the root of
>>> your problem lies.
>>>
>>> Do you have PgAdmin? If so try the exact same query against the
>>> same database and server.
>>> Also try changing your query as follows:
>>>
>>> SELECT description FROM glossary_item WHERE name LIKE '%Alcohol%'
>> Great aim for shooting in the dark. :)) That was it.
>> Now, could someone explain why I need the LIKE statement?
>> I will try to check the documentation...
>>>
>>> and see what you get.
>>>
>>> Regards,
>>>
>>> Andy
>>>
>>>
>>> PJ wrote:
>>>> Annotated within text below:
>>>>
>>>> Andy Shellam wrote:
>>>>> PJ wrote:
>>>>>> Lynna Landstreet wrote:
>>>>>> Well, it does explain things a little. Unfortunately, I have
>>>>>> tried about everything imaginable before posting except the right
>>>>>> thing.
>>>>>> I can not visualize what it is that my query is returning. Here
>>>>>> is what the code is:
>>>>>>
>>>>>> Whatever I enter as values for pg_fetch_result, the screen
>>>>>> output is :
>>>>>>
>>>>>> resource(3) or type (pgsql result)
>>>>>> *Warning*: pg_fetch_result() [function.pg-fetch-result
>>>>>> ]: Unable to jump to
>>>>>> row 1 on PostgreSQL result index 3 in
>>>>>> */usr/local/www/apache22/data/k2/test1_db.php* on line *29*
>>>>>
>>>>> This suggests that there is no row 1 in your result-set. I
>>>>> believe it is zero-based, so try fetching row 0 if your query only
>>>>> returns 1 row.
>>>> Been there, done that. No change.
>>>>>
>>>>>
>>>>>> I don't understand what $resuts is returning - if it is an entire
>>>>>> row, the one that the field is in that I am looking for, then why
>>>>>> do I not get a printout of the text that is in that field? The
>>>>>> row in the table is the second row and the field I am trying to
>>>>>> retrieve is the 4th field.
>>>>> $results as explained previously is just a pointer to a
>>>>> recordset. This analogy isn't brilliant, but think of your
>>>>> database table as a book. Each row on a page within that book is
>>>>> a record, and the words in that row are the data in the table's
>>>>> columns.
>>>>>
>>>>> When you run a query, think of yourself looking at the book's
>>>>> index for a given word. The index will tell you the pages that
>>>>> word is on. That's your $results - simply a pointer to your data.
>>>>>
>>>>> You then need to turn to that page in the book (pg_fetch_*
>>>>> functions) to start examining the lines for the word you want.
>>>>> Once you've got your line, you can find the word (column/data,
>>>>> from your array) you're looking for.
>>>>>
>>>>> Now turn that into PHP and SQL. You run your query (looking in
>>>>> the book's index) and the PostgreSQL driver will save the results
>>>>> into a block of memory in your server's RAM, returning a resource
>>>>> identifier. This is literally just saying "resource #3 is located
>>>>> at this location in the computer's memory." When you look up a
>>>>> record from that result-set, PHP then knows where to look for the
>>>>> data.
>>>>>
>>>>> I never really use the "or die" syntax, I tend to explicitly check
>>>>> the return values of the functions. Try this:
>>>>>
>>>>> >>>>> $db = pg_connect("host=localhost port=5432 dbname=med user=med
>>>>> password=0tscc71");
>>>>>
>>>>> // Note: according to
>>>>> http://uk2.php.net/manual/en/language.types.boolean.php a resource
>>>>> always evaluates to true,
>>>>> // therefore !$db may not evaluate to false when connection
>>>>> fails.
>>>>> if ($db === false)
>>>>> {
>>>>> die("Could not open connection to database server");
>>>>> }
>>>>>
>>>>> // generate and execute a query
>>>>> $query = "SELECT description FROM glossary_item WHERE
>>>>> name='Alcohol'";
>>>>> $results = pg_query($db, $query);
>>>>> var_dump ($results);
>>>>>
>>>>> if ($results === false)
>>>>> {
>>>>> die("SQL query failed: " . pg_last_error($db));
>>>>> }
>>>>> else if (pg_num_rows($results) == 0)
>>>>> {
>>>>> // Only do this if you were expecting at least 1 row back
>>>>> die("SQL query returned no rows");
>>>>> }
>>>>>
>>>>> $results_formatted = pg_fetch_all($results);
>>>>> echo "

"; // need this to show output better in a HTML page

>>>>>       var_dump($results_formatted);

>>>>>       echo "

Re: display query results

On 31/07/2008 14:55, PJ wrote:
> Now I am trying to verify the padding on "Alcohol" and in so doing tried
> to invoke phpPgAdmin... and you gussed it, I tried to follow all hte
> INSTALL instructions and http://localhost/phppgadmin just gives "cannot
> find on this server"

What web server are you running? What platform? How did you set it up?

> On to of it, the same script I ran yesterday now give me "Fatal error"
> Call to undefined function pg_connect() on line 10. Worked fine yesterday.
> Wonder what it is that I screwed up?

This suggests that the PG functions aren't enabled in PHP, which is kind
of weird since they worked before. If you're on Windows, I'd
double-check your php.ini file to make sure that the PostgreSQL
extension is enabled (not commented out).

Ray.

------------------------------------------------------------ ------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------ ------

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

Raymond O'Donnell wrote:
> On 31/07/2008 14:55, PJ wrote:
>> Now I am trying to verify the padding on "Alcohol" and in so doing
>> tried to invoke phpPgAdmin... and you gussed it, I tried to follow
>> all hte INSTALL instructions and http://localhost/phppgadmin just
>> gives "cannot find on this server"
>
> What web server are you running? What platform? How did you set it up?
FreeBS D7.0, postgresql 8.3.3 php 5.2.6, php5-pgsql5.2.6_1, apache
2.2.9, phpPgAdmin4.2_1,
>
>> On to of it, the same script I ran yesterday now give me "Fatal
>> error" Call to undefined function pg_connect() on line 10. Worked
>> fine yesterday.
>> Wonder what it is that I screwed up?
>
> This suggests that the PG functions aren't enabled in PHP, which is
> kind of weird since they worked before. If you're on Windows, I'd
> double-check your php.ini file to make sure that the PostgreSQL
> extension is enabled (not commented out).
I just reconfigured php.ini, postgresql.conf to what they were before
but no change.
What is weird is that I have the file extensions.ini under the php
directory but I cannot find a reference to it in any conf file.
There is an entry in php.ini :
; UNIX: "/path1:/path2"
#include_path = ".:/usr/local/share/pear"
So, path1 would seem to mean what? The period means what?
The extensions.ini is in the /usr/local/etc/php/ directory. I recall
configuring the extensions.ini file a while back but for the life of me
I cannot recall how or where it was referred to.
Should I be setting the include path1 to ../ for the extensions or
/usr/local/etc/php ?
>
>
> Ray.
>
> ------------------------------------------------------------ ------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------ ------
>


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

Finally muddled my way throught the php.ini file and set the includes
and extensions right.
So, thanks much all.

Raymond O'Donnell wrote:
> On 31/07/2008 14:55, PJ wrote:
>> Now I am trying to verify the padding on "Alcohol" and in so doing
>> tried to invoke phpPgAdmin... and you gussed it, I tried to follow
>> all hte INSTALL instructions and http://localhost/phppgadmin just
>> gives "cannot find on this server"
>
> What web server are you running? What platform? How did you set it up?
FreeBS D7.0, postgresql 8.3.3 php 5.2.6, php5-pgsql5.2.6_1, apache
2.2.9, phpPgAdmin4.2_1,
>
>> On to of it, the same script I ran yesterday now give me "Fatal
>> error" Call to undefined function pg_connect() on line 10. Worked
>> fine yesterday.
>> Wonder what it is that I screwed up?
>
> This suggests that the PG functions aren't enabled in PHP, which is
> kind of weird since they worked before. If you're on Windows, I'd
> double-check your php.ini file to make sure that the PostgreSQL
> extension is enabled (not commented out).
I just reconfigured php.ini, postgresql.conf to what they were before
but no change.
What is weird is that I have the file extensions.ini under the php
directory but I cannot find a reference to it in any conf file.
There is an entry in php.ini :
; UNIX: "/path1:/path2"
#include_path = ".:/usr/local/share/pear"
So, path1 would seem to mean what? The period means what?
The extensions.ini is in the /usr/local/etc/php/ directory. I recall
configuring the extensions.ini file a while back but for the life of me
I cannot recall how or where it was referred to.
Should I be setting the include path1 to ../ for the extensions or
/usr/local/etc/php ?
>
>
> Ray.
>
> ------------------------------------------------------------ ------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------ ------
>



--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: display query results

You were quite right, the problem was a line feed after the word "Alcohol"
Now it works fine.
Thanks, I learned a lot .... :)

Andy Shellam wrote:
>> Haha, no worries, I've had the same issue before.
>> It's almost certain that the text in your column does not exactly
>> match "Alcohol" for one of a few reasons, e.g.
>>
>> "Alcohol " (right-padding)
>> " Alcohol" (left-padding)
>> " Alcohol " (padded both sides)
>>
>> will not match = 'Alcohol' in the query.
>> Try trimming the data in that field - from PgAdmin or some other
>> query tool, run something like:
>>
>> |UPDATE glossary_item SET "name" = |trim(both ' ' from "name")


--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php