<?php
// database viewer and total index counter. Code by Pekka Saarinen 2007 http://photography-on-the.net
// parts of code borrowed from Exhibit Engine 2 (by permission :) ).
error_reporting(255);
if (!function_exists("mysql_connect")) {
print "<p>FATAL ERROR: MySQL support not installed for PHP!";
}
$servername = "localhost";
$username = "mysql_user_name";
$password = "mysql_user_pass";
$ee_mysql_connection = @mysql_connect($servername, $username, $password);
if (!$ee_mysql_connection) {
print "cannot connect";
exit();
}
?>
<html><body>
<title>count</title>
<style type="text/css" media="screen"> <!--
SAMP {
font-style: normal;
}
IMG {
text-decoration: none;
color: #000000;
}
A {
text-decoration: none;
color: #0000ff;
}
A:hover {
text-decoration: underline;
color: #007700;
}
BIG {
font-size: 18px;
font-style : normal;
font-family: arial,helvetica,sans-serif;
font-weight : bold;
}
H2 {
font-size: 19px;
font-style : normal;
font-family: arial,helvetica,sans-serif;
}
H2 A {
font-size: 19px;
font-style : normal;
font-family: arial,helvetica,sans-serif;
color: #000000;
}
P,LI,UL,FORM,TD,BLOCKQUOTE {
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 9px;
}
option {
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 10px;
background-color : #fcf7ed;
}
SELECT {
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 10px;
background-color : #eeeeee;
}
INPUT,TEXTAREA {
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 10px;
}
FORM {
margin-bottom : 0px;
margin-top : 0px;
}
BODY {
background: #ffffff;
color: #000000;
margin-left : 10px;
margin-top : 10px;
margin-right : 10px;
margin-bottom : 10px;
font-family: arial,helvetica,sans-serif;
font-size: 11px;
background-image : none;
background-position : right;
background-repeat : no-repeat;
background-attachment : fixed;
background-color : White;
}
-->
</style>
<?php
function ee_humanfilesize_accurate($size,$dec=2,$disp=1) {
// function originated from http://fi.php.net/manual/en/function.filesize.php
$kb = 1024;
$mb = 1024 * $kb;
$gb = 1024 * $mb;
$tb = 1024 * $gb;
if ($size < $mb) {
if ($disp == 1) {
$abbr = " KB";
}
return round($size/$kb,$dec)."{$abbr}";
}
else {
if ($disp == 1) {
$abbr = " MB";
}
return round($size/$mb,$dec)."{$abbr}";
}
return;
}
$total_datasize_all = 0;
$total_indexsize_all = 0;
$table_count_all = 0;
$ee_main_database_list = array();
$show = mysql_query("
SHOW DATABASES
");
if ($show) {
while ($showstats = mysql_fetch_array($show)) {
$ee_main_database_list[] = $showstats[0];
}
}
foreach ($ee_main_database_list as $key => $databasename) {
if ($databasename == "mysql") {
continue;
}
if ($databasename == "information_schema") {
continue;
}
mysql_select_db($databasename);
print "<p><big>{$databasename}</big></p>";
$ee_main_table_list = array();
$show = mysql_query("
SHOW TABLES
");
if ($show) {
while ($showstat = mysql_fetch_array($show)) {
$ee_main_table_list[] = $showstat[0];
}
}
$table_count = count($ee_main_table_list);
$table_count_all = $table_count + $table_count_all;
print "<table border=\"0\" cellpadding=\"1\" cellspacing=\"0\"><tr><td nowrap><b>Table</b></td><td> </td><td nowrap><b>Rows</b></td><td> </td><td nowrap><b>Data length</b></td><td> </td><td nowrap><b>Index lenght</b></td><td> </td><td nowrap><b>Total Lenght</b></td><td> </td><td nowrap><b>Overhead</b></td><td> </td><td nowrap><b>Last Updated</b></td><td> </td><td nowrap><b>Status</b></td><td> </td><td nowrap><b>Comment</b></td>";
$rowcolor = 0;
$total_datasize = 0;
$total_indexsize = 0;
$total_backupsize = 0;
$columns_list = array();
foreach ($ee_main_table_list as $key => $k) {
$bgcolor = "#cdddde";
($rowcolor % 2) ? (0) : ($bgcolor = "#ffffff");
$checked_status = "";
$totalsize = 0;
$table_rows = 0;
$table_bytesize = 0;
$index_bytesize = 0;
$table_comment = "";
$data_free = 0;
$mismatch = FALSE;
$last_updated = "n/a";
$status = mysql_query("
SHOW TABLE STATUS FROM `{$databasename}` LIKE '{$k}'
");
if ($status) {
while ($stat = mysql_fetch_array($status)) {
$table_rows = @$stat["Rows"];
$table_bytesize = @$stat["Data_length"];
$index_bytesize = @$stat["Index_length"];
$last_updated = @$stat["Update_time"];
$table_comment = @$stat["Comment"];
$data_free = @$stat["Data_free"];
$totalsize = $table_bytesize+$index_bytesize;
}
}
if (!$status) {
$totalsize = 0;
}
$checked_status = "---";
/* remove for table status check
$check = mysql_query("
CHECK TABLE {$k}
");
if ($check) {
while ($checkstat = mysql_fetch_array($check)) {
$Msg_type = $checkstat["Msg_type"];
$Msg_text = $checkstat["Msg_text"];
}
$checked_status = $Msg_type . ": " . $Msg_text;
}
if (!$check) {
$checked_status = "---";
}
remove for table status check */
$total_datasize = $total_datasize+$table_bytesize;
$total_indexsize = $total_indexsize+$index_bytesize;
print "<tr bgcolor=\"{$bgcolor}\"><td nowrap><b>";
print $k;
print "</b></td><td> </td><td nowrap>";
print $table_rows;
print "</td><td> </td><td nowrap>";
print ee_humanfilesize_accurate(intval($table_bytesize));
print "</td><td> </td><td nowrap>";
print ee_humanfilesize_accurate(intval($index_bytesize));
print "</td><td> </td><td nowrap>";
print ee_humanfilesize_accurate(intval($totalsize));
print "</td><td> </td><td nowrap>";
print ee_humanfilesize_accurate(intval($data_free));
print "</td><td> </td><td nowrap>";
print $last_updated;
print "</td><td> </td><td nowrap>";
print $checked_status;
print "</td><td> </td><td nowrap>";
print $table_comment;
print " </td>";
$rowcolor++;
}
?>
</tr></table></td>
</tr></table>
<?php
$total_datasize_all = $total_datasize + $total_datasize_all;
$total_indexsize_all = $total_indexsize + $total_indexsize_all;
print "<br><b>TOTALS</b>";
print "<br>Table data size: " . ee_humanfilesize_accurate(intval($total_datasize));
print "<br>Index (key) size: " . ee_humanfilesize_accurate(intval($total_indexsize));
?>
</td></tr></table>
<br><br><br>
<?php
}
$indexes = 0;
$indexes = intval($total_indexsize_all);
print "<hr><big>TOTALS</big>";
print "<br>Table data size: " . ee_humanfilesize_accurate(intval($total_datasize_all));
print "<br>Index (key) size: " . ee_humanfilesize_accurate($indexes,2);
print "<br>Tables: " . $table_count_all;
print "<span style=\"color:#ff0000;\">";
print "<br><br><strong>Recommendations for my.cnf:</strong> ";
print "<br>table_cache: " . $table_count_all*8 . " + headroom";
print "<br>key_buffer_size: " . ee_humanfilesize_accurate($indexes,0,0) . "M + headroom";
print "</span>";
print "<hr><br><br><br><br><br>";
?>
</body></html>
