Script to list all the field data types in specified database and create a CSV file then download the CSV file.
<?php
set_time_limit(0);
getUniqueFieldTypes('HOSTNAME','DATABASE USERNAME','PASSWORD','DATABASENAME');
function getUniqueFieldTypes($host,$user,$pass,$name,$tables = '*')
{
$link = mysql_connect($host,$user,$pass) or die('Could not connect');
mysql_select_db($name,$link);
//Get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
$dataTypes = array();
//Cycle through
foreach($tables as $table)
{
$result = mysql_query('SHOW FIELDS FROM '.$table);
$num_fields = mysql_num_fields($result);
while($row = mysql_fetch_row($result))
{
$dataTypes[$row[1]] = $row[1];
}
//sleep(1);
}
asort($dataTypes);
$fp = fopen('fields.csv', 'w+');
foreach($dataTypes as $dataType) {
$values = array($dataType);
fputcsv($fp, $values);
}
fclose($fp);
$file = 'fields.csv';
if (file_exists($file)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . filesize($file));
ob_clean();
flush();
readfile($file);
exit;
}
}
?>
<html>
<head>
<title>Create CSV with list of field data types</title>
</head>
</html>
Below are the list data types supported by MYSQL:
VARCHAR
TINYINT
TEXT
DATE
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
DATETIME
TIMESTAMP
TIME
YEAR
CHAR
TINYBLOB
TINYTEXT
BLOB
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM
SET
BOOL
BINARY
VARBINARY
<?php
set_time_limit(0);
getUniqueFieldTypes('HOSTNAME','DATABASE USERNAME','PASSWORD','DATABASENAME');
function getUniqueFieldTypes($host,$user,$pass,$name,$tables = '*')
{
$link = mysql_connect($host,$user,$pass) or die('Could not connect');
mysql_select_db($name,$link);
//Get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
$dataTypes = array();
//Cycle through
foreach($tables as $table)
{
$result = mysql_query('SHOW FIELDS FROM '.$table);
$num_fields = mysql_num_fields($result);
while($row = mysql_fetch_row($result))
{
$dataTypes[$row[1]] = $row[1];
}
//sleep(1);
}
asort($dataTypes);
$fp = fopen('fields.csv', 'w+');
foreach($dataTypes as $dataType) {
$values = array($dataType);
fputcsv($fp, $values);
}
fclose($fp);
$file = 'fields.csv';
if (file_exists($file)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . filesize($file));
ob_clean();
flush();
readfile($file);
exit;
}
}
?>
<html>
<head>
<title>Create CSV with list of field data types</title>
</head>
</html>
Below are the list data types supported by MYSQL:
VARCHAR
TINYINT
TEXT
DATE
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
DATETIME
TIMESTAMP
TIME
YEAR
CHAR
TINYBLOB
TINYTEXT
BLOB
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM
SET
BOOL
BINARY
VARBINARY
