Re: PHP and multiple tables in db

Hi everyone!

Something tells me this will be a RDBMS by RDBMS solution.

SQL Server has system tables that contain table names, column names, and 
data types.  The quickest SQL I know of to get a listing of all tables, 
columns in each table, and column datatypes is:

         select t.name as TableName, c.name as ColumnName, x.name as TypeName
           from sysobjects t INNER JOIN syscolumns c
                  ON t.id = c.id
                    INNER JOIN systypes x
                      ON c.xtype = x.xtype
          order by t.name, c.name

MySQL would require nested loops to retrieve the information.  Start with:

         show databases;

loop through each database name returned with:

         show tables from <database>;

loop through each table returned with:

         show columns from <table> from <database>;

and this query will return you columns of:

         Field   : column name
         Type    : column datatype

But, that is where my knowledge ends.  Other RDBMS systems will probably 
have their own methods.

Just somewhere to start looking.

Hope this helps,
Eric

At 07:43 PM 9/28/2000 -0500, Cyanide _7 wrote:
>i'm not sure if i am understanding the question correctly, but if i am, 
>well i'd like to know too! here is how interpretted the question to read:
>
>Given a database and a connection to it, how would one retrieve an 
>enumeration of the tables stored in it? in most cases, you have to know 
>the table's name explicitly before querying it or the like, but what if 
>you wanted to look at all tables, gather data about them like field types 
>and sizes etc...then have a standard for for editing generic tables. does 
>that make sense? the retrieving the table names part anyway? - Cyanide_7
>
>
>>--- Frani Ciesielski <franic(at)qis.net> wrote:
>> > Hello, Not sure if this can be done, but it would
>> > sure save a lot of time. I
>> > have 70 separate tables in a MySQL db that I need to
>> > add information to with
>> > a form. Is there a way to use a select list to say
>> > which table to insert the
>> > info into?
>>
>>There sure is. You could say something like
>>
>><select name="table"><option>table1<option>table2...
>>
>>and then your query is "insert into $table values
>>(...)" But you must really trust your users that they
>>know what they're doing, and if you have 70 tables you
>>might want to take another look at your data model
>>because that sounds like a lot.
>>
>>David Mintz
>>Spanish Interpreter, US District Court
>>Southern District of New York
>>Web Design & Hosting http://dmintzweb.com/
>>Personal http://panix.com/~dmintz/
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free!
>>http://photos.yahoo.com/
>
>_________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
>Share information about yourself, create your own public profile at 
>http://profiles.msn.com.

HWG: hwg-languages mailing list archives, maintained by Webmasters @ IWA