嗨,我一直在玩下面的代码来导入csv文件。我怎么把它放在这样的地方,它
- 检查记录是否已存在
- 如果需要,请更新记录
- 如果不存在,请创建新记录
-
创建一个新的csv文件,其中包含已完成的任何额外记录或更新的信息。感谢
<?php $connect = mysql_connect('localhost','root','12345'); if (!$connect) { die('Could not connect to MySQL: ' . mysql_error()); } $cid =mysql_select_db('test',$connect); // supply your database name define('CSV_PATH','C:/wamp/www/csvfile/'); // path where your CSV file is located $csv_file = CSV_PATH . "infotuts.csv"; // 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]; $query = "INSERT INTO csvdata(ID,name,email) VALUES('','".$insert_csv['name']."','".$insert_csv['email']."')"; $n=mysql_query($query, $connect ); $i++; } fclose($csvfile); echo "File data successfully imported to database!!"; mysql_close($connect); ?>
对于您的问题1到3:在MySQL中使用"upstart":
$query = "INSERT INTO csvdata (id,name,email)
VALUES ('".$insert_csv['ID']."','".$insert_csv['name']."','".$insert_csv['email']."')
ON DUPLICATE KEY UPDATE name='".$insert_csv['name']."', email='".$insert_csv['email']."';";
这将在数据库中插入记录,除非密钥重复(在这种情况下,我假设您的主键是"ID")。http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html
对于第4个问题,请使用mysql_affected_rows(http://php.net/manual/en/function.mysql-affected-rows.php)
如果返回的值大于0,则表示插入/更新了某些内容。然后,您可以将值写入一个单独的数组中,并在末尾将其写入一个新的CSV。
$fh = fopen('altered_rows.csv', 'w');
[...]
$n=mysql_query($query, $connect );
$affected_rows = mysql_affected_rows($connect);
if($affected_rows>0) fputcsv($fh, $insert_csv);
[...]
fclose($fh);
http://php.net/manual/en/function.fputcsv.php