php - 通过 CSV 复制记录更新数据库
问题描述
我正在尝试从网页更新我的数据库。我与数据库的连接正常工作,并且能够从网页更改数据库中的记录。我目前的问题是,当我上传 CSV 时,网页会更改记录,但会将数据库中的所有记录更改为 CSV 的最后一行。它为 CSV 中的每个项目创建一行,但每行都被复制到同一条记录中。谁能指出哪里可能有错误?
$connect = mysqli_connect("$dbHost", "$dbUsername", "$dbPassword",
"$dbName");
$message = '';
if(isset($_POST["upload"]))
{
if($_FILES['product_file']['name'])
{
$filename = explode(".", $_FILES['product_file']['name']);
if(end($filename) == "csv")
{
$handle = fopen($_FILES['product_file']['tmp_name'], "r");
while($data = fgetcsv($handle))
{
$device_state = mysqli_real_escape_string($connect, $data[0]);
$last_seen = mysqli_real_escape_string($connect, $data[1]);
$device_type = mysqli_real_escape_string($connect, $data[2]);
$site_number = mysqli_real_escape_string($connect, $data[3]);
$meritage_site_number = mysqli_real_escape_string($connect,
$data[4]);
$store_address = mysqli_real_escape_string($connect, $data[5]);
$city = mysqli_real_escape_string($connect, $data[6]);
$state = mysqli_real_escape_string($connect, $data[7]);
$ip_address = mysqli_real_escape_string($connect, $data[8]);
$hostname = mysqli_real_escape_string($connect, $data[9]);
$node_number = mysqli_real_escape_string($connect, $data[10]);
$real_status = mysqli_real_escape_string($connect, $data[11]);
$short_model = mysqli_real_escape_string($connect, $data[12]);
$software_version = mysqli_real_escape_string($connect, $data[13]);
$query = "
UPDATE offline_devices
SET
device_state = '$device_state',
last_seen = '$last_seen',
device_type = '$device_type',
site_number = '$site_number',
meritage_site_number = '$meritage_site_number',
store_address = '$store_address',
city = '$city',
state = '$state',
ip_address = '$ip_address',
hostname = '$hostname',
node_number = '$node_number',
real_status = '$real_status',
short_model = '$short_model'
WHERE software_version = '$software_version'
";
mysqli_query($connect, $query);
}
fclose($handle);
header("location: ORBupload.php?updation=1");
}
else
{
$message = '<label class="text-danger">Please Select CSV File
only</label>';
}
}
else
{
$message = '<label class="text-danger">Please Select File</label>';
}
}
if(isset($_GET["updation"]))
{
$message = '<label class="text-success">Database Updated</label>';
}
$query = "SELECT * FROM offline_devices";
$result = mysqli_query($connect, $query);
当我上传文件时,数据库会为每一行复制相同的记录。数据库中的每一行都重复 CVS 的最后一行。
解决方案
如果没有看到您的数据库结构和数据或 CSV 文件的内容,我无法确定问题出在哪里,但我认为您应该非常仔细地检查您的查询。在我看来,这可能是问题所在。如果您的数据库中的每条记录都具有相同的software_version,那么您每次使用该 software_version 值运行此查询时都会更新数据库中的每条记录:
$query = "
UPDATE offline_devices
SET
device_state = '$device_state',
last_seen = '$last_seen',
device_type = '$device_type',
site_number = '$site_number',
meritage_site_number = '$meritage_site_number',
store_address = '$store_address',
city = '$city',
state = '$state',
ip_address = '$ip_address',
hostname = '$hostname',
node_number = '$node_number',
real_status = '$real_status',
short_model = '$short_model'
WHERE software_version = '$software_version'
";
该查询不应该检查 product_id 或其他内容吗?也许node_number?
推荐阅读
- http - CGI 脚本如何知道请求者的 IP 地址?
- c# - 如何访问在 Xamarin.Forms C# 代码后面创建的组件?
- vb6 - VB6 'Out of memory' 可以在一台机器上编译,但不能在另一台机器上编译
- android - Unity3d为什么我所有的模型都在移动中闪烁?
- python - 将 boto3 BaseCondition 转换为其字符串表示形式
- html - 改进在没有 JavaScript 的情况下提交到电子邮件的 HTML 表单的格式?
- python - 在视图中按月分组
- java - 使用动态字段反序列化嵌套 XML
- linux-kernel - 在 AMD 中使用 perf_event_open 对内存负载进行计数和采样
- dynamics-crm - 找不到 Dynamics CRM 2011 安装程序