如何将单个CSV行上载到PHP中的不同表中? [英] How can I upload individual CSV rows into different tables in PHP?
问题描述
我正在尝试找出执行此操作的最佳方法.我有一个数据库，其中有5个表，围绕MySQL的5个记录/行，我需要经常对其进行更新.问题是我上传的文件将是csv文件.另外，我希望每行进入5个不同的表.我正在尝试更新'where'有一个唯一的ID.
I'm trying to figure out the best way to do this. I have a database with 5 tables around 5 records/rows in MySQL and I need to update it frequently. The problem is the file I upload will be csv file. Also I want each lines to go into 5 different tables.,I'm trying to updated 'where' there is a unique id.
uid="001";
实现此目标的最佳方法是什么? 从早上开始我一直在尝试，而且我只知道一次只能更新一张桌子.非常感谢您的建议.谢谢.
What is the best way to achieve this? I've been trying since morning, and I only know how to update one table alone at a time. Your suggestions is most appreciated. Thanks.
csv示例和下面的单个表
sample of csv and individual table below
csv示例:
id | name | title |
-----------------------------------------------------
1 | james | Chief |
2 | job | officer |
3 | bats | lawyer |
4 | Supes | thief |
5 | WW20 | Lobster |
表1:
uid | name | title |
-----------------------------------
001 | | |
002 | | |
003 | | |
table2，table3和table4包含表一的数据和字段
table2, table3 and table4 contains the data and field as table one
我希望詹姆士和他的头衔进入表1， 工作和他对table2的标题， 蝙蝠和他对table3的称号， 等等. 全部在第001行
I want james and his title to go to table1, jobs and his title to table2, bats and his title to table3, e.t.c. all on row 001
关于唯一性，数据库(每个表)中的"UID"是主键.....我希望它导入它并使用uid作为每行的位置来更新表输入表格
About the uniquenes, the 'UID' in the database (each of the table) is the primary key..... and i want it to import it and update the tables using the uid as the location for each rows to enter the tables
下面是我如何在csv中获取值的方法，而忽略了仅是标题的第一行.
Below is how i got the values in the csv, ignoring first line,which is the header alone.
$uid="001";
if(isset($_POST["submit"]))
{
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
$name= $filesop[0];
$title= $filesop[1];
}
$sql = "UPDATE eyfstb SET name='$name',title='$title'WHERE uid='$uid'";
$stmt = mysqli_prepare($db,$sql);
mysqli_stmt_execute($stmt);
$c = $c + 1;
}
if($sql){
echo "sucess";
}
else
{
echo "Sorry! Unable to impo.";
}
}
推荐答案
在您的情况下，最好的解决方案是最简单的解决方案，因此只需创建五个查询，您甚至可以在循环中完成此操作:
In your case, the best solution is the simplest one, so just creating five queries, you can even do it in the loop:
$pdo = new PDO("mysql:host=127.0.0.1;dbname=yourdbname;charset=utf8", "username", "password");
if (($handle = fopen("test.csv", "r")) !== FALSE) {
$row = 1;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if ($row == 1) {
$row++;
continue;
}
$row++;
foreach (['table1', 'table2', 'table3', 'table4', 'table5'] as $table) {
$stmt = $pdo->prepare("INSERT INTO $table (name, title) VALUES (?,?)");
$stmt->execute([$data[0], $data[1]]);
}
}
fclose($handle);
}
或者对于使用uid的UPDATE替换前叉:
Or for UPDATE with uid replace the forech:
foreach (['table1', 'table2', 'table3', 'table4', 'table5'] as $table) {
$stmt = $pdo->prepare("UPDATE $table SET name=?, title=? WHERE uid=?");
$stmt->execute([$data[0], $data[1], $uid]);
}
使用INSERT或UPDATE甚至更好，请注意，在这种情况下，我们使用的是命名参数.
Or even better with INSERT or UPDATE, note that in this case we're using named params.
foreach (['table1', 'table2', 'table3', 'table4', 'table5'] as $table) {
$stmt = $pdo->prepare("INSERT INTO $table (uid, name, title)
VALUES (:uid, :name, :title)
ON DUPLICATE KEY UPDATE name=:name, title=:title");
$stmt->bindValue('uid', $uid);
$stmt->bindValue('name', $data[0]);
$stmt->bindValue('title', $data[1]);
$stmt->execute();
}
用于table1的SQL .. table5
SQL for table1 .. table5
CREATE TABLE table1 (
uid int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
title varchar(255) NOT NULL,
PRIMARY KEY (uid)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
注意:当您将更好地描述如何保持唯一性时，我可能会添加一些其他解决方案.目前，代码不知道CSV主管James是DB主管James.
Note: When you'll describe better how do you want to maintain uniqueness I'll probably add some other solutions. At the moment code doesn't know if James, chief from CSV is the same James, chief in DB.
这篇关于如何将单个CSV行上载到PHP中的不同表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
