Create mysql table to store property data from rets metadata PHRETS | Updated

Goal: MYSQL TABLE CREATE Statement * of property table to store property data from rets metadata.

EXAMPLE

// first of all create session
$connect = $rets->Login();

// getting system meta data
$system = $rets->GetSystemMetadata();

// list of available resources
$resources = $system->getResources();

// array of available property types
$classes = $rets->GetClassesMetadata('Property');

// manually setting resouce and class (class = Property Type)
$resource = "Property";
$class = "RE_1";

$table_name = "rets_".strtolower($resource)."_".strtolower($class);
$fields = $rets->GetTableMetadata($resource,$class);

$sql = create_table_sql_from_metadata($table_name, $fields, "L_ListingID");
echo $sql;

function create_table_sql_from_metadata($table_name, $rets_metadata, $key_field, $field_prefix = "") {
	$sql_query = "CREATE TABLE ".$table_name." (\n";
	foreach ($rets_metadata as $field) {
		$cleaned_comment = addslashes($field->getLongName());
		$sql_make = "\t`" . $field_prefix . $field->getSystemName()."` ";
		if ($field->getInterpretation() == "LookupMulti") {
			$sql_make .= "TEXT";
		} elseif ($field->getInterpretation() == "Lookup") {
			$sql_make .= "VARCHAR(50)";
		} elseif ($field->getDataType() == "Int" || $field->getDataType() == "Small" || $field->getDataType() == "Tiny") {
			$sql_make .= "INT(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "Long") {
			$sql_make .= "BIGINT(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "DateTime") {
			$sql_make .= "DATETIME default '0000-00-00 00:00:00' NOT NULL";
		} elseif ($field->getDataType() == "Character" && $field->getMaximumLength() <= 255) {
			$sql_make .= "VARCHAR(".$field->getMaximumLength().")";
		} elseif ($field->getDataType() == "Character" && $field->getMaximumLength() > 255) {
			$sql_make .= "TEXT";
		} elseif ($field->getDataType() == "Decimal") {
			$pre_point = ($field->getMaximumLength() - $field->getPrecision());
			$post_point = !empty($field->getPrecision()) ? $field->getPrecision() : 0;
			$sql_make .= "DECIMAL({$field->getMaximumLength()},{$post_point})";
		} elseif ($field->getDataType() == "Boolean") {
			$sql_make .= "CHAR(1)";
		} elseif ($field->getDataType() == "Date") {
			$sql_make .= "DATE default '0000-00-00' NOT NULL";
		} elseif ($field->getDataType() == "Time") {
			$sql_make .= "TIME default '00:00:00' NOT NULL";
		} else {
			$sql_make .= "VARCHAR(255)";
		}
		$sql_make .=  " COMMENT '".$cleaned_comment."',\n";
		$sql_query .= $sql_make;
	}
	$sql_query .=  "PRIMARY KEY(`".$field_prefix.$key_field."`) )";
	return $sql_query;
}