javascript - MySQL 查询搜索包括来自错误列的结果

Question

我有一个返回正确结果的搜索栏，除了它包含来自我不想被搜索的列的结果。

MySQL 查询（文件名search2.php）：

if ($_SERVER["REQUEST_METHOD"] === "POST") {
  $submitted_search = $_POST['search'];
}

$safe_search = '%' . $submitted_search . '%'; //I know it's not 'safe' yet-bound below!

$sqlSearch = "
SELECT tbl.title
     , tbl.artists
     , tbl.date_starting
     , tbl.date_ending
     , tbl.opening_date
     , tbl.opening_time
     , tbl.category
     , tbl.cost
     , tbl.place_decode_strip
     , tbl.title_decode_strip
     , tbl.artists_decode_strip
     , place.site
     , place.addr
     , place.hours
     , place.web
     , place.admis
  FROM tbl
 JOIN place
    ON tbl.place = place.site
 WHERE CONCAT_WS(' || ', tbl.date_ending, tbl.opening_date, tbl.place_decode_strip, 
tbl.title_decode_strip, tbl.artists_decode_strip,
place.aka)
LIKE ?
 ORDER 
    BY date_ending DESC
";

$stmt = $conn->prepare($sqlSearch);
$stmt->bind_param("s", $safe_search);
$stmt->execute();
$data = $stmt->get_result();

$searchResultsNum = mysqli_num_rows($data);

if ($searchResultsNum === 0) {
  echo "<h3>There are no results matching your search.</h3>";
} elseif ($searchResultsNum === 1) {
  echo "<h3>There is 1 result matching &ldquo;<i>" . $submitted_search . "</i>&#8239;&rdquo;.</h3>";
} else {
  echo "<h3>There are " . $searchResultsNum . " results matching &ldquo;<i>" . $submitted_search . "</i>&#8239;&rdquo;.</h3>";
}

if ($searchResultsNum > 0) {
  // data of each row
  while ($searchRow = $data->fetch_assoc()) {
    $date = strtotime($searchRow["opening_date"]);
    $sdate = strtotime($searchRow["date_starting"]);
    $edate = strtotime($searchRow["date_ending"]); ?>
    <section class="entry">
      <article class="site-info">
        <p class="site"><?php $web = $searchRow["web"];
                        echo "<a href='$web' target='_blank' rel='noreferrer'>" . $searchRow["site"]; ?></a>
        </p>
        <p class="site-add"><?php echo $searchRow["addr"]; ?></p>
        <p class="site-hrs"><?php echo $searchRow["hours"]; ?></p>
      </article>

      <article class="event-info">
        <p class="title"><?php echo $searchRow["title"]; ?></p>
        <p class="artists"><?php echo $searchRow["artists"]; ?></p>
        <?php if ($searchRow["date_starting"] != $searchRow["date_ending"]) {
        ?><p><?php
              echo date("F j", $sdate) . " – ";
              echo date("F j, Y", $edate); ?></p>
          <p><?php
            }
            echo $searchRow["category"] . ": ";
            echo date("F j", $date) . ", ";
            echo $searchRow["opening_time"]; ?></p>
          <?php if ($searchRow["cost"] !== null) { ?>
            <p><?php echo $searchRow["cost"]; ?></p>
          <?php } ?>
      </article>
    </section>
<?php }
}
?>

JavaScript：

const searchButton = document.getElementById('search-btn');
searchButton.addEventListener("click", stopRedirect);

function submitSearch() {
  const searchInput = document.getElementById('searchInput').value;
  //  const searchResultId = document.getElementById('search-results');
  if (!searchInput) {
    document.getElementById('search-results').innerHTML = "Please enter search term."
  } else if (searchInput == ' ') {
    document.getElementById('search-results').innerHTML = "Please enter a valid search term."
  } else if (searchInput == '%') {
    document.getElementById('search-results').innerHTML = "Please enter a valid search term."
  } else {
    var formData = new FormData();
    formData.append('search', searchInput);

    var xhttp = new XMLHttpRequest();
    xhttp.open("POST", "../phpScripts/search2.php", true);

    xhttp.onreadystatechange = function () {
      if (xhttp.readyState == 4 && xhttp.status == 200) {
        const response_data = xhttp.response;
        document.getElementById('search-results').innerHTML = response_data; //this.response
      }
    }
  }

  xhttp.send(formData);
}

function stopRedirect(e) {
  e.preventDefault();
}

HTML 搜索表单：

<aside class="search-field">
  <form class="search-form" id="search-form" action="/phpScripts/search2.php" method="POST" role="search">
    <input type="search" name="search" placeholder="Search all events" 
      id="searchInput" aria-label="Search through site content" />
    <input type="submit" value="Submit" id="search-btn" onclick="submitSearch()" />
  </form>
</aside>

当我搜索时，<我得到了正确的结果event_tbl.title（即，它包括带有 HTML 标记的标题），但我不想要该列的结果。tbl.title_decode_strip在这种情况下，我只想要解码和剥离列的结果。

为什么查询从 中返回结果tbl.title？如何将查询限制为不包括来自的搜索（WHEN）结果tbl.title？我需要包含tbl.title在查询中，因为这是在 AJAX 中返回的 HTML 的一部分。tbl.title不包含在WHEN查询部分中，所以我不知道为什么它包含在结果中。

标签： javascriptphpmysql