时间戳

首页 > 解决方案 > PHP - 从具有空值的日期列中插入日期时遇到问题。值在数组内

php - PHP - 从具有空值的日期列中插入日期时遇到问题。值在数组内

问题描述

我正在制作一个 SQL Server 到 MySQL 程序，它允许我在两个相同的表之间同步内容。但是，我无法从日期列中插入日期值，该日期列的某些单元格设置为 NULL。这应该工作的方式是它应该使用格式函数将日期列中的每个值转换为字符串。由于我是从一个数组中生成每一行并通过循环运行它，因此我无法手动挑选带有 NULL 的行。这是代码（它相当长，所以我删掉了一些部分，所以请多多包涵）：

//SELECT FROM SQL SERVER DB
$mssql_array = array();
$ms_sql = "SELECT * FROM health";
$mssql_query = sqlsrv_query($conn_mssql, $ms_sql);

while($row = sqlsrv_fetch_array($mssql_query))
{
  //turn individual rows into array    
  $mssql_array[] = array(
  'id' => $row['id'], 
  'period' => $row["period"], //date column; has NULLs in it that prevent insert
  'BP' => $row['BP'], 
  'pulserate' => $row['pulserate'],
  'temperature' => $row['temperature'],
  'labresult' => $row['labresult'],
  'waistline' => $row['waistline'],
  'weight' => $row['weight'],
  'BMI' => $row['BMI'],
  'findings' => $row['findings'],
  'diagnosis' => $row['diagnosis'],
  'plano' => $row['plano'] ... );
}

foreach($mssql_array as $key => $value)
{
  //SELECT FROM MySQL DB
  $my_sql = $conn_mysql->prepare(" SELECT * FROM health  
  WHERE id = ? 
  AND period = ?
  AND `BP` = ?
  AND pulserate = ?
  AND temperature = ? 
  AND labresult = ? 
  AND waistline = ? 
  AND weight = ? 
  AND BMI = ? 
  AND findings = ? 
  AND diagnosis = ? 
  AND plano = ? ... ");

  $my_sql->bind_param("issssssssssssssiissssssssiiiiss", $id, $period, $BP, $pulserate, $temperature, $labresult, $waistline, $weight, $BMI, $findings, $diagnosis, $plano ... );

  $id = $value["id"]; 
  $period = $value["period"]->format('Y-m-d H:i:s'); //prevents me from converting all date values to string...

  if(is_null($period))
  {
    unset($period);
  }

  $BP = $value["BP"]; 
  $pulserate = $value["pulserate"];
  $temperature = $value["temperature"];
  $labresult = $value["labresult"];
  $waistline = $value["waistline"];
  $weight = $value["weight"];
  $BMI = $value["BMI"];
  $findings = $value["findings"];
  $diagnosis = $value["diagnosis"];
  $plano = $value["plano"]; ...

  $my_sql->execute();
  $num_rows = $my_sql->num_rows();

  $my_sql->close();

  //PREPARE INSERT INTO MySQL DB
  $sql = $conn_mysql->prepare("INSERT INTO `health` VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");//31 total

  $sql->bind_param("issssssssssssssiissssssssiiiiss", $id, $period, $BP, $pulserate, $temperature, $labresult, $waistline, $weight, $BMI, $findings, $diagnosis, $plano ... );

  $id = $value["id"]; 
  $period = $value["period"]->format('Y-m-d H:i:s'); //...because it stops the process when it encounters a NULL value

  if(is_null($period))
  {
    unset($period);
  }

  $BP = $value["BP"]; 
  $pulserate = $value["pulserate"];
  $temperature = $value["temperature"];
  $labresult = $value["labresult"];
  $waistline = $value["waistline"];
  $weight = $value["weight"];
  $BMI = $value["BMI"];
  $findings = $value["findings"];
  $diagnosis = $value["diagnosis"];
  $plano = $value["plano"]; ...

    if($num_rows == 0)
      {
        $sql->execute();
      }
}

echo 'Table health from MS SQL DB and table health from MySQL DB are now synced!'."<br>";
echo "<a href='table_updater.php'>Go back to updater</a>";

它给我的错误是“致命错误：未捕获的错误：在 null 上调用成员函数 format()”，这几乎告诉我它不能在遇到的 NULL 值上使用格式函数。但是，使用格式功能，它会给我错误“对象日期时间无法转换为字符串”。我有什么办法可以解决这个问题吗？我无法更改数据库值本身，因为它不是我的。

标签： phpmysqlsqlmysqli

解决方案

在调用之前，您必须检查并确保该变量是 DateTime 对象format()：

$period = ($value["period"] instanceof DateTime) ? $value["period"]->format('Y-m-d H:i:s') : null;

推荐阅读