Is it possible to repair MySql Databases using PHP?

Why yes, yes it is.

Here I am attempting to devise a method of storing the structure of a MySql database and it’s individual tables in PHP. So far, it looks like this:

public static $STRUCTURE = Array(
	'members' => Array(
		Array(
			'field' => 'id',
			'type' => 'int(11)',
			'null' => 'not null',
			'extra' => 'auto_increment',
			'primary key' => ', PRIMARY KEY(id)',
			'suffix' => ','
		),
		Array(
			'field' => 'member_name',
			'type' => 'varchar(32)',
			'null' => 'not null',
			'suffix' => ','
		),
		Array(
			'field' => 'member_pass_hash',
			'type' => 'varchar(32)',
			'null' => 'not null'
		)
	)
);

This may look complicated and overkill – however, this could easily be loaded from an ini file, another php file, and updated from the mysql database itself (with the latter being the easiest option).

Updating the structure from the database can obviously only be done when the database is functioning perfectly. This way, the array self::$STRUCTURE can be used to repair the database and to create queries. For example, the query:

CREATE TABLE members( id INT NOT NULL AUTO_INCREMENT , PRIMARY KEY(id) );

This query could easily be generated by using the following structure:

private static $STRUCTURE = Array(
	'members' => Array(
		Array(
			'field' => 'id',
			'type' => 'int(11)',
			'null' => 'not null',
			'extra' => 'auto_increment',
			'primary key' => ', PRIMARY KEY(id)'
		)
	)
);

A function could be devised to transform this table structure into a mysql query. Something like this:

static public function GetCreateQuery($table) {
	$structure = self::GetTableStructure($table);
	if($structure != 0) {
		$query = "CREATE TABLE " . $table . "( ";
		foreach($structure as $index) {
			$query .= implode(' ', $index) . " ";
		}
		$query .= ");";
		return $query;
	} else {
		return false;
	}
}

This function should return a valid MySql query if set up properly. If you run the query in your MySql panel, it creates the correct table with primary key and extra values. It should be easy from here to design a system that can troubleshoot its own MySql tables. For example, to repair a table, a query like this could be used (but obviously altered):

CREATE TABLE new_members_table(id int(11) not null auto_increment);
INSERT INTO new_members_table SELECT * FROM old_members_table;
DROP TABLE old_members_table;
RENAME TABLE new_members_table TO old_members_table;

Notice that the structure of the table created is the same as was created by our ‘GetCreateQuery’ function, and was structured by the self::$STRUCTURE['members'] array.

Using this could improve your PHP blogging system, forum software, commenting software.. any kind of web software which uses PHP and MySql databases – so there is no limit to its use!

Also, as an extension, I give you my code for the function 'Troubleshoot' inside Database:

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 "}

"; } } }

Hopefully you'll learn something from this.

AZForums: Is it possible to repair MySql Databases using PHP? - AZForums