pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
See postgre tables from PHP code
5+ messages / 5 participants
[nested] [flat]

* See postgre tables from PHP code
@ 2008-11-10 04:04  Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
  0 siblings, 4 replies; 5+ messages in thread

From: Félix Sánchez Rodríguez @ 2008-11-10 04:04 UTC (permalink / raw)
  To: pgsql-php

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. 





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: See postgre tables from PHP code
@ 2008-11-12 03:32  Brew <brew@themode.com>
  parent: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
  3 siblings, 0 replies; 5+ messages in thread

From: Brew @ 2008-11-12 03:32 UTC (permalink / raw)
  To: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>; +Cc: pgsql-php

> 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=>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: See postgre tables from PHP code
@ 2008-11-12 09:03  Andrew McMillan <andrew@morphoss.com>
  parent: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
  3 siblings, 0 replies; 5+ messages in thread

From: Andrew McMillan @ 2008-11-12 09:03 UTC (permalink / raw)
  To: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>; +Cc: pgsql-php

On Mon, 2008-11-10 at 05:04 +0100, Félix Sánchez Rodríguez 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 = 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. 

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

For example, on a Mediawiki installation I have:

davical_wiki=# \d
No relations found.
davical_wiki=# set search_path TO 'mediawiki';
SET
davical_wiki=# \d
                        List of relations
  Schema   |         Name          |   Type   |      Owner       
-----------+-----------------------+----------+------------------
 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=# \d mediawiki.archive
                   Table "mediawiki.archive"
    Column     |           Type           |     Modifiers      
---------------+--------------------------+--------------------
 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<space><tab><tab> you will see a list of schema, like:

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

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.
------------------------------------------------------------------------





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: See postgre tables from PHP code
@ 2008-11-12 09:34  Raymond O'Donnell <rod@iol.ie>
  parent: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
  3 siblings, 0 replies; 5+ messages in thread

From: Raymond O'Donnell @ 2008-11-12 09:34 UTC (permalink / raw)
  To: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>; +Cc: pgsql-php

On 10/11/2008 04:04, Félix Sánchez Rodríguez wrote:

> 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.

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
------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: See postgre tables from PHP code
@ 2008-11-12 13:16  Andy Anderson <aanderson@amherst.edu>
  parent: Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
  3 siblings, 0 replies; 5+ messages in thread

From: Andy Anderson @ 2008-11-12 13:16 UTC (permalink / raw)
  To: pgsql-php

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élix Sánchez Rodríguez 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 = 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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2008-11-12 13:16 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-11-10 04:04 See postgre tables from PHP code Félix Sánchez Rodríguez <fesanch@ciego.cult.cu>
2008-11-12 03:32 ` Brew <brew@themode.com>
2008-11-12 09:03 ` Andrew McMillan <andrew@morphoss.com>
2008-11-12 09:34 ` Raymond O'Donnell <rod@iol.ie>
2008-11-12 13:16 ` Andy Anderson <aanderson@amherst.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox