Get Information About A MySQL Database With PHP

By using the MySQL command.

SHOW TABLE STATUS FROM database;

You can get all sorts of information about a database. The query returns each table as a row and gives lots of information about each table. Using this query it is possible to work out some usage data for the database as a whole. The following function will take a database name and a database resource handle and return how big that database is, the number of tables, and the number of rows in those tables. You will probably have a maximum limit to the amount of data that you can store in your database, so this function is useful to make sure that you don't exceed this limit. This function also uses another function found on the #! code site called readableFileSize() to give more meaningful data sizes.

function getDatabaseInfo($database, $db, $readableSizes=true) {
 // set default return values
 $size = 0;
 $dataLen = 0;
 $indexLen = 0;
 $rows = 0;
 $tables = 0;
 // run SQL
 $result = mysql_query('SHOW TABLE STATUS FROM '.$database.';');
 if ( $result ) {
  // if result exsits then work out data values
  $tables = mysql_num_rows($result);
  while ( $data = mysql_fetch_assoc($result) ) {
    $size += ($data['Index_length'] + $data['Data_length']);
    $dataLen += $data['Index_length'];
    $indexLen += $data['Data_length'];
    $rows += $data['Rows'];
  };
 };
 // return
 if ( $readableSizes ) {
  return array('size'=>readableFileSize($size),'rows'=>$rows,'index-length'=>readableFileSize($indexLen),'data-length'=>readableFileSize($dataLen),'tables'=>$tables);
 } else {
  return array('size'=>$size,'rows'=>$rows,'index-length'=>$indexLen,'data-length'=>$dataLen,'tables'=>$tables);
 };
}

To run this function just connect to the database and run the function, passing the database name and the connection resource.

// open mysql connection
$handle = mysql_connect('localhost', 'root', 'wibble');
 
if(!$handle){
 die('Connection failed!');
}
 
echo '<pre>'.print_r(getDatabaseInfo('wordpress', $handle,false),true).'</pre>';

This prints the results.

Array
(
 [size] => 1835008
 [rows] => 1805
 [index-length] => 1277952
 [data-length] => 557056
)

Just to check I had a look at what the MySQL administrator application tells me about the database structure and found that they were the same.