import.php

<?php

error_reporting(E_ALL);
$host = '127.0.0.1';
$user = 'root';
$pass = 'password';
$database = 'test_cnes';
$delimiter = ';'; 

$db = mysqli_connect($host, $user, $pass,$database);

if (!$db) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

$dir = getcwd();
$files = scandir($dir);

foreach ($files as $file) {

    $file = $file;
    $table = str_replace('.csv', '', $file);
     
    $fp = fopen($file, 'r');

    $frow = fgetcsv($fp,0,$delimiter);
    $columns = '';

    foreach($frow as $column) {
        if($columns) $columns .= ',';
        $columns .= str_replace(['\'', 'TO_CHAR(', ',DD/MM/YYY'], '', "`$column` VARCHAR(191) NULL ");
    }

    $create = "create table if not exists $table ($columns);";
    echo $create."\n";
    mysqli_query($db,$create);

    /********************************************************************************/
    // Import the data into the newly created table.

    $file = $_SERVER['PWD'].DIRECTORY_SEPARATOR.$file;
    $q = "load data local infile '$file' into table $table fields terminated by '$delimiter' ENCLOSED BY '\"' ";
    echo $q."\n";
    mysqli_query($db,$q);
    echo "$table \n";

    $q = "ALTER TABLE $table ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);";
    //$q2 = "ALTER TABLE $table ADD INDEX(`CO_UNIDADE`);";
    //$q3 = "ALTER TABLE rlCooperativa201904 ADD CONSTRAINT `co_unidade` FOREIGN KEY (`CO_UNIDADE`) REFERENCES `tbEstabelecimento201904`(`CO_UNIDADE`) ON DELETE RESTRICT ON UPDATE RESTRICT;";
    mysqli_query($db,$q);
    //mysqli_query($db,$q2);
    //mysqli_query($db,$q3);
}