Query problems with PostgreSQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

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.

Quoted text here. Click to load it
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= 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

The code for my detailed results page looks like this:

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

    $myresult = pg_exec($connection, "SELECT * FROM
WHERE identification_no = '$id_no'");

'public.archived_with_photos' is a view defined on the postgresql

I can run the query

SELECT * FROM public.archived_with_photos WHERE identification_no =

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!

Re: Query problems with PostgreSQL

webhead74 wrote:

Quoted text here. Click to load it

I cannot imagine Postrgres will have problems with a certain  
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.

Erwin Moller

Re: Query problems with PostgreSQL

webhead74 wrote:

Quoted text here. Click to load it

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

Site Timeline