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
