See postgre tables from PHP code

Hi everybody:

I'm relatively new to PHP programming and I was recommended to use
PostgreSQL as the site's database. However I haven't able to see my DB's
tables from PHP. Here is my (very simple, I think) code:

$pg = pg_connect("host=localhost port =5432 dbaname = ATM user=postgres
password = mypassword");
pg_query($pg,"select nick,contrasenna,nombre,apellidos from usuarios");

Beside, I opened my PostgreSQL interface, called pgAdmin and run the same
query inside the ATM database and the error was the same: "relation
'usuarios' was not found". It's like something else was needed to access the
DB tables.



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

Re: See postgre tables from PHP code

> Beside, I opened my PostgreSQL interface, called pgAdmin and run the same
> query inside the ATM database and the error was the same: "relation
> 'usuarios' was not found". It's like something else was needed to access the
> DB tables.

From the postgres monitor (or maybe pgAdmin) do \d to see a list of
the tables available.

That is, in my case:

mode=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | accode | table | mode
public | categories | table | mode
public | key_requests | table | mode
public | listings | table | mode
public | listings_listing_id_seq | sequence | mode
public | query_benchmarks | table | mode
public | stored_strings | table | mode
public | uptime_benchmarks | table | mode
public | users | table | mode
public | users_user_id_seq | sequence | mode
(10 rows)

mode=>

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

Re: See postgre tables from PHP code

On Mon, 2008-11-10 at 05:04 +0100, Félix Sánchez Rodríguez=
wrote:
> Hi everybody:
>=20
> I'm relatively new to PHP programming and I was recommended to use=20
> PostgreSQL as the site's database. However I haven't able to see my DB'=
s=20
> tables from PHP. Here is my (very simple, I think) code:
>=20
> $pg =3D pg_connect("host=3Dlocalhost port =3D5432 dbaname =3D ATM user=3D=
postgres=20
> password =3D mypassword");
> pg_query($pg,"select nick,contrasenna,nombre,apellidos from usuarios");
>=20
> Beside, I opened my PostgreSQL interface, called pgAdmin and run the sa=
me=20
> query inside the ATM database and the error was the same: "relation=20
> 'usuarios' was not found". It's like something else was needed to acces=
s the=20
> DB tables.=20

Is it possible that the tables are in a different schema?

For example, on a Mediawiki installation I have:

davical_wiki=3D# \d
No relations found.
davical_wiki=3D# set search_path TO 'mediawiki';
SET
davical_wiki=3D# \d
List of relations
Schema | Name | Type | Owner =20
-----------+-----------------------+----------+------------- -----
mediawiki | archive | table | davical_wikiuser
mediawiki | category | table | davical_wikiuser
mediawiki | category_id_seq | sequence | davical_wikiuser
....

You can refer to tables in a different schema by prefixing the table
name with the schema name and '.', e.g. mediawiki.archive

davical_wiki=3D# \d mediawiki.archive
Table "mediawiki.archive"
Column | Type | Modifiers =20
---------------+--------------------------+----------------- ---
ar_namespace | smallint | not null
ar_title | text | not null
....

The same prefixing syntax should work. The same 'set search_path ...'
syntax should also work if you do that after the pg_connect call.

If you're in psql, too, tab-completion should work, so if you go
\d you will see a list of schema, like:

davical_wiki=3D# \d=20
information_schema. pg_catalog. pg_toast. public. =
=20
mediawiki. pg_temp_1. pg_toast_temp_1. =20

If I did that after setting the search path the autocomplete finds all
the tables in the mediawiki schema also.

Cheers,
Andrew.

------------------------------------------------------------ ------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
Q: How much does it cost to ride the Unibus?
A: 2 bits.
------------------------------------------------------------ ------------



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

Re: See postgre tables from PHP code

On 10/11/2008 04:04, F=E9lix S=E1nchez Rodr=EDguez wrote:

> Beside, I opened my PostgreSQL interface, called pgAdmin and run the
> same query inside the ATM database and the error was the same: "relatio=
n
> 'usuarios' was not found". It's like something else was needed to acces=
s
> the DB tables.

Did you by any chance create the tables from pgAdmin and use mixed-case
names?

PostgreSQL by default will fold names to lower-case unless you put
double-quotes around them, so e.g. "Usarios" is a different table from
"usarios".

When creating a table from pgAdmin, if you mix upper- and lower-case in
a table name then pgAdmin double-quotes the names to preserve the case;
and if you then SELECT from the table without double-quoting the name,
Postgres fold the names to lower-case and then can't find the table.

Ray.


------------------------------------------------------------ ------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------ ------

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

Re: See postgre tables from PHP code

If your code below is literal, then try changing "dbaname" to
"dbname"; otherwise it's not going to set the database properly and
won't know where these tables are.

-- Andy

On Nov 9, 2008, at 11:04 PM, F=E9lix S=E1nchez Rodr=EDguez wrote:

> Hi everybody:
>
> I'm relatively new to PHP programming and I was recommended to use
> PostgreSQL as the site's database. However I haven't able to see my
> DB's tables from PHP. Here is my (very simple, I think) code:
>
> $pg =3D pg_connect("host=3Dlocalhost port =3D5432 dbaname =3D ATM
> user=3Dpostgres password =3D mypassword");
> pg_query($pg,"select nick,contrasenna,nombre,apellidos from
> usuarios");
>
> Beside, I opened my PostgreSQL interface, called pgAdmin and run the=20=
=20
> same query inside the ATM database and the error was the same:
> "relation 'usuarios' was not found". It's like something else was
> needed to access the DB tables.
>
>
> --=20
> Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php


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