<?
//MySQL / PHP - Optimize MySQL Tables Script
//by BrightCherry
//www.brightcherry.co.uk

//db connection details
$host = "localhost";
$username = "root";
$password = "";
$db = "wordpress";

//connect to db
$db_connection = mysql_connect($host, $username, $password) or die("Could not connect to db");
mysql_select_db ($db, $db_connection) or die("Could not connect to table");

//get statuses for tables in db
$sql = "SHOW TABLE STATUS";
$result	= mysql_query($sql);

//initialize array
$tables = array();
while($row = mysql_fetch_array($result))
{
    // return the size in Kilobytes
    $table_size = ($row[ "Data_length" ] + $row[ "Index_length" ]) / 1024;
    $tables[$row['Name']] = sprintf("%.2f", $table_size);

    //get total size of all tables
    $total_size += round($table_size,2);

    // optimize tables
    $optimise_sql = "OPTIMIZE TABLE {$row['Name']}";
    $optimise_result = mysql_query($optimise_sql);
}

//get statuses for tables in db after optimization
$sql = "SHOW TABLE STATUS";

//initialize array
$optimised_tables = array();
$result	= mysql_query($sql);
while($row = mysql_fetch_array($result))
{
	// return the size in Kilobytes
	$table_size = ($row[ "Data_length" ] + $row[ "Index_length" ]) / 1024;
	$optimised_tables[$row['Name']] = sprintf("%.2f", $table_size);

	//get total size of all tables after optimization
	$optimise_total_size += round($table_size,2);
}
?>


<table width="600" border="1">
	<thead>
		<tr>
			<th>Table</th>
			<th>Size (KB)</th>
			<th>Optimised Size (KB)</th>
			<th>Optimised</th>
		</tr>
	</thead>
	<tbody>
	<?foreach($tables as $table => $size):?>
	<tr>
		<td><?=$table;?></td>
		<td><?=$size;?></td>
		<td><?=$optimised_tables[$table];?></td>
		<td>
			<?if($size > $optimised_tables[$table]):?>
				<?=$size - $optimised_tables[$table];?>
			<?endif;?>
		</td>
	</tr>
	<?endforeach;?>
	<tr>
		<td><b>Total</b></td>
		<td><b><?=$total_size;?></b></td>
		<td><b><?=$optimise_total_size;?></b></td>
		<td><b><?=round($total_size - $optimise_total_size,2);?></b></td>
	</tr>
	</tbody>
</table>