PHP/MYSQL上传，将.csv文件导入mysql-process-table设计 [英] PHP/MYSQL Upload, import .csv file to mysql-process-table design
问题描述
我想让用户将他们的联系人从.csv文件(从各种联系人管理程序导出)上传到服务器，然后将其保存在mysql中.根据我在SO和其他地方所读到的内容，MYSQL可以使用LOAD DATA INFILE导入数据.似乎还有一个LOCAL选项可以从客户端获取它.我的三部分问题是:我是否需要使用文件上载才能将文件发送到服务器，还是可以通过LOAD DATA INFILE的LOCAL选项直接访问它?由于该应用程序将通过联系人为多个用户提供服务，因此我应该将所有联系人都放在一个表中吗?由于不同的用户在他们的.csv文件中具有不同的结构，因此，处理不同.csv文件中的字段名/模式差异的最佳方法是什么?
I want to let users upload their contacts from a .csv file (exported from various contact management programs) to the server and then save it in mysql. From what I've read on SO and elsewhere, MYSQL can import the data using LOAD DATA INFILE. It also seems like there is a LOCAL option to get it from the client side. My three-part question is: Do I need to use file upload to get the file to the server or can I directly access it via the LOCAL option of LOAD DATA INFILE? Since the application will service multiple users with their contacts, should I put all the contacts in one table? Since different users will have different structures in their .csv files what is the best approach to handling discrepancies in fieldnames/schema in the different .csv files?
暂定代码:
<html>
<body>
<form action="getcsvile.php" method="post"
enctype="multipart/form-data">
<input type="file" size=12 name="file" id="file" /><input type="submit" name="submit" value="Upload CSV File" /></form>
<body>
</html>
<?php
$userid = $_SESSION['userid'];
//error checking to make sure .csv file and other requirements met...then
//get extension, set to $ext
$target = "csvfiles/".$userid.".".$ext;
move_uploaded_file($_FILES["file"]["tmp_name"],$target);
//NEED TO PARSE DATA USING fgetcsv or something to examine, clean up csv data?
$sql = "LOAD DATA LOCAL INFILE '$target' INTO TABLE contacts FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES";// IS LOCAL right?
MySQL_query($sql);
$sql2 = "UPDATE contacts SET userid = '$userid';
MySQL_query($sql2);
echo "success;
这似乎是正确的方法吗?容纳来自不同用户的不同csv文件的难度有多大?
Does this seem like the right approach? How hard is it likely to be to accomodate different csv files from different users?
非常感谢.
推荐答案
-
请牢记客户"从MySQL的角度来看，是直接连接到它的应用程序.在您的情况下，那就是PHP. PHP的客户端"确实是一个事实.用户的Web浏览器无关紧要-而且您几乎可以肯定不希望用户直接连接到MySQL服务器.
Bear in mind the "client" from MySQL's perspective is the application which connects directly to it. In your case, that's PHP. The fact that PHP's "client" is the user's web-browser is irrelevant - and you almost certainly don't want your users connecting directly to the MySQL server.
因此，如果它们在不同的计算机上运行，则可以使用LOCAL将文件从PHP传递到MySQL，但是如果它们在同一计算机上并且MySQL可以从任何地方进行读取，则不必要PHP已写入文件.
So, you might use LOCAL to pass the file from PHP to MySQL if they're running on different machines, but that'd be unnecessary if they're on the same machine and MySQL has access to read from wherever PHP has written the file.
这实际上取决于您要对数据进行什么处理，但总的来说-是的，将它们全部放在一张表中.
It really depends what you're going to do with the data, but on the whole - yes, put it all in one table.
在LOAD DATA语句中，您可以告诉MySQL表中的CSV映射中的哪些列-您可以根据文件内容进行猜测(特别是如果它包含标题行)，或者基于用户提供的提示，例如CSV源自哪个软件；或者，如果您无法确定CSV如何映射到表格，则可以在浏览器中向用户展示一个简短的示例，并请他们解决.
In the LOAD DATA statement you can tell MySQL which columns in the CSV map to which in the table - you could either make some guesses based on the file contents (especially if it includes a header row), or based on a hint provided by the user e.g. from which software the CSV originated; or, if you can't decide how the CSV maps to the table, you could present a short sample to the user in a browser and ask them to resolve it.
这篇关于PHP/MYSQL上传，将.csv文件导入mysql-process-table设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
