PHP搜索栏用于过滤NULL上的HTML表格分隔符

Question

我有一个由mysql数据库填充的简单html表，以及一个用php创建新查询的函数，然后将其加载到查询中，该查询用于填充表，作为一种过滤方式。问题是，当我的数据库中有一个NULL值而不只是一个空值时，它会中断语句。

下面是用于生成SQL的值，该SQL将在过滤后填充表：

<?php
  if(isset($_POST['search'])) {
      $valueToSearch = $_POST['valueToSearch'];

      $query = "SELECT * FROM PatientEventLog WHERE CONCAT(`patientEventLogID`, `patientEventID`, `patientID`, `eventCreatorID`, `eventEdited`, `antibiotic`, `waterBroke`, `vertex`, `cervixDilation`, `effacedPercent`, `station`, `membranes`, `analgesia`, `augment`, `notes`, `visible`) LIKE '%" . $valueToSearch ."%'";
      echo "value to search:   \n";
      echo $valueToSearch;
      echo "\n \n       above search resluts";
      echo $query;
      echo "below search results";
      $search_result = filterTable($query);

  } else {
    $query = "select * from PatientEventLog";
    $search_result = filterTable($query);

  }

  function filterTable($query) {
    $connect = mysqli_connect("localhost", "root", "GMd-eUJ-L6m-RxF", "lndappDB");
    $filter_results = mysqli_query($connect, $query);
    echo $filter_results;
    return $filter_results;

  }
?>

为什么like语句不能提取这些NULL值？

Answer 1

使用COALESCE操作符：

$query = "SELECT * FROM PatientEventLog 
  WHERE CONCAT(
  COALESCE(`patientEventLogID`,''), 
  COALESCE(`patientEventID`,''), 
  COALESCE(`patientID`,''), 
  COALESCE(`eventCreatorID`,''), 
  COALESCE(`eventEdited`,''), 
  COALESCE(`antibiotic`,''), 
  COALESCE(`waterBroke`,''), 
  COALESCE(`vertex`,''), 
  COALESCE(`cervixDilation`,''),
  COALESCE(`effacedPercent`,''), 
  COALESCE(`station`,''), 
  COALESCE(`membranes`,''), 
  COALESCE(`analgesia`,''), 
  COALESCE(`augment`,''), 
  COALESCE(`notes`,''), 
  COALESCE(`visible`,'')
) LIKE '%" . $valueToSearch ."%'";

但是为了避免MySQL注入，最好使用$valueToSearch作为参数，下面是一个示例：How can I prevent SQL injection in PHP?