php + postgresql

admin wrote:

First, thanks to everyone who responded to my newbie questions
yesterday, all clear now.

I spent most of today struggling with apparently inconsistent behaviour
while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are
both as supplied with CentOS 5.1, a fairly conservative distro).

It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?

1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?

It sounds like you must be using a connection pooler, so your scripts
are acquiring connections that've already been used and had statements
prepared for them. If you try to prepare a new statement with the same
name it'll fail.

I understand that this is a common issue with simple connection poolers,
but as I don't deal with them myself I don't have any suggestions for
you. Others here may, and I'm sure Google can help out too.

3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?

Required by what? I'm not sure I really understand your question.

Do you mean "does PostgreSQL always return a row count that can then be
accessed with pg_num_rows()" ?

Or:

"Must I iterate through a resultset with a loop over pg_num_rows()
rather than using some other method to iterate through the resultset" ?

4.
Another weird one was that this statement always failed:

$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'

"failed" how? What did you expect to happen? What happened instead? What
was the exact error message?

You always need to ask yourself those questions when reporting any sort
of problem. Otherwise, the people reading your question will just have
to ask them, so you'll get slower and less useful responses (and fewer
of them, as many people will just ignore poorly written questions).

--
Craig Ringer

On 24/07/2008 10:41, admin wrote:

I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?

That's not something I've ever encountered, and I've done a good bit of
PHP+PG at this stage. Can you show us an example? Also, how are you
connecting? - are you simply doing pg_connect(....) to connect directly,
or is there anything else in the middle - maybe a connection pooler of
some kind?

Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

Again, this ought to be fine as you've shown it....can you show us the
SELECT statement and other information?

Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?

No real need - I generally use the idiom you have above -

