473,377 Members | 1,153 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,377 software developers and data experts.

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 2312
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound...
16
by: Dave Weaver | last post by:
I'm having severe performance issues with a conceptually simple database. The database has one table, containing weather observations. The table currently has about 13.5 million rows, and is being...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
1
by: Dino Nardini | last post by:
Hey folks, I've been migrating a content management system from MS SQL Server 7 over to PostgreSQL. I used the EMS DataPump utility (http://www.ems-hitech.com/index.phtml) to migrate the...
3
by: Stijn Vanroye | last post by:
Hi List, I'm running a query on a not-so-small db. Mostly this query runs fast enough, but every once in a while the query takes a long time to complete in wich case the users start banging away...
2
by: Keith C. Perry | last post by:
Ok, I've tried a number of things here and I know I'm missing something but at this point my head is spinning (i.e. lack of sleep, too much coffee, etc...) My environment is PG 7.4.3 on Linux...
11
by: Tore Halset | last post by:
Hello. I am trying to port an old java application from MS SQL Server to PostgreSQL running on Mac OS X. I have access to the java source code and can make modifications. I have tried with...
3
by: Rodríguez Rodríguez, Pere | last post by:
Hello, I think I have found a query problem when the query has an alias for a table and use alias item and table name. I ilustrate the problem with a simple table and query. prr=# create...
0
by: Lee Blevins | last post by:
I'm trying to query a postgresql database with PHP. I am having a problem when using a WHERE. It works without the WHERE. Example: $query = "SELECT job_number, customer, desription, archive,...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.