MySQL- how to list all fields of a table (or columns)

Howard_L · #1 07-Jul-2008, 01:48

Can you connect to your site and then query to find all databases availabel?
All tables in a database?
All fields in a table?
I have a tendency to forget things... it would be nice to be able to print out the whole tree if needed...
Thanks

TurboPT · #2 07-Jul-2008, 07:12

Quote:

Originally Posted by Howard_L

Can you connect to your site and then query to find all databases availabel?

Yes. query: SHOW databases;

Quote:

Originally Posted by Howard_L

All tables in a database?

SHOW tables FROM <database_name>

Quote:

Originally Posted by Howard_L

All fields in a table?

SHOW columns FROM <table_name>

Quote:

Originally Posted by Howard_L

I have a tendency to forget things... it would be nice to be able to print out the whole tree if needed...

I don't know about a tree-like command, but in case you might not know about it, there is a nifty [free] graphical tool to MySQL databases here. (see the demo link for screen shots -- also, further down the demo page are links where you can actually tinker with the tool!)
It's sort of an 'all-in-one' tool for database needs, designed as a web interface for remote DB administration.

Howard_L · #3 07-Jul-2008, 10:55

phpMyAdmin ... Yes, my hosting service provides that and it works nicely but I feel like it is cheating so am trying not to use it unless I'm at a loss!
I guess I should have said php/mysql rather than straight mysql.
But from your hints I am getting the idea of how the striaght mysql commands are implemented into the php functions.
After some fooling around I'm started to get somewhere:

PHP Code:


<?php  /* list-1.0.php  Exploring the listing of databases and their tables. */
/**** some vars ****/
    $user = "mysqlsiteusername";   
   $userp = "mysqlsitepassword";

/**** connect to database ****/
  echo "<br />";
  mysql_connect("localhost", $user, $userp)
    or die("  crud!... " . mysql_error());
  echo "Connected to MySQL. user: '". $user ."' <br />";

  $result = mysql_query("SHOW DATABASES");
  echo "result=>". $result ."< <br />";
  echo "<br />Printing available databases:  <br />";
  $i = 0;
  while($row = mysql_fetch_array( $result ))
  {
    echo $i++ .") ". $row[0] ."' <br />";
  }
  echo "<br />...(neato)...<br />";
?>

I think I can work this up to a pretty good listing...
Thanks!
Howard++;
ps: how come when I use the php code tags the code doesn't wrap at the newlines in the code box? I am using vi in FC6. utf8 problem?

TurboPT · #4 07-Jul-2008, 13:30

Quote:

Originally Posted by Howard_L

I think I can work this up to a pretty good listing...

Yep. That looks like the general direction.

Quote:

Originally Posted by Howard_L

ps: how come when I use the php code tags the code doesn't wrap at the newlines in the code box? I am using vi in FC6. utf8 problem?

I have seen that before too, but only with this forum's code tags. I'm only assuming it's related to the [php] parsing, as I haven't seen that strangeness with C/C++/Java/VB tags.
(look back at the cookie/time post -- the code block appears as expected now)

admin · #5 07-Jul-2008, 20:02

I'm sorry but yes this only happens when you paste PHP code examples in your replies through the "Quick Reply" box (but works fine if you click on the "Go Advanced" button before submitting the post). I don't know how to fix this yet.

Howard_L · #6 07-Jul-2008, 22:54

Quote:

click on the "Go Advanced" button before submitting the post

10-4 not a problem (if I can remember)

I'm at a pretty good stopping point on that listing tool.
Now I can refresh my memory on my database layout in a flash!
Here is a bit of the ouput:

Code:

******** Database (1)='mydb_test' ********
    $result2= 'Resource id #21'

. Table (0)='example'
Descriptions:   Field         Type  Null  Key  Default          Extra
. . Column (0)=    id      int(11)    NO  PRI           auto_increment
. . Column (1)=  name  varchar(30)   YES 
. . Column (2)=   age      int(11)   YES

...and here is the script:

PHP Code:


<?php
/* mysql_list-1.1.php    Lists sites databases, their tables and columns. */
/**** some vars ****/
   $user  = "myusername";
   $userp = "mypassword";

/**** connect to database ****/
  echo "<br />";
  mysql_connect("localhost", $user, $userp)
    or die("... poop! ... " . mysql_error());
  echo "Connected to MySQL. user: '". $user ."' <br />";

  $result = mysql_query("SHOW DATABASES");
  echo "result=>". $result ."< <br />";
  echo "<br />Printing available databases:  <br />";

  $i = 0;
  while($row = mysql_fetch_array( $result ))
  {
    echo "<br />******** Database (". $i++ .")='". $row[0] ."' ********<br />";
    $result2 = mysql_query("SHOW TABLES IN $row[0]");
      echo "&nbsp &nbsp \$result2= '". $result2 ."' <br />";
    $j = 0;
    while($row2 = mysql_fetch_array( $result2 ))
    {
      echo "<br />. Table (". $j++ .")='". $row2[0] ."'<br />";
      $result3 = mysql_query("SHOW COLUMNS FROM $row2[0] FROM $row[0] ");
      $k = 0;
      echo "<table rules=all frame=box cellpadding='2' >
            <tr>
               <td>Descriptions:</td>
               <td>Field</td>
               <td>Type</td>
               <td>Null</td>
               <td>Key</td>
               <td>Default</td>
               <td>Extra</td>
             </tr>";
      while($row3 = mysql_fetch_array( $result3 ))
      {  
        echo "<tr>
                 <td>. . Column (". $k++ .")=</td>
                 <td> ". $row3[0] ."</td>
                 <td> ". $row3[1] ."</td>
                 <td> ". $row3[2] ."</td>
                 <td> ". $row3[3] ."</td>
                 <td> ". $row3[4] ."</td>
                 <td> ". $row3[5] ."</td>
              </tr>";
      }
      echo "</table> <br />";
    }
  }
  mysql_close();
  echo "<br />...........(neato)...........<br />";
?>
/*
http://dev.mysql.com/doc/refman/4.1/en/show-columns.html

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

FULL gives even more info than we're already getting... (see link)

 Field   Type  Null  Key  Defaul1t  Extra
*/

Thanks for pointing me in the right direction
...and yes it looks like the php codebox is working OK after going to 'Advanced' first...
Howard++;

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Coding Help	sneakerhead724	Java Forum	4	09-Apr-2008 08:20
Str_Misaligned in Double Link List	Peter_APIIT	C Programming Language	1	29-Feb-2008 21:50
Airport Log program using 3D linked List : problem reading from file	batrsau	C Programming Language	11	29-Feb-2008 08:44
C++ class -- Please help	vnca_1	C++ Forum	3	14-Jun-2006 13:31

MySQL- how to list all fields of a table (or columns)

Re: MySQL- how to list all fields of a table (or columns)

Re: MySQL- how to list all fields of a table (or columns)

Re: MySQL- how to list all fields of a table (or columns)

Re: MySQL- how to list all fields of a table (or columns)

Re: MySQL- how to list all fields of a table (or columns)