$rs = pg_query($sql_string);
while ($row = pg_fetch_assoc($rs)
{
$value = $row['col1'];
// etc....
}

Another weird one was that this statement always failed:

$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'

That's because you need to use double-inverted-commas for string
interpolation:

...WHERE fld_name = "$name"

Ray.

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

admin wrote:

First, thanks to everyone who responded to my newbie questions
yesterday, all clear now.

I spent most of today struggling with apparently inconsistent behaviour
while running SELECT statements on PG 8.1.9 using PHP 5.1.6 (these are
both as supplied with CentOS 5.1, a fairly conservative distro).

It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?

PHP's functions change on a regular basis I'm afraid. There's a
changelog to track the detail, but the docs give details of larger
changes. You might find it simplest to refer to the docs that come with
your distro.

1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?

You're probably using persistent connections. Don't - they're not much
use with a standard Apache+PHP installation. Prepared queries last for
the length of a session (connection).

2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

Nothing leaping out at me, but don't refer to columns by index, refer to
them by name.

3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?

Not required. The while($row=) works if you want all rows. Of course if
you just want a page of 20 or so then you might want a for loop.

4.
Another weird one was that this statement always failed:

$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'

while this one always worked:

SELECT fld_content FROM tbl_page WHERE fld_pid=1

1. Don't interpolate variables directly into SQL. Use the parameterised
query functions.
2. Check the error message to see why there's a problem.

... but this post is getting too unwieldy. I am reading documentation
but am also under some pressure to get basic things up and running. Any
pointers to good documentation covering PHP + PG, or any well known
gotchas?

None (other than the fact that persistent connections don't work how a
newbie might want).

PS If people want to throw MySQL->PostgreSQL gotchas at me I'm happy to
collate and write up.

Traditionally MySQL is very "relaxed" about data validity. PostgreSQL
isn't and dates of 00-00-0000 aren't allowed. There are pages of "mysql
gotchas" and "postgresql gotchas" too - google for them.

--
Richard Huxton
Archonet Ltd

Hi Mick,

1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone
just didn't seem to work. But SELECT statements seemed to be cached
or persistent in some way, such that they "lived" beyond the life of
the PHP script. Is there something I need to know about persistent
behaviour in PG that doesn't exist in MySQL?

Do you have an example? and what makes you say they are persisting?

2.
Another problem was that no matter how many times I checked and re-
checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

are you sure pg_fetch_array($query) is returning any rows? (try echo
$row[0]; within the while loop)

3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ...
is this required behaviour (PHP docs don't appear to discuss this)?

I often do something along the lines of this:

if($stat = pg_exec($dbh, $sql))
{
if($rows = pg_numrows($stat))
{
for($i=0; $i < $rows; $i++)
{
$data = pg_fetch_array($stat, $i);

# do something with $data
}
}
else{echo "no rows returned";}
}
else{echo "query failed";}

4.
Another weird one was that this statement always failed:

$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'

is $name being interpolated correctly when you use it.... maybe use:

$sql = "SELECT fld_content FROM tbl_page WHERE fld_name='".$name."'";

(or use a prepared statement)

On 24/07/2008 11:13, Raymond O'Donnell wrote:

$rs = pg_query($sql_string);
while ($row = pg_fetch_assoc($rs)

Whoops! -

while ($row = pg_fetch_assoc($rs))
....

Ray.

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

On Thursday 24. July 2008, admin wrote:

It seems that some of PHP's PG functions have changed recently, are
there any known issues with them?

I've been using PHP with PostgreSQL for 5 years, and haven't noticed any
substantial changes.

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

You have to use an intermediate variable like a handle. Try this:

$handle = pg_query("SELECT whatever FROM foo");
while ($row = pg_fetch_array($handle) {
$content = $row[0];
}

Any pointers to good documentation covering PHP + PG, or any well
known gotchas?

You can download my pg+php genealogy app "exodus" from here:

http://solumslekt.org/forays/exodus.tgz

The code is certainly not stellar, but it works for me. Note that the
code is intended for running in a private environment, and there are no
security features whatsoever.
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On Thu, 24 Jul 2008 19:11:36 +0930
admin <mick@mjhall.org> wrote:

2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an
array member and trying to use it later just would not work, eg

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

Did the result contain at least 1 row?
Also prefer column names. If you change the schema, order etc...
you'll have less chances to break code.

What do you mean by 'undeclared'?

if(!isset($content)) ?

or just

echo $content doesn't return output?

what about
$content .= $row[0]." # ";
for quick debugging?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Thu, 24 Jul 2008 12:30:22 +0200
"Leif B. Kristensen" <leif@solumslekt.org> wrote:

On Thursday 24. July 2008, admin wrote:

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

$content was always 'undeclared'.

You have to use an intermediate variable like a handle. Try this:

$handle = pg_query("SELECT whatever FROM foo");
while ($row = pg_fetch_array($handle) {
$content = $row[0];
}

ivan@dawn:~$ php -a
Interactive mode enabled

<?php
echo pg_fetch_array($pippo);

Warning: pg_fetch_array() expects parameter 1 to be resource, null
given in /home/ivan/- on line 2

Call Stack:
18.3923 1079256 1. {main}() /home/ivan/-:0
18.3924 1079528 2. pg_fetch_array() /home/ivan/-:2

It can't be the problem. BTW even passing a string will end up in
the same error.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Thursday 24 July 2008 12:41, admin wrote:

1.
I ended up using pg_prepare() and pg_execute() as pg_query() alone just
didn't seem to work. But SELECT statements seemed to be cached or
persistent in some way, such that they "lived" beyond the life of the
PHP script. Is there something I need to know about persistent behaviour
in PG that doesn't exist in MySQL?

Not sure what causes this with your server but I always use something like
this, ie first connect then do your stuff and then close the connection:

require("dbconnect.inc"); // holds the $conn which is pg_connect("with
passes")

if (!$conn)
{exit("Database connection failed. Please try again." . $conn);}

$sql ="SELECT ...";

$product=pg_exec($conn,$sql);
if (!$product)
{exit("Database connection failed. Please try again.");}

while ($row = pg_fetch_row($product))
{
echo"

$row[1]

";
}

pg_close($conn);

BR,
--
Aarni

Burglars usually come in through your windows.

Thanks again for replies.
I know those questions were pretty vague.
I need to set up some methodical test scripts that replicate my
problems, so that it is clear what is going on.

There does seem to be some evidence of problems historically with PHP
and persistent connections in PostgreSQL, on the PHP forums. The advice
is typically to avoid them.

Mick

On Thu, Jul 24, 2008 at 6:33 AM, admin <mick@mjhall.org> wrote:

Thanks again for replies.
I know those questions were pretty vague.
I need to set up some methodical test scripts that replicate my problems, so
that it is clear what is going on.

There does seem to be some evidence of problems historically with PHP and
persistent connections in PostgreSQL, on the PHP forums. The advice is
typically to avoid them.

php and persistant connections are a foot gun for any database really.
There are very strict provisioning rules you have to follow to use
them correctly, and they are often NOT the best answer for a given
problem. Until they are. :)

Mick,

As I haven't seen anyone else say it, I just wanted to throw this in.

I'm not a PHP programmer, so I'm not very sure of PHP's scoping rules,
but this looks to me like a variable scoping problem. If the first
time you've used $content is inside of the while(), it's probably
going out of scope before your echo. Try this:

# Initialize $content before going into the loop.
# This declares it outside the scope of the while()

$content=''';

# Now do your loop

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

Your loop is a little weird, too. You're not accumulating anything,
you're just saving the previous value. When you exit the loop,
$content will only contain the value from the final row. If that's
your intent, you may save some time by reverse-ordering your query and
using "limit 1". That way you can remove the loop altogether and save
lots of processing time.

--
David Spadea

On Thu, 24 Jul 2008 11:13:52 -0400
"David Spadea" <david.spadea@gmail.com> wrote:

Mick,

As I haven't seen anyone else say it, I just wanted to throw this
in.

I'm not a PHP programmer, so I'm not very sure of PHP's scoping
rules, but this looks to me like a variable scoping problem. If
the first time you've used $content is inside of the while(), it's
probably going out of scope before your echo. Try this:

ivan@dawn:~$ php -a
Interactive mode enabled

<?php
if(true) {
$content="it exixts";
}
print $content;
it exixts

The code and PHP scoping are not nice but it should prove that
scoping is not the problem ;)

# Initialize $content before going into the loop.
# This declares it outside the scope of the while()

$content=''';

mistype

# Now do your loop

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

Your loop is a little weird, too. You're not accumulating anything,
you're just saving the previous value. When you exit the loop,
$content will only contain the value from the final row. If that's

for debugging I suggested:
$content .= $row[0]." # ";
So he could see if any row even if all $row[0] contained '' or null.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

There does seem to be some evidence of problems historically with PHP
and persistent connections in PostgreSQL, on the PHP forums. The advice
is typically to avoid them.

You'll find the same advice for mysql + persistent connections or any
other db + persistent connections. It's not a php+postgres thing.

--
Postgresql & php tutorials
http://www.designmagick.com/

2.
Another problem was that no matter how many times I checked and
re-checked code, or which pg_fetch_* function I used, copying an array
member and trying to use it later just would not work, eg

while ($row = pg_fetch_array($query)) {
$content = $row[0]
}

echo $content;

pg_fetch_array expects a result from a query, not an actual sql query.

You need something like this:

$query = "select id, name from tablename";
$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$content = $row[0];
}

3.
Some examples I found used PHP's pg_num_rows() function to count the
rows in a result, then iterated through them with a "for" loop ... is
this required behaviour (PHP docs don't appear to discuss this)?

You used to have to do this but you don't any more.

The old style was something like:

<?php
....

$result = pg_query($query);
$rows = pg_num_rows($result);

for ($row_num = 0; $row_num < $rows; $row_num++) {
$db_row = pg_fetch_array($result, $row_num);
}

The new style works like:

<?php
....

$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$db_row = pg_fetch_array($result);
}

This was changed a lot time ago (according to the php manual - 4.1.0).

4.
Another weird one was that this statement always failed:

$name = "file.php";
SELECT fld_content FROM tbl_page WHERE fld_name='$name'

Escape your data:

$name = 'blah';
$query = "SELECT fld_content FROM tbl_page WHERE fld_name='" .
pg_escape_string($name) . "'";

<shameless plug>
I have some intro guides here you might want to check out:

http://www.designmagick.com/category/2/
</shameless plug>

--
Postgresql & php tutorials
http://www.designmagick.com/

You need something like this:

$query = "select id, name from tablename";
$result = pg_query($query);
while ($row = pg_fetch_array($result)) {
$content = $row[0];
}

That's actually what I was using.
The scoping wasn't the issue either.

Today I switched back to pg_connect() from pg_pconnect(), made some
changes to my overall architecture and re-wrote my database stuff. Then
re-booted.

Not sure what fixed it but all working now. I'm only working on a draft
"skeleton" right now so am free to fiddle.

Keep finding cool features in PostgreSQL, I think I'm sold!

Thanks
Mick

In response to Chris <dmagick@gmail.com>:

There does seem to be some evidence of problems historically with PHP
and persistent connections in PostgreSQL, on the PHP forums. The advice
is typically to avoid them.

You'll find the same advice for mysql + persistent connections or any
other db + persistent connections. It's not a php+postgres thing.

They're manageable if you know all the ins and outs. The big advantage
is speed, as they avoid the cost of establishing the initial TCP
connection and logging in. In my experiments, this cut the run time
for the average script in half.

But you have to deal with managing an overwhelming # of perpetually
open connections, which takes a lot of resources on both the server and
the client side, in addition to problems like connection settings persisting
from one script to the next.

My opinion is avoid them unless you have a demonstrated need for the
speed increase. In that case, make sure you have the time to understand
and code for all the potential issues.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************

Hi,

admin wrote:

Thanks again for replies.
I know those questions were pretty vague.
I need to set up some methodical test scripts that replicate my
problems, so that it is clear what is going on.

There does seem to be some evidence of problems historically with PHP
and persistent connections in PostgreSQL, on the PHP forums. The advice
is typically to avoid them.

usually it goes so far to avoid PHP alltogether ;)
Is there any special reason to use PHP? There are
a couple other scripting languages useable for the
web which do all have better abstration available.
(afaic even PHP does have some more abstration to
just using pg* functions)

Tino.

Aarni Ruuhimäki wrote:
...

Not sure what causes this with your server but I always use something like
this, ie first connect then do your stuff and then close the connection:

require("dbconnect.inc"); // holds the $conn which is pg_connect("with
passes")

I would avoid that in favour of using $HOME/.pgpass

http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html

HTH
Tino