Match csv filenames to table names and import

Question

I have multiple csv files generated everyday with same name and they should be imported to Mysql database . I can import a single file to table where I have defined the csv file and table name before importing, but how to match the csv file to table names and import them. here is my code:

$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (mysqli_connect_error()) {
  echo "Connection failed!!!" .mysqli_connect_error();
  exit();
}
else{
echo "Connected successfully \n";
}


$serverDir = $_SERVER['DOCUMENT_ROOT'];

//$filename = "OwlertonGreenIN.CSV";

echo getcwd();

print_r($filename);

//$table_name = strtolower( $filename );

//$filename = "$serverDir.$filename";

if (($handle = fopen($filename, 'r')) !== FALSE)
     while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
     {
      // print_r($data);
       $import="INSERT into   table name (`camera_name`,`plate`) values(`camera_name`,`plate`)";
}

Answer 1

I created functions for myself a while back that would essentially solve your problem if implemented correctly.

Only thing is I created them inside a DB class that uses/extends PDO and doesn't use mysqli. If you were to change how you connect to your database and use PDO, you could use the below class and utilize the insertQuery function that I made.

<?php
    class DB extends PDO{
        protected $host = "localhost";
        protected $user = "admin";
        protected $password = "";

        public $connect;

        public function __construct(string $dbname){
            try{
                $this->connect = new \PDO("mysql:host=".$this->host.";dbname=".$dbname, $this->user, $this->password);
                $this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }catch(PDOException $e){  }
        }

        public function insertQuery(string $table, array $data){
            try{

                // declare our query peices
                $half1 = "INSERT INTO `${table}` (";
                $half2 = ") VALUES (";

                // build both halfs of query w/ the keys of the array being used as the column name
                foreach($data as $column => $v){
                    $half1 .= "${column}, ";
                    $half2 .= ":${column}, ";
                }

                // remove extra commas and spaces from the ends of our two halfs and then combine them
                $query = substr($half1, 0, strlen($half1) - 2).substr($half2, 0, strlen($half2) - 2).")";

                // prepare query
                $prep = $this->connect->prepare($query);

                // loop through & bind all values in array to the corresponding keys in our query 
                foreach($data as $column => $value){
                    if(is_string($value)){
                        $prep->bindValue(":${column}", $value, PDO::PARAM_STR);
                    }elseif(is_int($value)){
                        $prep->bindValue(":${column}", $value, PDO::PARAM_INT);
                    }elseif(is_bool($value)){
                        $prep->bindValue(":${column}", $value, PDO::PARAM_BOOL);
                    }
                }

                // execute query
                $prep->execute();

                // if row added, return true
                if($prep->rowCount() > 0) return true;

                // if not, return false
                else return false;

            }catch(PDOException $e){ return false; }
        }

        public static function csvParse($input, $callback = false){
            $results = [];
            $raw_array = (is_file($input)) ? array_map('str_getcsv', file($input)):array_map('str_getcsv', explode("\n", $input));
            $array = array_splice($raw_array, 1, count($raw_array));
            foreach($raw_array[0] as $c) $columns[] = $c;
            foreach($array as $key0 => $val0) foreach($val0 as $key1 => $val1) $results[$key0][$columns[$key1]] = $val1;
     
            if(is_callable($callback) && !empty($results)) call_user_func_array($callback, array($results));
            elseif(!empty($results)) return $results;
            else throw new Exception("Results Empty: Can not read the string or open file.");
        }
    }
    
    $csv_str = "name,age,occupation,city\nCrimin4L,24,Programmer,New York\nMrAwesome,20,Gamer,Los Angeles";
    
    // parse CSV file or string into a readable array (if file, use file location as parameter):
    $csv_array = DB::csvParse($csv_str);
    
    // print_r($csv_array) output:
    /* Array
    (
        [0] => Array
            (
                [name] => Crimin4L
                [age] => 24
                [occupation] => Programmer
                [city] => New York
            )
    
        [1] => Array
            (
                [name] => MrAwesome
                [age] => 20
                [occupation] => Gamer
                [city] => Los Angeles
            )
    
    ) */
    
    // with that you can now use the DB::insertQuery function, but since it 
    // is a 2 dimentional array (multiple rows) you would need to loop 
    // through with a foreach
    
    # start your database connection 
    $db = new DB("database_name_to_connect_into");
    
    // Now loop through array with foreach
    foreach($csv_array as $row){

        # insert current row 
        $db->insertQuery("table_to_insert_data_into", $row);
            
    }
    
    /*
        As long as the table column(s) match the key(s) in 
        the array, it should work flawlessly.
        ----
        For this example, the table columns in the database 
        would need to be named: name, age, occupation, & city; 
        because that is what the array keys are named, basically
        the array values are inserted into their corresponding table
        columns by matching the array keys (if that any makes sense).
    */
    
    
?>

I can't really give you an example as its dealing with databases but if you'd like to play around with the code here it is in a sandbox: https://sandbox.onlinephpfunctions.com/c/20365

Answer 2

I'd use this:

$tablename = pathinfo($filename, PATHINFO_FILENAME);

Prepare an INSERT statement with placeholders:

$import="INSERT INTO `{$tablename}` (`camera_name`,`plate`) VALUES(?, ?)";
$stmt = $conn->prepare($import);

The tablename should be within back-ticks because you don't know if it contains whitespace or punctuation or an SQL reserved keyword.

Then execute that prepared statement for each row you read from the CSV file.

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
{
   $stmt->bind_param('ss', $data[0], $data[1]);
   $stmt->execute();
}

This may run pretty slowly, inserting one row at a time. You might like to read my presentation Load Data Fast! for more tips on performance improvement.