SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    list table names and row counts in a database

    Hi,

    I am trying to list all the tables with their row counts in a databse... I have been racking my brain on how to do this. So far I have figured out that there is a function mysql_list_tables(), however, this is depreciated and should not be used. So this is my code so far, but I have no idea on how to include my while statments to output a list like so

    Code:
    TABLE NAME  COUNT
    table1           1204
    table2           1053
    table3           5042
    PHP Code:
    <?php
    include ("config.php");
    if (!
    mysql_connect('$host''$user''$pass')) {
    ** echo 
    'Could not connect to mysql' mysql_error;
    ** exit;
    }
    $result=mysql_query('SHOW TABLES FROM $db') or die(mysql_error);
    ?>

  2. #2
    SitePoint Enthusiast Redprince's Avatar
    Join Date
    May 2004
    Location
    Salzgitter, Lower Saxony, Germany
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Should be pretty easy:
    - get all tables with 'SHOW TABLES'
    - loop over the tables from step #1
    - "SELECT COUNT(*) FROM foobar", where 'foobar' is the current table name
    - show the data
    I am not part of the allesburner. I am the allesburner.

  3. #3
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm very close to getting this one, however, I can figure out what to add here...

    I need the names of the tables in the database as well as each row count... so far this is what I got...

    I believe the problem is figuring out what the row-> value is supposed to be?

    PHP Code:
    <?php
    include ("config.php");

    if (!
    mysql_connect("$host""$user""$pass")) {
        echo 
    'Could not connect to mysql' mysql_error();
        exit;
    }
    $result=mysql_query("SHOW TABLES FROM $db") or die(mysql_error());
    while (
    $row mysql_fetch_object($result)) {
        
    $num_rows mysql_num_rows($result);
        echo 
    $row->$db;
        echo 
    " has $num_rows Rows\n";
    }
    mysql_free_result($result);
    ?>

  4. #4
    SitePoint Enthusiast Redprince's Avatar
    Join Date
    May 2004
    Location
    Salzgitter, Lower Saxony, Germany
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    mysql_connect()
    mysql_select_db()
    $db_result = mysql_query("SHOW TABLES");
    while($table = mysql_fetch_row($db_result))
    {
      $db_count = mysql_query(("SELECT COUNT(*) AS `cnt` FROM `" . $table[0] . "`"));
      $cnt = mysql_fetch_assoc($db_count);
      echo "Table '" . $table[0] . "' has " . $cnt['cnt'] . " rows!<br />";
    }
    mysql_close()
    Not tested but should work.
    I am not part of the allesburner. I am the allesburner.

  5. #5
    Resistance is Futile webgodjj's Avatar
    Join Date
    Nov 2002
    Location
    Madison, WI USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a bunch... this worked out great!

    Cheers!

  6. #6
    SitePoint Evangelist Aska's Avatar
    Join Date
    Aug 2003
    Location
    Melbourne, Australia
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's also a very useful query: "show table status" which gives you the table name, type, rows, data_length, max_data_length, index_length, auto_increment, create/update time etc.

    And did I mention it's pretty fast as well?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •