From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | admin <mick(at)mjhall(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: php + postgresql |
Date: | 2008-07-24 10:13:41 |
Message-ID: | 488855D5.8040109@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | adam_pgsql | 2008-07-24 10:15:22 | Re: php + postgresql |
Previous Message | Raymond O'Donnell | 2008-07-24 10:13:33 | Re: php + postgresql |