Web Analytics Made Easy -
StatCounter MYSQL: How to export ONLY tables, and field names. Nothing else - CodingForum

Announcement

Collapse
No announcement yet.

MYSQL: How to export ONLY tables, and field names. Nothing else

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSQL: How to export ONLY tables, and field names. Nothing else

    Is there a way to export a list of all the tables and fields from mysql? I have a LARGE program that I would like to export the tables with the fields in them categorized as they are in the database. The reason, I would like to find out how the program is structured. Is there a program out there that will do this? Or can phpmyadmin do this? I just want the tables and fields exported (not the coding, nor mysql coding).

    I basically only want the table and field names.. Don't care for the NULL's, VARCHAR and sizes, etc.. Just want to export the structure itself.
    So if there was a table in the database called table1 with fields field1, field2, field3, & field4. And one called table2 with fields field5, field6, field7, & field8.
    Then It would export and show there are 2 tables called table1 and table 2. And in table 1 is ..... fields. and table2 is .... fields. (no NULL, No varchar, no sizes..)
    I tried PhpMyAdmin but it also exports all the data like the VARCHAR, INT, NULL, etc.. I just want the tables and the field names that go with them.

    Is there a program out there that will be Exactly this? or can PhpMyAdmin some how do just this?

  • #2
    Check the sticky on top of this forum.

    In your phpmyadmin export-tab, you can stecify if you want data and/or structure.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment


    • #3
      Originally posted by raf
      Check the sticky on top of this forum.

      In your phpmyadmin export-tab, you can stecify if you want data and/or structure.
      It still exports all the other field stuff like NULL, NOT NULL, VARCHAR, INT,ETC.. I just want the tables and field names.

      Comment


      • #4
        I see. Didn't read it carefully enough. Sorry
        If you only need a list of tables and their column, then you best run a little PHP script

        With mysql_list_tables you get all tables, and if you loop through that recordset, you can use mysql_field_name to get all the columnnames.
        PHP Code:
        //first your connectionstrings, then this

        $result mysql_list_tables($yourdbname);
        while (
        $rowtable mysql_fetch_row($result)) {
             echo 
        '<br /><br />Table: ' $row[0];
           
        $res mysql_query("select * from " $row[$row[0]] . " LIMIT 1"$link); //$link = linkidetifier of your connection. Can be dropped.
           
        for ($i 0$i mysql_num_fields($res); $i ++){
              echo 
        '<br />' mysql_field_name($res$i)
           } 
           
        mysql_free_result($res);
        }
        mysql_free_result($result); 
        Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

        Comment


        • #5
          I have 2 errors:

          Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/camaro92/public_html/query.php on line 9

          Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/camaro92/public_html/query.php on line 17



          from

          PHP Code:
          <?php
          // create connection
          $conn mysql_connect("localhost","DATABASE","PASSWORD") or die(mysql_error());

          // select database
          $db mysql_select_db("DATABASE"$conn) or die(mysql_error());

          $result mysql_list_tables($yourdbname);
          while (
          $rowtable mysql_fetch_row($result)) {   
               echo 
          '<br /><br />Table: ' $row[0];
             
          $res mysql_query("select * from " $row[$row[0]] . " LIMIT 1"$link); //$link = linkidetifier of your connection. Can be dropped.
             
          for ($i 0$i mysql_num_fields($res); $i ++){
                echo 
          '<br />' mysql_field_name($res$i);
             } 
             
          mysql_free_result($res);
          }
          mysql_free_result($result);

          ?>
          LINE 9 : while ($rowtable = mysql_fetch_row($result)) {
          LINE 17 : mysql_free_result($result); (last one)

          Comment


          • #6
            That is probably because you should have replaced the "$yourdbname" inside

            $result = mysql_list_tables($yourdbname);

            with your actual db-name you want to see the tables/columns from. Like

            $result = mysql_list_tables("DATABASE");


            And because this isn't a real operational script (more something you'll run once or twise) i didn't include any checks to see if the returned recordsets are empty or not. Since you have a nonexisting value for the db-name (the $yourdbname is empty), you get these warnings. (not errors,since the parsing is OK, but there is nothing to process/free so it throws a warning).

            <edit> i now also see a few mistakes. The while loop-code should be
            PHP Code:
            while ($rowtable mysql_fetch_row($result)) {   
                 echo 
            '<br /><br />Table: ' $rowtable[0];
               
            $res mysql_query("select * from " $rowtable[0] . " LIMIT 1"$link); //$link = linkidetifier of your connection. Can be dropped.
               
            for ($i 0$i mysql_num_fields($res); $i ++){
                  echo 
            '<br />' mysql_field_name($res$i);
               } 
               
            mysql_free_result($res);

            (need coffee !)
            </edit>
            Last edited by raf; Feb 24, 2004, 07:21 AM.
            Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

            Comment


            • #7
              Still getting the same errors

              Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/camaro92/public_html/query.php on line 10

              Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/camaro92/public_html/query.php on line 18


              PHP Code:

              <?php
              // create connection
              $conn mysql_connect("localhost","DATABASE","PASSWORD") or die(mysql_error());

              // select database
              $db mysql_select_db("DATABASE"$conn) or die(mysql_error());

              $result mysql_list_tables($DATABASE);

              while (
              $rowtable mysql_fetch_row($result)) {   
                   echo 
              '<br /><br />Table: ' $rowtable[0];
                 
              $res mysql_query("select * from " $rowtable[0] . " LIMIT 1"$link); //$link = linkidetifier of your connection. Can be dropped.
                 
              for ($i 0$i mysql_num_fields($res); $i ++){
                    echo 
              '<br />' mysql_field_name($res$i);
                 } 
                 
              mysql_free_result($res);
              }
              mysql_free_result($result);

              ?>
              line 10 is still the while line
              line 18 is still the last mysql_free_results($result);

              I of course replaced the real database name with DATABASE and the real password with PASSWORD (obviously not going to post the real info online) ;-)

              Comment


              • #8
                $result = mysql_list_tables($DATABASE);
                is wrong --> you have inserted a variable inthere, that yu did not give any value. So you basically replaced one empty variable by another.

                if 'DATABASE' is the actual name then your code should either be
                PHP Code:
                $result mysql_list_tables('DATABASE'); 
                or
                PHP Code:
                $DATABASE 'DATABASE';
                $result mysql_list_tables($DATABASE); 
                Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                Comment

                Working...
                X