Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting MySQL Tables

    Hi, I need some help. I am trying to show in the admin panel how many tables there are in a database...

    My database is called paidtosi_crusad and I am wanting to count all of the tables in pts_ads and then return a number, with the amount, in a table.

    Please could someone guide me?

    Thanks

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,226
    Thanks
    23
    Thanked 606 Times in 605 Posts
    It is hard to know if you want to count the number of TABLES in your database or the number of ROWS in a table.
    This gives you both. The number of tables and then their names in your database.
    And then it gives you the number of rows in a table.
    PHP Code:
    <?php
    require ('./inc/DB_connect.php'); // LINK TO YOUR DATABASE HERE DON'T USE MINE

    $query "SHOW TABLES FROM paidtosi_crusad";

    $result    mysql_query($query);
    $num_of_tables mysql_num_rows($result);
    echo 
    $num_of_tables."<br />";
    while(
    $showtablerow mysql_fetch_array($result))
    {
        echo 
    $showtablerow[0]."<br />";
    }

    echo 
    "========================<br />";
    $result mysql_query("SELECT * FROM pts_ads");
    $num_rows mysql_num_rows($result);
    echo 
    "$num_rows Rows in pts_ads";
    ?>

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    If you are going to show the number of rows in one table, why not show the number of rows in *EACH* table???

    And this is a terrible way to find the number of rows:
    Code:
    $result = mysql_query("SELECT * FROM pts_ads");
    $num_rows = mysql_num_rows($result);
    If you had millions of records in that table, it would take a *LONG* time to run!

    So...
    Code:
    $query = "SHOW TABLES FROM paidtosi_crusad";
    $result    = mysql_query($query);
    $num_of_tables = mysql_num_rows($result);
    echo $num_of_tables."<br />";
    while($onetable = mysql_fetch_array($result))
    {
        $tblname = $onetable[0];
        $sql = "SELECT COUNT(*) FROM `" . $tblname . "`";
        $tresult = mysql_query($sql);
        $tcount = mysql_fetch_array($tresult);
        echo $tblname . ": " . $tcount . " records<br/>\n";
    }
    Remembering, please, that I don't use PHP. So I could well have a PHP typo in that.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,620
    Thanks
    78
    Thanked 4,388 Times in 4,353 Posts
    Another way to do this, if you'd like that names of the tables and, say, how many columns are in each table:
    Code:
    $sql = "SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*) AS columnCount "
         . " FROM information_schema.COLUMNS "
         . " GROUP BY TABLE_SCHEMA, TABLE_NAME " 
         . " ORDER BY TABLE_SCHEMA, TABLE_NAME ";
    $results = mysql_query( $sql );
    while($onetable = mysql_fetch_array($result))
    {
        $dbname = $onetable[0];
        $tblname = $onetable[1];
        $colcount = $onetable[2];
        echo $dbname . ".`$tblname . "`: " . $tcount . " columns<br/>\n";
    }
    The information_schema database contains many useful tables that will give you the META-DATA about your databases and tables.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hai...
    The sql coding worked properly... I have already worked the count coding...
    SELECT COUNT(*) FROM tablename WHERE user=5


  •  

    Posting Permissions

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