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();
}

Community · Accepted Answer · 2017-05-23 12:21:16Z

1

There's no 'unknown' data type in MySQL (maybe the blob types is the most similar to that).

If you really need to define table fields automatically only from their names, and don't know which type of data will be stored there, create them as blob or text, and when storing values there create a text representation made from its actual type as a header plus a text representation of the value.

Or maybe you don't need that at all. Check Using PHP to take the first line of a CSV file and create a MySQL Table with the data and maybe you'll find a response there.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Apr 22, 2012 at 12:01

sharcashmo

8251 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

phedon rousou Over a year ago

Hello, that will definitely work. Thanks for the link. It really helped. I will be posting the solution based on that link

phedon rousou · Accepted Answer · 2012-04-22 17:36:02Z

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();

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related