[OT] Postgresql and PHP

On 12/23/2016 07:44 AM, Alessandro Baggi wrote:

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.

From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.

And what is the count?

From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have searched

How do you know it fails?

error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where srcaddr
= $1
DETAIL: parameters: $1 = '192.168.1.1'

I've tried also to use pg_last_error and errors are not printed.

Can someone put me on the right way?

Thanks in advance.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi <
alessandro.baggi@gmail.com> wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have searched
error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where srcaddr =
$1
DETAIL: parameters: $1 = '192.168.1.1'

​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which has
the relevant data, is not the same database that PHP is talking to, and
which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.

On 23/12/16 15:44, Alessandro Baggi wrote:

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but
changed to see if this solves my problem.

From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

and query is performed.

From php I'm trying to do the same with this code:

$ipsrc = "192.168.1.1";

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource where
srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

Are you sure it's failing? Try this:

if ($query === false) {
....
}

PHP has funny ideas about what's true and false.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Adrian,

And what is the count?

Reported is 1

How do you know it fails?

Really I don't know if it fails but

$query = pg_query_params(....);
if(!$query)
{
echo pg_last_error($dbcon);
echo "ERROR";
} else {
$row = pg_fetch_assoc($query);
if(!$row)
{
echo "some error";
}

}

does not help to check if there are errors?

With this code I block in the first statement with "ERROR" string
printed and not real error. I've also tried to see if pg_last_error()
works with query keywords errors (Select * from nonexisttable) and works.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
<alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL: parameters: $1 = '192.168.1.1'

​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.

Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.

Then I've only this db and only this host.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
<alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL: parameters: $1 = '192.168.1.1'

​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.

Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.

Might want to set log_statement to 'all' at least temporarily:

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

Then I've only this db and only this host.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/23/2016 08:03 AM, Alessandro Baggi wrote:

Il 23/12/2016 16:52, David G. Johnston ha scritto:

On Fri, Dec 23, 2016 at 8:44 AM, Alessandro Baggi
<alessandro.baggi@gmail.com <mailto:alessandro.baggi@gmail.com>>wrote:

$query = pg_query_params($dbcon, "SELECT count(*) from bs_ipsource
where srcaddr = $1", array($ipsrc));
if(!$query) { print error...}

I don't understand why this query fails without error. I have
searched error in httpd logs and postgresql log without success.

from postgresql logs i get:

LOG: execute <unnamed>: SELECT count(*) from bs_ipsource where
srcaddr = $1
DETAIL: parameters: $1 = '192.168.1.1'

​You might wish to explain how you've defined "fail" since there is no
actual error.

One common explanation is that the database psql is talking to, which
has the relevant data, is not the same database that PHP is talking to,
and which lacks matching data.

You might want to fire off one or more "host machine identification"​
queries in both just to confirm whether this is the case.

David J.

Hi David,
thanks for your answer.
I don't think that this is the problem.
Before the reported query I've other queries to the same db on other
tables and works without problem.

Then I've only this db and only this host.

Another thought, do you have this table in more then one schema and
maybe you are running into a search_path issue:

https://www.postgresql.org/docs/9.5/static/runtime-config-client.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,
On Fri, 2016-12-23 at 16:44 +0100, Alessandro Baggi wrote:

Hi list,
sorry for this OT.

I have a table on postgresql like this:

id serial not null,
srcaddr varchar(16) not null

I use this table to store ip address. I've used also inet type but 
changed to see if this solves my problem.

 From psql, I run:

select count(*) from bs_ipsource where srcaddr = '192.168.1.1';

I store and retrieve IP Addresses as follows:-

id serial not null,
srcaddr inet not null

select count(*) from bs_ipsource where srcaddr = '192.168.1.1'::inet;

Cast the textual representation to inet.

Never had a problem.

HTH,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general