我想用php将csv文件中的数据插入到我的mysql数据库中。但是我不知道我做错了什么。
这是我的php代码
if ($_FILES[csv][size] > 0){
$csv_file = $_FILES[csv][tmp_name]; // Name of your CSV file
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
$i = 0;
while (!feof($csvfile)) {
$csv_data[] = fgets($csvfile, 1024);
$csv_array = explode(",", $csv_data[$i]);
$insert_csv = array();
$insert_csv['id'] = $csv_array[0];
$insert_csv['name'] = $csv_array[1];
$insert_csv['email'] = $csv_array[2];
if(!empty($insert_csv['email'])){
$query = "INSERT INTO contacts(id,name,email)
VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')";
$n=mysqli_query($database->connection,$query);
}
$i++;
}
fclose($csvfile);
}
这是我的csv。
id---- name ------- email
1 ---- user1--------bla@hotmail.com
2 ---- user2 --------blah
3------ user 3 ------ blah
当我运行这段代码时,我的mysql结果是
在电子邮件表中=##0.00"TL")在我的姓名表中$#en=也##0.00"TL")$#;
我做错了什么?
您可能希望使用MySQL通过LOAD DATA INFILE语句完成整个加载过程。
if($_FILES['csv']['error'] === UPLOAD_ERR_OK && $_FILES['csv']['size'] > 0) {
$query = "LOAD DATA INFILE '" . $_FILES['csv']['tmp_name']
. "' INTO TABLE contacts FIELDS TERMINATED BY ',' ENCLOSED BY ''"' LINES TERMINATED BY ''n' (id, name, email);";
if(!mysqli_query($query)){
die('Oops! Something went wrong!');
}
}
如果需要,可以调整加载参数(FIELDS TERMINATED BY
、ENCLOSED BY
、LINES TERMINATED BY
)。
请注意,如果使用这种方法,您的临时文件需要存储在MySQL服务器可以访问的地方(如/tmp
)。
首先,我认为您应该删除第一个
$data = fgetcsv($getfile, 1000, ",");
线,while循环之外。。。
请尝试这样的例子,它应该适合你,因为你想要
我想你错过了
$query = "INSERT INTO contacts(id,name,email)
VALUES('".$col1."','".$col2."','".$col3."')";
"
<?php
$csv_file = 'C:'wamp'www'stockmarket'test.csv'; // Name of your CSV file with path
if (($getfile = fopen($csv_file, "r")) !== FALSE) {
$data = fgetcsv($getfile, 1000, ",");
while (($data = fgetcsv($getfile, 1000, ",")) !== FALSE) {
$num = count($data);
for ($c=0; $c < $num; $c++) {
$result = $data;
$str = implode(",", $result);
$slice = explode(",", $str);
$col1 = $slice[0];
$col2 = $slice[1];
$col3 = $slice[2];
// SQL Query to insert data into DataBase
$query = "INSERT INTO contacts(id,name,email)
VALUES('".$col1."','".$col2."','".$col3."')";
$s=mysql_query($query, $connect );
}
}
}
?>