Prüfe ob Tabelle existiert und bestimmte Felder vorhanden sind, ggfl. ergänzen – ASDW

Prüfen ob eigene Felder in einer bestimmten Tabelle vorhanden sind und ggfls. hinzufügen – asdw_check_table_columns

In modular aufgebauten Programmen kommt es immer wieder vor, dass optionale Module bestimmte Felder / Tabellen benötigen, die sonst im System nicht vorhanden sind.

Statt diese Prüfung bei jeder Installation / jedem Update manuell durchzuführen oder externe Programme einzusetzen, kann dies beim Aufruf des Moduls mit dieser Funktion geprüft werden. Fehlende Tabellen und/oder Felder werden dann automatisch angelegt.

Die benötigte Tabelle und die Felder werden dazu in zwei Arrays definiert. Hier ein Beispiel:

Array für Tabelle:

Array für Felder:

Code für den Aufruf der Funktion mit diesen Beispiel-Daten:

$my_table = array(
		'name'=>'TEST', 
		'comment'=>'This is a test table to be deleted if possible.',
		'engine'=>'MyISAM',
		'collate'=>'utf8_general_ci');

$my_columns = array();
$my_columns[] = array(
		'name'=>'Feld_1',
		'type'=>'text',
		'collate'=>'utf8_general_ci',
		'null'=>1,
		'comment'=>'third column at the end');
