I want to know the size of my database using php. How to display the size in megabytes entire database? Size in megabytes a specific request?
Add a comment
|
5 Answers
Try this to get the size in bytes:
mysql_select_db("yourdatabase");
$q = mysql_query("SHOW TABLE STATUS");
$size = 0;
while($row = mysql_fetch_array($q)) {
$size += $row["Data_length"] + $row["Index_length"];
}
then to convert in megabytes:
$decimals = 2;
$mbytes = number_format($size/(1024*1024),$decimals);
Comments
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Comments
I try to modify from internet source for this case, just try it please.
<?php
mysql_connect("localhost","root","password here");
$namadb=''; //put db name here
$sql="SELECT table_schema 'db_name', SUM( data_length + index_length) / 1024 / 1024 'db_size_in_mb' FROM information_schema.TABLES WHERE table_schema='$namadb' GROUP BY table_schema ;";
$query=mysql_query($sql);
$data=mysql_fetch_array($query);
print $data['db_size_in_mb'];
Comments
This query give you a useful data about your database size:
SELECT
TABLE_SCHEMA AS DB_Name,
count(TABLE_SCHEMA) AS Total_Tables,
SUM(TABLE_ROWS) AS Total_Tables_Row,
ROUND(sum(data_length + index_length)/1024/1024) AS "DB Size (MB)",
ROUND(sum( data_free )/ 1024 / 1024) AS "Free Space (MB)"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
GROUP BY TABLE_SCHEMA ;
Comments
Try querying information_schema.