gpt4 book ai didi

php - 如何从txt文件导入mysql空值

转载 作者:行者123 更新时间:2023-11-29 14:12:37 25 4
gpt4 key购买 nike

我在 php 中编写了一段代码,其中:

  1. 打开一个txt文件,
  2. 阅读每一行,
  3. 使用explode()函数将行分成几段(基于空格)
  4. 将每个子字符串分配给变量并
  5. 将所有内容插入数据库中。

在这些行中,我有数字,有时还有 NULL 值。问题是,当我将所有内容插入 mysql 时,空值显示为 0 而不是 null

是否可以将txt中的空值显示为..数据库表中的空值?

如果我没有解释清楚,请告诉我。

谢谢。
d.

<!DOCTYPE HTML>
<?php

// This code:
// 1) opens a txt file, which is stored in a specified folder,
// 2) reads each line of the file,
// 3) inserts the data in the specified database


// Connect to the database
include('connect.php');
// Define the location of the txt file.
$myFile = "/Applications/MAMP/htdocs/php_test/data_correct.txt";
// Open the file
$fh = fopen($myFile,'r') or die ("Could not open:".mysql_error());
// !feof means "not the end of the file"
while (!feof($fh)){
// fgets: gets the first line of $fh and assigns it to the variable $line
// Each line can be up to 5000 characters long
$line = fgets($fh,5000);
// explode function separated the string by means of "tab" space
list($WN,$TOW,$Receiver_ID,$lat,$lon,$height,$SVID,$Azimuth,$Elevation,$Total_S4_L1,$SNR_L1,$Phi01_L1,$Phi03_L1,$Phi10_L1,$Phi30_L1,$Phi60_L1,$TEC_TOW_45,$dTEC_TOW_60_45,$TEC_TOW_30,$dTEC_TOW_45_30,$TEC_TOW_15,$dTEC_TOW_30_15,$TEC_TOW,$dTEC_TOW_15_TOW,$L1_lock_time,$L2_lock_time,$SI_index_L1,$Total_S4_L2,$SNR_L2,$Phi01_L2,$Phi03_L2,$Phi10_L2,$Phi30_L2,$Phi60_L2,$Lock_L2,$SI_index_L2,$Total_S4_L5,$SNR_L5,$Phi01_L5,$Phi03_L5,$Phi10_L5,$Phi30_L5,$Phi60_L5,$Lock_L5,$SI_index_L5)=explode(" ",$line);

// insert data into table in database. The name of the table is GPS.
// The primary key increases automatically. The null values are acceptable.

$query = ("INSERT INTO GPS (WN,TOW,Receiver_ID,lat,lon,height,SVID,Azimuth,Elevation,Total_S4_L1,SNR_L1,Phi01_L1,Phi03_L1,Phi10_L1,Phi30_L1,Phi60_L1,TEC_TOW_45,dTEC_TOW_60_45,TEC_TOW_30,dTEC_TOW_45_30,TEC_TOW_15,dTEC_TOW_30_15,TEC_TOW,dTEC_TOW_15_TOW,L1_lock_time,L2_lock_time,SI_index_L1,Total_S4_L2,SNR_L2,Phi01_L2,Phi03_L2,Phi10_L2,Phi30_L2,Phi60_L2,Lock_L2,SI_index_L2,Total_S4_L5,SNR_L5,Phi01_L5,Phi03_L5,Phi10_L5,Phi30_L5,Phi60_L5,Lock_L5,SI_index_L5)
VALUES ('".$WN."','".$TOW."','".$Receiver_ID."','".$lat."','".$lon."','".$height."','".$SVID."','".$Azimuth."','".$Elevation."','".$Total_S4_L1."','".$SNR_L1."','".$Phi01_L1."','".$Phi03_L1."','".$Phi10_L1."','".$Phi30_L1."','".$Phi60_L1."','".$TEC_TOW_45."','".$dTEC_TOW_60_45."','".$TEC_TOW_30."','".$dTEC_TOW_45_30."','".$TEC_TOW_15."','".$dTEC_TOW_30_15."','".$TEC_TOW."','".$dTEC_TOW_15_TOW."','".$L1_lock_time."','".$L2_lock_time."','".$SI_index_L1."','".$Total_S4_L2."','".$SNR_L2."','".$Phi01_L2."','".$Phi03_L2."','".$Phi10_L2."','".$Phi30_L2."','".$Phi60_L2."','".$Lock_L2."','".$SI_index_L2."','".$Total_S4_L5."','".$SNR_L5."','".$Phi01_L5."','".$Phi03_L5."','".$Phi10_L5."','".$Phi30_L5."','".$Phi60_L5."','".$Lock_L5."','".$SI_index_L5."')");



$result = mysqli_query($dbcon, $query) or die('no available data');

}// end of while loop
fclose($fh);

?>

创建表查询是:

CREATE TABLE `GPS` ( 
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`WN` INT, # GPS Week Number
`TOW` INT, # Time of Week (seconds)
`Receiver_ID` VARCHAR( 30), # Name of GPS receiver
`lat` FLOAT(10,6), # Latitude of the GPS receiver
`lon` FLOAT(10,6), # Longitude of the GPS receiver
`height` FLOAT(10,2), # Height
`SVID` INT, #Satellite ID
`Azimuth` INT, # Azimuth in degrees
`Elevation` INT, #Elevation in degrees
`Total_S4_L1` FLOAT(10,3), # Total S4 on L1 (dimensionless)
`SNR_L1` FLOAT(10,1), # SNR on L1 (db)
`Phi01_L1` FLOAT(10,3), # Phi01 on L1, 1-second phase sigma (radians)
`Phi03_L1` FLOAT(10,3), # Phi03 on L1, 3-second phase sigma (radians)
`Phi10_L1` FLOAT(10,3), # Phi10 on L1, 10-second phase sigma (radians)
`Phi30_L1` FLOAT(10,3), # Phi30 on L1, 30-second phase sigma (radians)
`Phi60_L1` FLOAT(10,3), # Phi60 on L1, 60-second phase sigma (radians)
`TEC_TOW_45` FLOAT(10,4), # TEC at TOW -45s (TECU --> TEC SLUNT)
`dTEC_TOW_60_45` FLOAT(10,4), # dTEC from TOW -60s to TOW -45s (TECU)
`TEC_TOW_30` FLOAT(10,4), # TEC at TOW -30s (TECU --> TEC SLUNT)
`dTEC_TOW_45_30` FLOAT(10,4), # dTEC from TOW -45s to TOW -30s (TECU)
`TEC_TOW_15` FLOAT(10,4), # TEC at TOW -15s (TECU --> TEC SLUNT)
`dTEC_TOW_30_15` FLOAT(10,4), # dTEC from TOW -30s to TOW -15s (TECU)
`TEC_TOW` FLOAT(10,4), # TEC at TOW (TECU --> TEC SLUNT)
`dTEC_TOW_15_TOW` FLOAT(10,4), # dTEC from TOW -15s to TOW (TECU)
`L1_lock_time` INT, # L1 lock time (seconds)
`L2_lock_time` INT, #L2 lock time (seconds)
`SI_index_L1` FLOAT (10,4), # SI Index on L1: (10*log10(Pmax)- 10*log10(Pmin))/(10*log10(Pmax)+10*log10(Pmin)) (dimensionless)
`Total_S4_L2` FLOAT(10,3), # Total S4 on L2 (GPS/GLO) or E5a (GAL) (dimensionless)
`SNR_L2` FLOAT(10,1), # SNR on L2 (dB)
`Phi01_L2` FLOAT(10,3), # Phi01 on L2 (GPS/GLO) or E5a (GAL), 1-second phase sigma (radians)
`Phi03_L2` FLOAT(10,3), # Phi03 on L2 (GPS/GLO) or E5a (GAL), 3-second phase sigma (radians)
`Phi10_L2` FLOAT(10,3), # Phi10 on L2 (GPS/GLO) or E5a (GAL), 10-second phase sigma (radians)
`Phi30_L2` FLOAT(10,3), # Phi30 on L2 (GPS/GLO) or E5a (GAL), 30-second phase sigma (radians)
`Phi60_L2` FLOAT(10,3), # Phi60 on L2 (GPS/GLO) or E5a (GAL), 60-second phase sigma (radians)
`Lock_L2` INT, # Lock time of L2 (GPS/GLO) or E5a (GAL) (seconds)
`SI_index_L2` FLOAT (10,4), # SI Index on L2 (GPS/GLO) or E5a (GAL) (dimensionless)
`Total_S4_L5` FLOAT(10,3), # Total S4 on L5 (GPS/GLO) or E5a (GAL) (dimensionless)
`SNR_L5` FLOAT(10,1), # SNR on L5 (dB)
`Phi01_L5` FLOAT(10,3), # Phi01 on L5 (GPS/GLO) or E5a (GAL), 1-second phase sigma (radians)
`Phi03_L5` FLOAT(10,3), # Phi03 on L5 (GPS/GLO) or E5a (GAL), 3-second phase sigma (radians)
`Phi10_L5` FLOAT(10,3), # Phi10 on L5 (GPS/GLO) or E5a (GAL), 10-second phase sigma (radians)
`Phi30_L5` FLOAT(10,3), # Phi30 on L5 (GPS/GLO) or E5a (GAL), 30-second phase sigma (radians)
`Phi60_L5` FLOAT(10,3), # Phi60 on L5 (GPS/GLO) or E5a (GAL), 60-second phase sigma (radians)
`Lock_L5` INT, # Lock time of L5 (GPS/GLO) or E5a (GAL) (seconds)
`SI_index_L5` FLOAT (10,4) # SI Index on L5 (GPS/GLO) or E5a (GAL) (dimensionless)
) # Default storage engine from version MySQL 5.5 is InnoDB

最佳答案

要将 NULL 传递给 MySQL,您只需这样做。

INSERT INTO table (field,field2) VALUES (NULL,'value')

在代码中,检查$var、$var2是否为null,如果是,则使用NULL代替'$var'或'$var2'。

$var = !empty($var) ? "'$var'" : "NULL";
$var2 = !empty($var2) ? "'$var2'" : "NULL";

$query = "INSERT INTO data (field1, field2)
VALUES ('$var', '$var2',)";

关于php - 如何从txt文件导入mysql空值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13221031/

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