我每天都会生成多个同名的csv文件，它们应该导入Mysql数据库。我可以将单个文件导入到表中，在导入之前我已经定义了csv文件和表名，但如何将csv文件与表名匹配并导入它们。这是我的代码：
$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`)";
}
我不久前为自己创建了一些函数，如果实现正确，这些函数基本上可以解决您的问题。
唯一的问题是，我在一个使用/扩展PDO而不使用mysqli的DB类中创建了它们。如果要更改连接数据库和使用PDO的方式，可以使用下面的类并使用我制作的insertQuery函数。
<?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,citynCrimin4L,24,Programmer,New YorknMrAwesome,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).
*/
?>
我真的不能给你一个处理数据库的例子，但如果你想在这里玩代码，它是在沙盒中：https://sandbox.onlinephpfunctions.com/c/20365
我会使用这个：
$tablename = pathinfo($filename, PATHINFO_FILENAME);
准备一个带有占位符的INSERT语句：
$import="INSERT INTO `{$tablename}` (`camera_name`,`plate`) VALUES(?, ?)";
$stmt = $conn->prepare($import);
表名应该在反引号内，因为您不知道它是否包含空格、标点符号或SQL保留关键字。
然后对从CSV文件中读取的每一行执行准备好的语句。
while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)
{
$stmt->bind_param('ss', $data[0], $data[1]);
$stmt->execute();
}
这可能运行得很慢，一次插入一行。您可能想阅读我的演示文稿"快速加载数据！"！了解有关性能改进的更多提示。