Match csv filenames to table names and import



PHP Snippet 1:

<?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).
    */
    
    
?>

PHP Snippet 2:

$tablename = pathinfo($filename, PATHINFO_FILENAME);

PHP Snippet 3:

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

PHP Snippet 4:

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