mySql: How to create a table with unknown data types?

Question

I would like your help with something that I have searched a lot but could't yet find an answer. As the title of the Question states, I am trying to create a table in mySql database in which I do not know the data types of the columns. Basically, I use php to create new tables in my database from an csv file. The first line contains the column names. I know that it can be done because I have seen it in phpMyAdmin where I can select a csv file and use the first line to generate the table column names and I do not have to specify the data types of each column,

So far, this is my php code :

$databaseName="myDatabase";
mysql_connect("localhost","xxxxxx","xxxxxxxx");
mysql_select_db($databaseName);


for($i = 0; $i < count($newFiles); $i++){//traverse the table that contains the file names
         $content = file($newFileLocation.$newFiles[$i]);

        //First line: $content[0];
        $firstLine=$content[0];//save first line which are the column names
        //echo  $firstLine;
        $tableName= str_replace('.txt','', $newFiles[$i]);//remove the .txt to use for table name
        //echo $tableName."\n";

        echo "\n\ncreating tables\n\n";
        /*mysql_query("load data local infile '$newFiles[$i]' 
               into table $tableName
                   fields terminated by ','
                   lines terminated by '\n'
                   ($firstLine) "); */


        mysql_close();
}

Answer 1

Here is the solution to the problem. At first, we take the first line of the file to save the cols names(so that we know how to create the table). We save it in a string and then we separate them based on the comma character. After that, we take the second line of the file which contains values. We store that line to a string and we separate the string to it's different values based on the comma character. We will be using this to identify the types of each column.

So, this is the code.

$databaseName="myDatabase";
mysql_connect("localhost","xxxxxx","xxxxxxxx");
mysql_select_db($databaseName);


for($k = 0; $k < count($newFiles); $k++){//traverse the table that contains the file names
            $content = file($newFileLocation.$newFiles[$k]);

            //First line: $content[0];
             $firstLine=$content[0];
             $secondLine=$content[1];   
            //echo $firstLine."\n";
            $firstLine = str_replace("\r\n",'',$firstLine);
            $colNames=explode(',',$firstLine);
            $fieldList = explode(',',$secondLine);

            $dataType="";
            for($i = 0; $i < count($fieldList); $i++){
                if (is_numeric($fieldList[$i])){
                        if (strpos($fieldList[$i],'.') == false){
                            $fieldList[$i] = (int)$fieldList[$i];
                       }else{
                            $fieldList[$i] = (float)$fieldList[$i];
                        }
                    }

                switch(gettype($fieldList[$i])) {
                        case 'integer':
                            $typeInfo = 'int(11)';
                            break;
                        case 'float':
                        case 'double':
                                $typeInfo = 'float';
                                break;

                        case 'string':
                                $typeInfo = 'varchar(80)';
                            break;
                        default:
                                $typeInfo = 'varchar(80)';
                                break;
                        }

                if(gettype($fieldList[$i]) != NULL) {
                    $dataType= $dataType.'`'.$colNames[$i].'` '." ".$typeInfo.' NOT NULL';
                    if(($i)!=count($fieldList)-1){
                        $dataType= $dataType.",";
                    }
                }
            }   //end of for loop


             $tableName= str_replace('.txt','', $newFiles[$k]);
             //echo $tableName."\n";

            //
                //echo "\n".$dataType."\n";
                $succ=mysql_query("CREATE TABLE `$tableName` ($dataType)");
                if($succ)
                    echo "Table ".$tableName." was created Succesfully \n";

                //echo $databaseName;

                 $loadsql = 'LOAD DATA LOCAL INFILE "'.$newFileLocation.$newFiles[$k].'" INTO TABLE `'.$tableName.'` FIELDS TERMINATED BY ","  IGNORE 1 LINES ('.$firstLine.')';
                 mysql_query($loadsql);     




        }//end of for loop


     mysql_close();