List:General Discussion« Previous MessageNext Message »
From:Warren Windvogel Date:July 11 2008 11:24am
Subject:Re: Count total number of records in db
View as plain text  
Radoulov, Dimitre wrote:
> mysql -NBe'show databases' |
>  while IFS= read -r db; do
>    printf "show tables from %s;\n" "$db" |
>      mysql -N | while IFS= read -r t; do
>        printf "select count(1) from %s.%s;\n" "$db" "$t"
>      done
>  done | mysql -N |
>    awk '{ s += $1 }END{ print s }'

I quickly put together a PHP script to do it. Its dirty( purpose built 
:-) ) but it works.

<?php

//EDIT YOUR MySQL Connection Info:
$DB_Server = "localhost";        //your MySQL Server
$DB_Username = "root";         //your MySQL User Name
$DB_Password = "";        //your MySQL Password
$DB_DBName = "DBName";            //your MySQL Database Name

//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
   or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . 
mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
   or die("Couldn't select database:<br>" . mysql_error(). "<br>" . 
mysql_errno());

$tables = mysql_list_tables($DB_DBName);
$count = 0;
$total_rows = 0;

while ($count < mysql_numrows($tables)) {
     $table_name = mysql_tablename($tables,$count);
     $sql = 'SELECT COUNT(*) FROM '.$table_name;
     $result = mysql_query($sql);
     $table_count = mysql_fetch_row($result);
     $table_count = $table_count[0];
     $total_rows = $total_rows + $table_count;
     echo 'Number of rows in <i>'.$table_name.'</i> = 
'.$table_count.'</br>';
     $count++;
}
echo '</br>';
echo '<b>Total number of rows in database: </b>'.$total_rows;

?>

Thanks guys.
Warren
-- 
Open Source Developer
Business Data Solutions
Email: warren.windvogel@stripped
Gmail: wwindvogel
MSN: wwindvogel
Skype: wwindvogel
Cell: 27 73 264 6700
Tel: 27 21 487 2177
Thread
Count total number of records in db Warren Windvogel11 Jul
  • Re: Count total number of records in dbMarisRuskulis11 Jul
    • Re: Count total number of records in dbDimitre Radoulov11 Jul
      • Re: Count total number of records in dbWarren Windvogel11 Jul
        • Re: Count total number of records in dbRob Wultsch12 Jul