我有一个将一组GPS字符串写入这样的文本文件的应用程序:
[{" date":" 02/13/2017 19:26:00"," TIME":1486974360428,"经度":151.209900," Latitude":-33.865143}/2017 19:26:13"," Time":1486974373496,"经度":151.209900," Latitude":-33.865143} {" date":" 02/13/13/13/20171486974383539,"经度":151.209900," latitude":-33.865143} {{" date":" 02/13/2017 19:26:33","时间","时间":1486974393349,"33.865143} {" date":" 02/13/2017 19:26:43"," TIME":14869744403423,"经度":151.209900," Latitude":-33.865143} { - 33.865143} {19:26:53","时间":1486974413483,"经度":151.209900," latitude":-33.865143}]
文件始终以[]
启动和结尾。
此文件上传到
的Ubuntu服务器'filepath'/uploads/gps/'device ID'/'year-month-day'/'UTC download time'.txt
例如
/uploads/gps/12/2017-02-12/1486940878.txt
当文件上传到服务器时,创建文本文件,因此每天写多个文件。
我想用标题设备(从filepath获得),日期,时间,经度,纬度将值写入MySQL数据库的方法。
最初,只有我可以在服务器上运行的命令,这是可取的,我最终可以从管理面板上的PHP命令运行。
我在哪里开始?
而不是上传,您可以轻松地将文本提交到服务器上的PHP程序。它将使用JSON解码将其转换为数组,然后将每个记录保存到表。设备ID将是脚本的参数之一。
使用这种类型的方法将消除许多问题,例如不导入文件,将文件重命名/移动文件,找到文件等。
。这也意味着您的数据每次发送数据时都是最新的。
这样的脚本编写非常琐碎,但是它应该内置某种类型的安全性,以防止数据被未经授权的实体发送。
这是一些示例代码,可以处理文件并将其存储到数据库中。我删除了您需要编辑的某些信息(用户ID/密码数据库名称)。我猜对了,但仍然很短。如果您需要更多信息,请下午我。
<?php
/* ===============================================================
Locate and parse GPS files, then store to MySQL DB.
Presumes a folder stucture of gps/device_id/date:YYYY-MM-DD.
After a file is processed and stored in the DB table, the
file is renamed with a leading "_" so it will be ignored later.
===============================================================
*/
$DS = '/'; // Directory separator character. Use '/' for Linux, '' for windows.
// Path to folder containing device folders.
$base_folder = "./gps";
// Today's date foratted like the folders under the devices. If parameter "date" has a value, use it instead of today's date. Parameter MUST be formatted correctly.
$today = isset($_REQUEST['date']) && $_REQUEST['date'] != '' ? $_REQUEST['date'] : date('Y-m-d');
// Get a list of device folders
$device_folders = get_folders($base_folder);
// Loop through all of the device folders
$num_file_processed = 0;
foreach($device_folders as $dev_folder) {
// Check to see if there is a folder in the device folder for today.
$folder_path = $base_folder.$DS.$dev_folder.$DS.$today;
// Check if the device/date folder exists.
if(file_exists($folder_path) && is_dir($folder_path)) {
// Folder exists, get a list of files that haven't been processed.
$file_list = get_files($folder_path);
// Process the files (if any)
foreach($file_list as $filename) {
$f_path = $folder_path.$DS.$filename;
$json = file_get_contents($f_path);
// Fix the JSON -- missing "," between records.
$json = str_replace("}{","},{",$json);
$data = json_decode($json);
// Process each row of data and save to DB.
$num_saved = 0;
$rec_num = 0;
foreach($data as $recno => $rec_data) {
if(save_GPS($dev_folder,$rec_data->date,$rec_data->time,$rec_data->longitude,$rec_data->latitude)) {
$num_saved++;
}
$rec_num++;
}
// Rename file so we can ignore it if processing is done again.
if($num_saved > 0) {
$newName = $folder_path.$DS."_".$filename;
rename($f_path,$newName);
$num_file_processed++;
}
}
} else {
echo "<p>" . $folder_path . " not found.</p>n";
}
}
echo "Processing Complete. ".$num_file_processed." files processed. ".$num_saved." records saved to db.n";
function save_GPS($dev_id,$rec_date,$rec_time,$long,$lat) {
$server = "localhost";
$uid = "your_db_user_id";
$pid = "your_db_password";
$db_name = "your_database_name";
$qstr = "";
$qstr .= "INSERT INTO `gps_log`n";
$qstr .= "(`device`,`date`,`time`,`longitude`,`latitude`)n";
$qstr .= "VALUESn";
$qstr .= "('".$dev_id."','".$rec_date."','".$rec_time."','".$long."','".$lat."');n";
$db = mysqli_connect($server,$uid,$pid,$db_name);
if(mysqli_connect_errno()) {
echo "Failed to connect to MySQL server: " . mysqli_connect_errno() . " " . mysqli_connect_error() . "n";
return false;
}
// Connected to DB, so save the record
mysqli_query($db,$qstr);
mysqli_close($db);
return true;
}
function get_folders($base_folder) {
$rslts = array();
$folders = array_map("htmlspecialchars", scandir($base_folder));
foreach($folders as $folder) {
// Ignore files and folders that start with "." (ie. current folder and parent folder references)
if(is_dir($base_folder."/".$folder) && substr($folder,0,1) != '.') {
$rslts[] = $folder;
}
}
return $rslts;
}
function get_files($base_folder) {
$rslts = array();
$files = array_map("htmlspecialchars", scandir($base_folder));
foreach($files as $file) {
// Ignore files and folders that start with "." (ie. current folder and parent folder references), or "_" (files already processed).
if(!is_dir($file) && substr($file,0,1) != '.' && substr($file,0,1) != '_') {
$rslts[] = $file;
}
}
return $rslts;
}