我有这个PHP代码,它上传CSV并将数据插入数据库,一旦在while循环中上传,它只从CSV:中选择一行
//do upload
if (is_uploaded_file($_FILES['dd_submission_form']['tmp_name']))
{
echo "<h3>" . "File ". $_FILES['dd_submission_form']['name'] ." uploaded successfully." . "</h3>";
echo '<h3>Display file contents:</h3>';
//readfile($_FILES['dd_submission_form']['tmp_name']);
}
//Import uploaded file to Database
$handle = fopen($_FILES['dd_submission_form']['tmp_name'], "r");
fgetcsv($handle, 1000, ",");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql="SELECT * from customer where directdebit_reference = '".$data[0]."' ";
echo $sql;
$rs=mysql_query($sql,$conn) or die(mysql_error());
$customer=mysql_fetch_array($rs);
if(mysql_num_rows($rs) > 0)
{
$sql2="INSERT into dd_submissions (customer_seq, dd_reference, sortcode, account_number, account_name, amount, bacs_code, invoice_no, title, initial, forename, surname, salutation_1, salutation_2, address_1, address_2, area, town, postscode, phone, mobile, email, notes) values ('".$customer["sequence"]."', '$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]', '$data[22]')";
$rs2=mysql_query($sql2,$conn) or die(mysql_error());
}
}
fclose($handle);
print "<br><br><h3>Successfully Imported Clients</h3><br>";
只需单独寻址循环前的第一行,不做任何操作即可跳过它。
fgets($handle);
然后循环
最简单的方法是在while
循环中使用continue
关键字,如下所示:
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if ($i == 0) { $i++; continue; }
...
$i++;
但是,为了安全起见,您必须确保第一行不包含所需的数据。
我建议您用对mySQL的LOAD DATA INFILE
查询的单个调用来替换所有这些代码,该查询旨在将CSV文件直接加载到数据库中,而不需要您正在执行的所有PHP循环。它运行的速度大大快于在PHP循环中运行。
使用子查询作为主LOAD DATA INFILE
查询的一部分,可以很容易地添加客户字段。
哦,为了回答最初的问题,作为标准语法的一部分,它还能够跳过第一行。
请参阅此处的mySQL手册页面:http://dev.mysql.com/doc/refman/5.1/en/load-data.html
[EDIT]
您的查询大致如下:
LOAD DATA INFILE
INTO TABLE dd_submissions
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ''r'n'
IGNORE 1 ROW
(
@dd_reference,
sortcode,
account_number,
account_name,
amount,
bacs_code,
invoice_no,
title,
initial,
forename,
surname,
salutation_1,
salutation_2,
address_1,
address_2,
area,
town,
postscode,
phone,
mobile,
email,
notes
)
SET
customer_seq = SELECT sequence from customer where directdebit_reference = @dd_reference,
dd_reference = @dd_reference
;
我怀疑您遇到的问题可能是因为"1000",这无论如何都是不必要的。跳过第一行的一个简单方法是执行以下操作:
fgetcsv($handle) //skips the first line
while($data = fgetcsv($handle)) { //will process second line to end of file
...
}
然而,你有一个更大的问题。如果您的csv文件看起来像:
'; DROP TABLE customer #
然后$sql变成
SELECT * from customer where directdebit_reference = ''; DROP TABLE customer #'
现在你没有客户了。查找SQL注入,了解如何确保传递到MySQL的查询是安全的。
从csv文件读取行中断的一个可能原因是csv文件中的数据可能包含打断sql查询的单引号或双引号,因此可以通过使用mysql_real_eescape_string函数来解决此问题。所以你的新插入查询看起来像:
....
......
$sql2="INSERT into dd_submissions (customer_seq, dd_reference, sortcode, account_number, account_name, amount, bacs_code, invoice_no, title, initial, forename, surname, salutation_1, salutation_2, address_1, address_2, area, town, postscode, phone, mobile, email, notes) values ('".mysql_real_escape_string($customer["sequence"])."', '".mysql_real_escape_string($data[0])."', .......);
希望它能帮助。。。
在while loop
:之前声明一个变量
$row=1;
则在CCD_ 7中设置以下条件:
if($row==1){
$row++;
continue;
}