Get Information About A MySQL Database With PHP

11th June 2008 - 4 minutes read time

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.

PHP
MySQL

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
6 + 6 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Recreating Spotify Wrapped In PHP

17th March 2024

I quite like the end of the year report from Spotify that they call "Wrapped". This is a little application in which they tell you what your favorite artist was and what sort of genres you listened to the most during the year.

Read more

Should A Constructor Throw An Exception?

18th February 2024

Let's say you had a class that you wanted to use, but there was some sort of error in creating the object. This might be that the wrong parameters were passed, or the third party service (eg. a database) wasn't available at the time of creation.

Read more

Solving Query Performance Problems With The MySQL Slow Query Log

21st January 2024

MySQL's slow query log is a key component in your MySQL administration setup. Whilst normal logging can help you in terms of tracking down issues with your database system, the slow query log can help you track down issues in your database setup before they become problematic.

Read more

PHP Question: Variable Reference

10th December 2023

Question

What does the following code print out?

function arrayPrint($array)
{
   echo implode(' ', $array);
}

$arrayA = [1, 2, 3];
$arrayB = $arrayA;
$arrayB[1] = 0;
arrayPrint($arrayA);

Read more

Creating An Authentication System With PHP and MariaDB

15th October 2023

Using frameworks to handle the authentication of your PHP application is perfectly fine to do, and normally encouraged. They abstract away all of the complexity of managing users and sessions that need to work in order to allow your application to function.

Read more

Creating Sparklines In PHP

17th September 2023

A sparkline is a very small line graph that is intended to convey some simple information, usually in terms of a numeric value over time. They tend to lack axes or other labels and are added to information readouts in order to expand on numbers in order to give them more context.

Read more