Repairing MySql Databases with PHP – Troubleshooting and Code

I’ve been playing around with the code from the previous tutorial (Is it possible to repair mysql databases with php?) and came up with a function that repairs a mysql database. I’m working on storing the database in an XML document, and importing it into PHP.

Here’s the code for the function ‘Troubleshoot()’ inside Database:

static public function Troubleshoot() {
		//make a list of all tables in the database
		$TABLES = Array();
		$table_list = self::Query("show tables");
		while($row = self::Fetch($table_list)) {
			array_push( $TABLES, $row['Tables_in_database'] );
		}

		//check for tables that don't exist in the mysql database and create
		foreach(self::$STRUCTURE as $key => $index) {
			if( !in_array( $key, $TABLES ) ) {
				echo "'{$key}' doesn't exist! Creating... ";
				if($create_query = Database::GetCreateQuery($key)) {
					Database::Query($create_query);
					echo "Table created!";
				} else {
					echo "Failed to create table.";
				}
			}
		}
		echo "
";
		//repair currently existing tables
		foreach($TABLES as $name) {
			if( array_key_exists($name, self::$STRUCTURE) ) {
				echo "Troubleshooting table '{$name}' : {
";
				$table_structure_query = self::Query( "describe {$name}" );
				$i = 0; while($table_structure = self::Fetch($table_structure_query)) {
					$table_row = self::$STRUCTURE[$name][$i];

					//Convert some of the table schemas to the easier to use ones stored in self::$STRUCTURE
					$table_structure['Null'] = ( ($table_structure['Null'] == 'NO') ? 'not null' : 'null' );
					$table_structure['Key']  = ( ($table_structure['Key'] == 'PRI') ? "PRIMARY KEY({$table_structure['Field']})" : '' );

					//check the table schema matches
					if(
					$table_structure['Field'] == $table_row['field'] &&
					$table_structure['Type']  == $table_row['type'] &&
					$table_structure['Null']  == $table_row['null'] &&
					$table_structure['Key']   == $table_row['primary key'] &&
					$table_structure['Extra'] == $table_row['extra']
					) {
						echo "
The table '{$table_structure['Field']}' is intact and built properly.

";
					} else {
						echo "
The row '{$table_structure['Field']}' is corrupted. Attempting to repair...";
						if(self::RepairTable($name)) echo "Repaired!";
						echo "

";
					}

					$i ++;
				}
				echo "}

";
			}
		}
	}

I’ll edit this all another day to make it more comprehensible. Right now, though, it’s 3am and I’m too tired to concentrate.