469,270 Members | 1,076 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Query problems with PostgreSQL

Hi,

I'm having intermittent problems with queries from my php script to a
postgresql database.

I have a form where I can enter a search query - for instance a last
name. This leads to a results page with a brief bit of information
about each of the matching results. From there, I can click a link
associated with any of the results which takes me to a page with all of
the details. Pretty standard stuff.

Problem I'm having is that for some of the results, when I click the
link to go to the full details page, no information is returned by the
script. I basically end up with my html template & no data populated.
For the majority of items, things work just fine.

One thing I've noticed is that it seems to be reproducable - that is,
for example item 5123 always fails to return any results on the full
details page.
>From my Postgres box (windows 2003), I can run the exact same query as
from my PHP script (with item id 5123) & it returns the data.

So what could the problem possibly be?

I thought that perhaps it was a load issue on the server - couldn't
answer the query, but the fact that it's reproducible seems to nix that
idea. Also, that Postgres box has 3.5 GB of RAM & we're talking about
a test system at this point - I'm the only one using it.

Code for my first search results page (basic info & hyperlinks) looks
like this ($name_last is the variable passed from the search form):

$connection=pg_connect("host=10.10.10.2 port=5432 dbname=db1
user=dbuser password=password");

$myresult = pg_exec($connection, "SELECT identification_no, name_last
, name_first, name_middle FROM public.j_identification WHERE name_last
~*
'$name_last'");
The code for my detailed results page looks like this:

$connection = pg_connect("host=10.10.10.2 port=5432 dbname=db1
user=dbuser password=password");

$myresult = pg_exec($connection, "SELECT * FROM
public.archived_with_photos
WHERE identification_no = '$id_no'");
'public.archived_with_photos' is a view defined on the postgresql
server.

I can run the query

SELECT * FROM public.archived_with_photos WHERE identification_no =
'5123';

and it returns all of the associated data from the database... whereby
the exact same query failed with the PHP script.

Any help or ideas greatly appreciated!

Jan 10 '07 #1
2 2164
webhead74 wrote:
Hi,

I'm having intermittent problems with queries from my php script to a
postgresql database.

I have a form where I can enter a search query - for instance a last
name. This leads to a results page with a brief bit of information
about each of the matching results. From there, I can click a link
associated with any of the results which takes me to a page with all of
the details. Pretty standard stuff.

Problem I'm having is that for some of the results, when I click the
link to go to the full details page, no information is returned by the
script. I basically end up with my html template & no data populated.
For the majority of items, things work just fine.

One thing I've noticed is that it seems to be reproducable - that is,
for example item 5123 always fails to return any results on the full
details page.
>>From my Postgres box (windows 2003), I can run the exact same query as
from my PHP script (with item id 5123) & it returns the data.

So what could the problem possibly be?

I thought that perhaps it was a load issue on the server - couldn't
answer the query, but the fact that it's reproducible seems to nix that
idea. Also, that Postgres box has 3.5 GB of RAM & we're talking about
a test system at this point - I'm the only one using it.

Code for my first search results page (basic info & hyperlinks) looks
like this ($name_last is the variable passed from the search form):

$connection=pg_connect("host=10.10.10.2 port=5432 dbname=db1
user=dbuser password=password");

$myresult = pg_exec($connection, "SELECT identification_no, name_last
, name_first, name_middle FROM public.j_identification WHERE name_last
~*
'$name_last'");
The code for my detailed results page looks like this:

$connection = pg_connect("host=10.10.10.2 port=5432 dbname=db1
user=dbuser password=password");

$myresult = pg_exec($connection, "SELECT * FROM
public.archived_with_photos
WHERE identification_no = '$id_no'");
'public.archived_with_photos' is a view defined on the postgresql
server.

I can run the query

SELECT * FROM public.archived_with_photos WHERE identification_no =
'5123';

and it returns all of the associated data from the database... whereby
the exact same query failed with the PHP script.

Any help or ideas greatly appreciated!
Strange.
I cannot imagine Postrgres will have problems with a certain
identification_no.
It must be something else.
Did you turn on ALL errorreporting?
And what does $myresult contain?

Also pg_exec is very old. Avoid it.
php.net doesn't even list it anymore...
Start using pg_query instead.

Regards,
Erwin Moller
Jan 10 '07 #2
webhead74 wrote:
$myresult = pg_exec($connection, "SELECT * FROM
public.archived_with_photos
WHERE identification_no = '$id_no'");
Could be something funny going on with the variable interpolation. Try:

$myquery = "SELECT *
FROM public.archived_with_photos
WHERE identification_no = '$id_no'";
$myresult = pg_query($connection, $query);
var_dump(array('Q'=>$query, 'R'=>$myresult));

Does the var_dump reveal the query you expected?

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

Jan 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Dave Weaver | last post: by
1 post views Thread by Dino Nardini | last post: by
3 posts views Thread by Stijn Vanroye | last post: by
2 posts views Thread by Keith C. Perry | last post: by
3 posts views Thread by Rodríguez Rodríguez, Pere | last post: by
reply views Thread by Lee Blevins | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.