mysql_table size

p0pb0b

Hi
I wanted to know if there was any mysql functions for php where I can show the status of a mysql db table such as Size. Thanks for any help

seby

this will show you the size in Megabytes, if you want Kilobytes instead change

number_format(($total[size]/1024/1024),2, '.', ''). 'MB';

to:

number_format(($total[size]/1024),2, '.', ''). 'KB';

untested, but try it out:

	$result =  mysql_query("SELECT SUM(LENGTH(column)) AS size FROM table");
	$total   =  mysql_fetch_array($result);
    echo number_format(($total[size]/1024/1024),2, '.', ''). 'Mb';

p0pb0b

Nope.. that did not work.. I got .00 MB and even after I took off those two divisions by 1024.. it still gave me .00 which I know I have more bytes then that.. thanks though
I tried to use mysql_fetch_lengths do you know how that works?

seby

ok i know why... because you want to check the size of an entire table, not a column right?

what i posted works because i have tested it, but it will give you the size of a column, not a whole table... give me a few while i put together that query..

p0pb0b

Yea I want size of a whole table
Thanks

p0pb0b

do I still use the SUM function?

seby

This works, i've tested it to make sure.. just edit the two variables at the top..

$table = 'your_table';
$dbase = 'your_database';

$results =  mysql_query("SELECT VERSION() AS version");
$version =  mysql_fetch_array($results);

$indexsize = 0;
$datasize = 0;
if ($version['version'] >= '3.23')
{
	$tables = mysql_query("SHOW TABLE STATUS FROM $dbase LIKE '$table'");

		while ($table = mysql_fetch_array($tables))
		{
			$datasize += $table['Data_length'];
			$indexsize += $table['Index_length'];
		}
		if (!$indexsize)
		{
			$indexsize = 'n/a';
		}
		if (!$datasize)
		{
			$datasize = 'n/a';
		}
	}
	else
	{
		$datasize = 'n/a';
		$indexsize = 'n/a';
	}

	   echo 'Table size: ' .number_format(($datasize/1024),2, '.', ''). "&nbsp; KB <br />";
       echo 'Index size: ' .number_format(($indexsize/1024),2, '.', ''). "&nbsp; KB <br />";