listing databases
listing databases
От:
magnus <reduct@asa.de>
Дата:
Hello, Can anyone tell me how to list (1.) databases and (2.) tables in a database using php? Thanks, Magnus Lawrie
Re: listing databases
От:
"Adam Lang" <aalang@rutgersinsurance.com>
Дата:
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" To: 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
Re: listing databases
От:
Louis Bertrand <louis@bertrandtech.on.ca>
Дата:
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 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 >
Re: listing databases
От:
"Dan Wilson" <dan_wilson@geocities.com>
Дата:
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" To: 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
Re: listing databases
От:
"Adam Lang" <aalang@rutgersinsurance.com>
Дата:
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" To: "magnus" ; 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" > To: > 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
Re: listing databases
От:
Dan Wilson <killroyboy@yahoo.com>
Дата:
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 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" > To: "magnus" ; > > 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" > > To: > > 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/
Re: listing databases
От:
Tod McQuillin <devin@spamcop.net>
Дата:
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
Re: listing databases
От:
Tod McQuillin <devin@spamcop.net>
Дата:
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
Re: listing databases
От:
"Adam Lang" <aalang@rutgersinsurance.com>
Дата:
Ah, gotcha. That's good to know. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Dan Wilson" To: "Adam Lang" ; 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 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" > > To: "magnus" ; > > > > 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" > > > To: > > > 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/
Re: listing databases
От:
"Adam Lang" <aalang@rutgersinsurance.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" To: "magnus" Cc: 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 >
RE: listing databases
От:
"Tony Dunn" <atdunn@bellsouth.net>
Дата:
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
Re: listing databases
От:
magnus <reduct@asa.de>
Дата:
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"
> *************************