Re: 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?

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