gpt4 book ai didi

php - 将 Excel 导入 mySQL 花费的时间太长

转载 作者:行者123 更新时间:2023-11-29 05:53:41 25 4
gpt4 key购买 nike

所以,我有一个表单,允许用户上传 excel 表,然后系统会将 excel 数据导入 mySQL。

但是,每次我使用 AJAX 提交表单时,它都会启动进程,保存大约一半的数据,然后给我一个 504 网关错误。我已经将 PHP 配置超时更改为 300,但它仍然在中途给出。我不认为 1000 行以下的 Excel 工作表应该花费 5 分钟以上?

这是我的代码:

 public function postImportGroup(Request $request)
{

if($request->hasFile('import_numbers')) {
$file = $request->file('import_numbers');
$file_extension = Input::file('import_numbers')->getClientOriginalExtension();
$supportedExt = array('csv', 'xls', 'xlsx');

if (!in_array_r($file_extension, $supportedExt)) {
return response()->json([
'status' => 'error',
'msg' => 'Please make sure that the uploaded file is a valid CSV, XLS, XLSX sheet.',
]);
}

}

$results = Excel::load($file)->get();

$results = json_decode($results[0], true);

$class = new DailyGroup();
$class->title = $request->group_name;
$class->user_id = Auth::guard('client')->user()->id;
$class->entries = count($results);
$class->save();

foreach ($results as $r => $value) {
//$data = array_values($value);
//return $value["employee_number"];
$group = new DailyGroupLocations();

$address = $value["address"] . ',' . $value["city"] . ',' . $value["state"] . ',' . $value["zip"];
$c = $value["country"];

$file_contents = file_get_contents('https://maps.googleapis.com/maps/api/geocode/json?address=' . urlencode($address) . '&components=country:' . urlencode($c) .'&sensor=false&key=xxx');
$json_decode = json_decode($file_contents);


if (isset($json_decode->results[0])) {
$group->lat = $json_decode->results[0]->geometry->location->lat;
$group->lng = $json_decode->results[0]->geometry->location->lng;
}
$phone = preg_replace('/\D+/', '', $value["ph"]);
$phone = '1'.$phone;

$group->user_id = Auth::guard('client')->user()->id;
$group->group_id = $class->id;
$group->employee_number = $value["employee_number"];
$group->work_date = $value["work_date"]["date"];
$group->first_name = $value["name"];
$group->last_name = $value["lastname"];
$group->phone = $phone;
$group->email = $value["email"];
$group->job_number = $value["job_number"];
$group->address = $value["address"];
$group->city = $value["city"];
$group->state = $value["state"];
$group->zip = $value["zip"];
$group->country = $value["country"];
$group->job_name = $value["job_name"];
$group->location = $value["location"];
$group->shift_description = $value["shift_description"];
$group->shift_start = $value["shift_start_time"];
$group->shift_end = $value["shift_end_time"];
$group->post_hours = $value["post_hours"];
$group->save();
}

return response()->json([
'status' => 'success',
'msg' => 'All data uploaded successfully. Please wait for tables to refresh.',
//'url' => '/user/location/location-areas'
]);
}

我可以做些什么来优化它吗?我在 foreach 语句中运行了很多东西吗?我可以使用任何提示或技巧吗?

最佳答案

不是 excel 文件在拖延时间...是阻止您的代码执行的 google geocode 调用。

您可以获得一个 google api key 来加快您的进程。

引用:https://developers.google.com/maps/documentation/javascript/get-api-key

您还应该检查地理编码调用的响应状态

$status = json_decode->results[0]->status;

可能的状态值:

  • 好的
  • ZERO_RESULTS
  • OVER_QUERY_LIMIT
  • INVALID_REQUEST
  • UNKNOWN_ERROR

如果在您的情况下可能,您可以考虑对数据集进行预地理编码,将 lat 和 lng 值与地址一起存储,这样如果您期望或需要快速执行,就不必即时进行地理编码。

关于php - 将 Excel 导入 mySQL 花费的时间太长,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51756331/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com