<?php
$dbserver = "[your database server";]
$dbname = "[your database name]";
$dbuser = "[your user]";
$dbpassword = "[your password]";

mysql_connect($dbserver,$dbuser,$dbpassword) or die('Could not connect: ' . mysql_error());
mysql_select_db($dbname);

$fileName = "sponsors.txt";
$tableName = str_replace(".txt","",$fileName);
echo $fileName;

$rowcount = 1;
foreach (file($fileName) as $row)
	{
	$rowArray = explode(chr(124),$row);

	if($rowcount==1)
		{
		$fieldArray = array();
		$fieldSizeArray = array();
		//echo "<strong>Fields:</strong><br />";
		foreach($rowArray as $field)
			{
			$field = strtolower($field);
			echo $field . "<br />";
			array_push($fieldArray,$field);
			}
		}
	else
		{
		$fieldCount = 1;
		foreach($rowArray as $field)
			{
			$field = strtolower($field);
			$fieldlegnth = strlen($field);
			if(isset($fieldSizeArray[$fieldCount]))
				{
				if($fieldSizeArray[$fieldCount] < $fieldlegnth)
					{
					$fieldSizeArray[$fieldCount] = $fieldlegnth;
					}
				}
			else
				{
				$fieldSizeArray[$fieldCount] = $fieldlegnth;
				}
			$fieldCount ++;
			}
		}

	$rowcount++;
	}

//Create Table
$checkLikeTableQuery = "show tables from clinical_trials like " . chr(34) . $tableName . chr(34);
$checkLikeTableResult = mysql_query($checkLikeTableQuery) or die('Query failed: ' . mysql_error());
if($checkLikeTableResult && mysql_num_rows($checkLikeTableResult))
	{
	$checkLikeTableResult = mysql_fetch_assoc($checkLikeTableResult);
	}
else
	{
	$CreateTableQuery = "CREATE TABLE  `clinical_trials`.`" . $tableName . "` (" . chr(13);
	$CreateTableQuery .= "`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,";
	$fieldcount = 1;
	foreach($fieldArray as $field)
		{
		$field = trim($field);
		if($field=='group_id')
			{
			$field = 'group_id';
			}
		$fieldsize = $fieldSizeArray[$fieldcount];
		if($fieldsize < 100)
			{
			$CreateTableQuery .= "`" . $field . "` varchar(100) NULL,";
			}
		elseif($fieldsize > 100 && $fieldsize < 1500)
			{
			$CreateTableQuery .= "`" . $field . "` varchar(1000) NULL,";
			}
		else
			{
			$CreateTableQuery .= "`" . $field . "` text,";
			}
		$fieldcount++;
		}

	$CreateTableQuery .= "PRIMARY KEY (`ID`)";
	$CreateTableQuery .= ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
	mysql_query($CreateTableQuery) or die('Query failed: ' . mysql_error());

	}

$rowcount = 1;
foreach (file($fileName) as $row)
	{
	$rowArray = explode(chr(124),$row);

	if($rowcount > 1) // && $rowcount < 10
		{

		$InsertQuery = "INSERT INTO `clinical_trials`.`" . $tableName . "`(";
		$first = 1;
		foreach($fieldArray as $field)
			{
			$field = trim($field);
			$InsertQuery .= $field;
			if($first < count($fieldArray)){ $InsertQuery .= ','; $first++; }
			}

		$InsertQuery .= ") VALUES(";

		$fieldcount = 1;
		$first = 1;
		foreach($rowArray as $value)
			{
			$InsertQuery .= "'" . mysql_real_escape_string(trim($value)) . "'";
			if($first < count($rowArray)){ $InsertQuery .= ','; $first++; }
			}

		$misscount = count($fieldArray) - count($rowArray);

		for ($x = 0; $x <= $misscount; $x++)
			{
			$InsertQuery .= "''";
			if($x < $misscount){ $InsertQuery .= ", ";}
			}

		$InsertQuery .= ")";
		echo $InsertQuery . "<br />";

		try
			{
		  mysql_query($InsertQuery);
			}
		catch (Exception $e)
			{
		  echo 'Caught exception: ',  $e->getMessage(), "\n";
			}
		}
	$rowcount++;
	}
?>