Last updated on March 31st, 2022 at 10:26 am
This is a simple tutorial that shows how to get the size of the MySQL database using php. This works in all latest PHP versions. We are using mysqli_connect function.
The query we are running to find the size of the database is SHOW TABLE STATUS
We are getting the size from Data_length and Index_length columns from the tables inside the database and adding them up.
All you have to do is update these lines in the main script.
1] Credentials to connect to database
$objConnect = mysqli_connect("localhost","","") or die("Error Connecting to Database,
<hr>Known issues:Database may be down or need to contact admin");
2] Name of the database you would like to check the size
$dbname = '';
Complete code
<?php
//connect to DB
$objConnect = mysqli_connect("localhost","root","") or die("Error Connecting to Database,
<hr>Known issues:Database may be down or need to contact admin");
$dbname = 'mytestdatabase';
function getdbsize( $data ) {
// bytes
if( $data < 1024 ) {
return $data . " bytes";
}
// kilobytes
else if( $data <1024000 ) {
return round( ( $data / 1024 ), 2 ) . "KB";
}
// megabytes
else {
return round( ( $data / 1024000 ), 2 ) . " MB";
}
}
mysqli_select_db($objConnect,$dbname);
$result = mysqli_query($objConnect,"SHOW TABLE STATUS");
$dbsize = 0;
while( $row = mysqli_fetch_array($result ) ) {
$dbsize += $row[ "Data_length" ] + $row[ "Index_length" ];
}
echo "The size of the database is ".getdbsize($dbsize)."<p>This is a size of a sample database we have";
?>
In the demo we have a small database that we are using to show you the size. It is the same size that phpmyadmin report for the database we are connecting see the screenshot below
admin