Thread: listing databases
Hello, Can anyone tell me how to list (1.) databases and (2.) tables in a database using php? Thanks, Magnus Lawrie
I don't know of a way. To connect to postgresql I think it is mandatory to include the dbname. Even if you didn't, there is no function that lists the DBs on the system. Unfortunately that is something MySQL in PHP has. You can connect to jsut the Server, and it has a function called mysql_list_dbs which returns a result set of databases on the server. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "magnus" <reduct@asa.de> To: <pgsql-php@postgresql.org> Sent: Wednesday, November 01, 2000 10:02 AM Subject: [PHP] listing databases > Hello, > Can anyone tell me how to list (1.) databases and (2.) tables in a > database using php? > Thanks, > Magnus Lawrie
Just a quick answer without doing much research. Have a look at the system tables. There is a switch in psql (can't remember which one) to monitor the actual sql sent to the back-end. Start with \dS to list the system tables. Try SELECT * FROM pg_database; to get the databases I think template1 always exists, so you can try connecting to that if you're not sure where to start. I'm not sure how to get the tables, but I'm sure it's the same process. Ciao --Louis <louis@bertrandtech.on.ca> Louis Bertrand http://www.bertrandtech.on.ca/ Bertrand Technical Services, Bowmanville, ON, Canada Tel: +1.905.623.1500 Fax: +1.905.623.3852 OpenBSD: Secure by default. http://www.openbsd.org/ On Wed, 1 Nov 2000, magnus wrote: > Hello, > Can anyone tell me how to list (1.) databases and (2.) tables in a > database using php? > Thanks, > Magnus Lawrie >
Databases: SELECT datname FROM pg_databases Tables: SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' The "NOT LIKE 'pg%'" filters out the system tables. If you are interested in learning more about the system tables, you can download my phpPgAdmin (http://www.phpwizard.net/projects/phpPgAdmin) and view the source code. I have to use the system tables for almost everything. If you do not want to download it, you can check out the source code through the snapshot page (http://pgdemo.acucore.com/snapshot). -Dan Wilson ----- Original Message ----- From: "magnus" <reduct@asa.de> To: <pgsql-php@postgresql.org> Sent: Wednesday, November 01, 2000 7:02 AM Subject: [PHP] listing databases > Hello, > Can anyone tell me how to list (1.) databases and (2.) tables in a > database using php? > Thanks, > Magnus Lawrie
So you just connect to the database using username, password, host, and port? Don't use a dbname in the connection string? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Dan Wilson" <dan_wilson@geocities.com> To: "magnus" <reduct@asa.de>; <pgsql-php@postgresql.org> Sent: Wednesday, November 01, 2000 10:38 AM Subject: Re: [PHP] listing databases > Databases: > > SELECT datname FROM pg_databases > > Tables: > > SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' > > The "NOT LIKE 'pg%'" filters out the system tables. > > If you are interested in learning more about the system tables, you can > download my phpPgAdmin (http://www.phpwizard.net/projects/phpPgAdmin) and > view the source code. I have to use the system tables for almost > everything. If you do not want to download it, you can check out the source > code through the snapshot page (http://pgdemo.acucore.com/snapshot). > > -Dan Wilson > > > ----- Original Message ----- > From: "magnus" <reduct@asa.de> > To: <pgsql-php@postgresql.org> > Sent: Wednesday, November 01, 2000 7:02 AM > Subject: [PHP] listing databases > > > > Hello, > > Can anyone tell me how to list (1.) databases and (2.) tables in a > > database using php? > > Thanks, > > Magnus Lawrie
I connect using the default database which is usually template1. But you can access the system tables from a connection into any database. -Dan --- Adam Lang <aalang@rutgersinsurance.com> wrote: > So you just connect to the database using username, > password, host, and > port? Don't use a dbname in the connection string? > > Adam Lang > Systems Engineer > Rutgers Casualty Insurance Company > ----- Original Message ----- > From: "Dan Wilson" <dan_wilson@geocities.com> > To: "magnus" <reduct@asa.de>; > <pgsql-php@postgresql.org> > Sent: Wednesday, November 01, 2000 10:38 AM > Subject: Re: [PHP] listing databases > > > > Databases: > > > > SELECT datname FROM pg_databases > > > > Tables: > > > > SELECT tablename FROM pg_tables WHERE tablename > NOT LIKE 'pg%' > > > > The "NOT LIKE 'pg%'" filters out the system > tables. > > > > If you are interested in learning more about the > system tables, you can > > download my phpPgAdmin > (http://www.phpwizard.net/projects/phpPgAdmin) and > > view the source code. I have to use the system > tables for almost > > everything. If you do not want to download it, > you can check out the > source > > code through the snapshot page > (http://pgdemo.acucore.com/snapshot). > > > > -Dan Wilson > > > > > > ----- Original Message ----- > > From: "magnus" <reduct@asa.de> > > To: <pgsql-php@postgresql.org> > > Sent: Wednesday, November 01, 2000 7:02 AM > > Subject: [PHP] listing databases > > > > > > > Hello, > > > Can anyone tell me how to list (1.) databases > and (2.) tables in a > > > database using php? > > > Thanks, > > > Magnus Lawrie > __________________________________________________ Do You Yahoo!? From homework help to love advice, Yahoo! Experts has your answer. http://experts.yahoo.com/
On Wed, 1 Nov 2000, magnus wrote:
> Hello,
> Can anyone tell me how to list (1.) databases and (2.) tables in a
> database using php?
To list databases, run
psql -l
from your unix shell prompt.
Once connected, enter \dt to list the tables.
--
Tod McQuillin
On Wed, 1 Nov 2000, Tod McQuillin wrote:
> On Wed, 1 Nov 2000, magnus wrote:
>
> > Hello,
> > Can anyone tell me how to list (1.) databases and (2.) tables in a
> > database using php?
>
> To list databases, run
>
> psql -l
Sorry, I didn't notice that you wanted to do it from php. Even so, the
answer is almost the same.
If you run "psql -E -l" you get this:
devin@glass ~% psql -E -l
********* QUERY *********
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
*************************
That gives you the query that psql runs.
after you connect with psql -E, \dt tells you:
SELECT c.relname as "Name", 'table'::text as "Type", u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name", 'table'::text as "Type", NULL as "Owner"
FROM pg_class c
WHERE c.relkind = 'r'
AND not exists (select 1 from pg_views where viewname = c.relname)
AND not exists (select 1 from pg_user where usesysid = c.relowner)
AND c.relname !~ '^pg_'
ORDER BY "Name"
Those are the queries you should run from php.
--
Tod McQuillin
Ah, gotcha. That's good to know. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Dan Wilson" <killroyboy@yahoo.com> To: "Adam Lang" <aalang@rutgersinsurance.com>; <pgsql-php@postgresql.org> Sent: Wednesday, November 01, 2000 10:07 AM Subject: Re: [PHP] listing databases > I connect using the default database which is usually > template1. But you can access the system tables from > a connection into any database. > > -Dan > > --- Adam Lang <aalang@rutgersinsurance.com> wrote: > > So you just connect to the database using username, > > password, host, and > > port? Don't use a dbname in the connection string? > > > > Adam Lang > > Systems Engineer > > Rutgers Casualty Insurance Company > > ----- Original Message ----- > > From: "Dan Wilson" <dan_wilson@geocities.com> > > To: "magnus" <reduct@asa.de>; > > <pgsql-php@postgresql.org> > > Sent: Wednesday, November 01, 2000 10:38 AM > > Subject: Re: [PHP] listing databases > > > > > > > Databases: > > > > > > SELECT datname FROM pg_databases > > > > > > Tables: > > > > > > SELECT tablename FROM pg_tables WHERE tablename > > NOT LIKE 'pg%' > > > > > > The "NOT LIKE 'pg%'" filters out the system > > tables. > > > > > > If you are interested in learning more about the > > system tables, you can > > > download my phpPgAdmin > > (http://www.phpwizard.net/projects/phpPgAdmin) and > > > view the source code. I have to use the system > > tables for almost > > > everything. If you do not want to download it, > > you can check out the > > source > > > code through the snapshot page > > (http://pgdemo.acucore.com/snapshot). > > > > > > -Dan Wilson > > > > > > > > > ----- Original Message ----- > > > From: "magnus" <reduct@asa.de> > > > To: <pgsql-php@postgresql.org> > > > Sent: Wednesday, November 01, 2000 7:02 AM > > > Subject: [PHP] listing databases > > > > > > > > > > Hello, > > > > Can anyone tell me how to list (1.) databases > > and (2.) tables in a > > > > database using php? > > > > Thanks, > > > > Magnus Lawrie > > > > > __________________________________________________ > Do You Yahoo!? > >From homework help to love advice, Yahoo! Experts has your answer. > http://experts.yahoo.com/
How would you use that with PHP though? Can you get the info back as something you can parse or interpret? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Tod McQuillin" <devin@spamcop.net> To: "magnus" <reduct@asa.de> Cc: <pgsql-php@postgresql.org> Sent: Wednesday, November 01, 2000 11:05 AM Subject: Re: [PHP] listing databases > On Wed, 1 Nov 2000, magnus wrote: > > > Hello, > > Can anyone tell me how to list (1.) databases and (2.) tables in a > > database using php? > > To list databases, run > > psql -l > > from your unix shell prompt. > > Once connected, enter \dt to list the tables. > -- > Tod McQuillin >
Checkout http://www.phpwizard.net/projects/phpPgAdmin/ -----Original Message----- From: pgsql-php-owner@postgresql.org [mailto:pgsql-php-owner@postgresql.org]On Behalf Of magnus Sent: Wednesday, November 01, 2000 10:03 AM To: pgsql-php@postgresql.org Subject: [PHP] listing databases Hello, Can anyone tell me how to list (1.) databases and (2.) tables in a database using php? Thanks, Magnus Lawrie
Thankyou, that seems to work fine, although I found I had to uncomment
everything inside my sql query quotes:
$sql= "SELECT pg_database.datname as Database,
pg_user.usename as Owner FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid";
Magnus Lawrie
Tod McQuillin wrote:
> Sorry, I didn't notice that you wanted to do it from php. Even so, the
> answer is almost the same.
>
> If you run "psql -E -l" you get this:
>
> devin@glass ~% psql -E -l
> ********* QUERY *********
> SELECT pg_database.datname as "Database",
> pg_user.usename as "Owner"FROM pg_database, pg_user
> WHERE pg_database.datdba = pg_user.usesysid
>
> UNION
>
> SELECT pg_database.datname as "Database",
> NULL as "Owner"FROM pg_database
> WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
> ORDER BY "Database"
> *************************