How to read all tables and fields in a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

How to read all tables and fields in a database

Post by raghavan20 »

I want find all tables in a database.
I want to extract all fields from each table.
I want to extract all values in each field

How to use PHP to do the above?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you are using MySQL the following queries should get you started (it's only a matter of iterating through all the queries that can return multiple rows)

Code: Select all

SHOW DATABASES;
USE database;
SHOW TABLES;
SHOW COLUMNS FROM table;
SELECT DISTINCT column FROM table;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

check into mysqldump or other various utilities in its goodie-bag..

SELECT ... INTO OUTFILE works nicely too.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

I am actually thinking of building a system to create backup the database.

Do you think the commands you specified are the common ones used for a backup operation?

Do I have to run all those commands using mysql_query and use mysql_fetch_array, mysql_num_rows to extract results?

Do I have to write them into a file?

Is there is a standard way how it should be written into a file to read from it later on ?
Last edited by raghavan20 on Sat Aug 13, 2005 7:43 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at mysqldump's output. It's very standard. (potentially check out phpMyAdmin's as well) very similar to mysqldump, but their own cooking..
Post Reply