$my_columns[] = array(
		'name'=>'Feld_2',
		'type'=>'int',
		'comment'=>'second column at the end',
		'after'=>'id',
		'default=>5);

asdw_print_r($my_table);

asdw_print_r($my_columns);
exit;
$my_erg =asdw_check_table_columns($my_table,$my_columns);

echo "<br><br>Result: " . $my_erg;

$my_table = array(

'name'=>'TEST',

'comment'=>'This is a test table to be deleted if possible.',

'engine'=>'MyISAM',

'collate'=>'utf8_general_ci');

$my_columns = array();

$my_columns[] = array(

'name'=>'Feld_1',

'type'=>'text',

'collate'=>'utf8_general_ci',

'null'=>1,

'comment'=>'third column at the end');

$my_columns[] = array(

'name'=>'Feld_2',

'type'=>'int',

'comment'=>'second column at the end',

'after'=>'id',

'default=>5);

asdw_print_r($my_table);

asdw_print_r($my_columns);

exit;

$my_erg =asdw_check_table_columns($my_table,$my_columns);

echo "<br><br>Result: " . $my_erg;

Funktion zum Prüfen und ggfls. Einfügen der Tabelle und Felder:

function asdw_check_table_columns($my_table,$my_columns)
	{
		// this function checks if $my_table[] exists (and if not, generates it)
		//   + checks if all columns in $my_columns[][] exist (and if not, adds them)
	
		asdw_echo("Check if table " . $my_table['name'] . " exists...");
		$my_sql = "SHOW TABLES LIKE '" . $my_table['name'] . "'";
		sc_select(my_data, $my_sql);
		if ({my_data} === false)
			{
				asdw_error ( "asdw_check_table_columns - Access error. Message =". {my_data_erro} . " - " . $my_sql);
			}
		else
			{
				if (!$my_data->EOF)
					{
						// Exists
						asdw_echo("Table " . $my_table['name'] . " already exists");
					}
				else
					{	// Create
						asdw_echo( "Table " . $my_table['name'] . " does not exist > create with id and primary index");

						if (!array_key_exists('comment',$my_table))  { $my_table['comment']=''; }
						if (!array_key_exists('engine',$my_table))  { $my_table['engine']='MyISAM'; }
						if (!array_key_exists('collate',$my_table))  { $my_table['collate']='utf8_general_ci'; }

						$insert_table = "
							CREATE TABLE `" . $my_table['name'] . "` (
								  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY 
								) COMMENT='" . $my_table['comment'] . "' ENGINE='" . $my_table['engine'] . "' COLLATE '" . $my_table['collate'] . "';";
						sc_exec_sql($insert_table);

						asdw_echo("Table has been created successfully.");
					}

				$my_data->Close();
			}
	

		asdw_echo("Check if all columns (fields) exist.");
		
		foreach($my_columns AS $my_column)
			{
				asdw_echo("Check column: " . $my_column['name']);
				$my_sql = "SHOW COLUMNS FROM `" .  $my_table['name']  . "` LIKE '" . $my_column['name'] . "'";
				sc_select(my_data, $my_sql);
				if ({my_data} === false)
					{
						asdw_error( "asdw_check_table_columns - Access error. Message =". {my_data_erro} . ' - ' . $my_sql);
					}
				else
					{
						if($my_data->EOF)
							{	
								if (!array_key_exists('type',$my_column))  { $my_column['type']='text'; }
								if (!array_key_exists('collate',$my_column))  { $mycollate=''; } else { $mycollate=' COLLATE ' . $my_column['collate']; }
								if (!array_key_exists('null',$my_column))  { $mynull=' NOT NULL '; } else { if ($my_column['null']==1) { $mynull=''; } else { $mynull = ' NOT NULL ';} }
								if (!array_key_exists('comment',$my_column))  { $mycomment=''; } else { $mycomment=" COMMENT '" . $my_column['comment']. "' "; }
								if (!array_key_exists('after',$my_column))  { $myafter=''; } else { $myafter=" AFTER `" . $my_column['after']. "` "; }
								if (!array_key_exists('default',$my_column))&nbsp; { $mydefault=''; } else { $mydefault=" DEFAULT '" . $my_column['default']. "' "; } 								
							        $add_sql = "ALTER TABLE `" . $my_table['name'] . "` ADD `" 	. $my_column['name'] . "` " 
																								. $my_column['type'] . " " 
																								. $mycollate . " " 
																								. $mynull . " " 
																								. $mycomment . " " 
																								. $myafter . " "
																								. $mydefault . " " ;
								asdw_echo( "<br>".$add_sql);
								sc_exec_sql($add_sql);
							}
						$my_data->Close();
					}
			}
	}

function asdw_check_table_columns($my_table,$my_columns)

{

// this function checks if $my_table[] exists (and if not, generates it)

// + checks if all columns in $my_columns[][] exist (and if not, adds them)

asdw_echo("Check if table " . $my_table['name'] . " exists...");

$my_sql = "SHOW TABLES LIKE '" . $my_table['name'] . "'";

sc_select(my_data, $my_sql);

if ({my_data} === false)

{

asdw_error ( "asdw_check_table_columns - Access error. Message =". {my_data_erro} . " - " . $my_sql);

}

else

{

if (!$my_data->EOF)

{

// Exists

asdw_echo("Table " . $my_table['name'] . " already exists");

}

else

{ // Create

asdw_echo( "Table " . $my_table['name'] . " does not exist > create with id and primary index");

if (!array_key_exists('comment',$my_table)) { $my_table['comment']=''; }

if (!array_key_exists('engine',$my_table)) { $my_table['engine']='MyISAM'; }

if (!array_key_exists('collate',$my_table)) { $my_table['collate']='utf8_general_ci'; }

$insert_table = "

CREATE TABLE `" . $my_table['name'] . "` (

`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY

) COMMENT='" . $my_table['comment'] . "' ENGINE='" . $my_table['engine'] . "' COLLATE '" . $my_table['collate'] . "';";

sc_exec_sql($insert_table);

asdw_echo("Table has been created successfully.");

}

$my_data->Close();

}

asdw_echo("Check if all columns (fields) exist.");

foreach($my_columns AS $my_column)

{

asdw_echo("Check column: " . $my_column['name']);

$my_sql = "SHOW COLUMNS FROM `" . $my_table['name'] . "` LIKE '" . $my_column['name'] . "'";

sc_select(my_data, $my_sql);

if ({my_data} === false)

{

asdw_error( "asdw_check_table_columns - Access error. Message =". {my_data_erro} . ' - ' . $my_sql);

}

else

{

if($my_data->EOF)

{

if (!array_key_exists('type',$my_column)) { $my_column['type']='text'; }

if (!array_key_exists('collate',$my_column)) { $mycollate=''; } else { $mycollate=' COLLATE ' . $my_column['collate']; }

if (!array_key_exists('null',$my_column)) { $mynull=' NOT NULL '; } else { if ($my_column['null']==1) { $mynull=''; } else { $mynull = ' NOT NULL ';} }

if (!array_key_exists('comment',$my_column)) { $mycomment=''; } else { $mycomment=" COMMENT '" . $my_column['comment']. "' "; }

if (!array_key_exists('after',$my_column)) { $myafter=''; } else { $myafter=" AFTER `" . $my_column['after']. "` "; }

if (!array_key_exists('default',$my_column))  { $mydefault=''; } else { $mydefault=" DEFAULT '" . $my_column['default']. "' "; }

$add_sql = "ALTER TABLE `" . $my_table['name'] . "` ADD `" . $my_column['name'] . "` "

. $my_column['type'] . " "

. $mycollate . " "

. $mynull . " "

. $mycomment . " "

. $myafter . " "

. $mydefault . " " ;

asdw_echo( "<br>".$add_sql);

sc_exec_sql($add_sql);

}

$my_data->Close();

}

Anmerkung: In dieser Funktion wurde zur Prüfung / Debugging die Funktionen asdw_echo() und asdw_error() aus unserer Sniplet-Sammlung verwendet.