php - 如何选择与(姓名，姓氏)OR(姓氏，姓名)串联匹配的所有记录

Question

我正在使用 ajax 实时搜索来选择名称和姓氏的串联与输入的文本匹配的所有用户，并且效果很好:

$sql = "SELECT * FROM users WHERE concat(name,' ',surname ) LIKE ?";

现在，我想将相同的用户与相反的串联(即姓氏 - 姓名)相匹配。我已经尝试过了，但没有成功。

$sql = "SELECT * FROM users WHERE (concat(name,' ',surname ) LIKE ?) or (concat(surname,' ',name ) LIKE ?)";

if(isset($_REQUEST["term"])){
  // Prepare a select statement
  $sql = "SELECT * FROM users WHERE (concat(name,' ',surname ) LIKE ?) or (concat(surname,' ',name ) LIKE ?)";

  if($stmt = mysqli_prepare($conn, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "s", $param_term);

    // Set parameters
    $param_term = $_REQUEST["term"] . '%';

    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
      $result = mysqli_stmt_get_result($stmt);

      // Check number of rows in the result set
      if(mysqli_num_rows($result) > 0){
        // Fetch result rows as an associative array
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
          echo "<p>" . $row["name"] . " " . $row["surname"] .  "</p>" 
        }
      } else{
        echo "<p>No matches found</p>";
      }
    } else{
      echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
    }
  }

  // Close statement
  mysqli_stmt_close($stmt);
}

我期望匹配相同的用户，但我收到消息:

ERROR: Could not able to execute SELECT * FROM users WHERE (concat(name,' ',surname ) LIKE ?) or (concat(name,' ',surname ) LIKE ?).

Answer 1

您的查询字符串中有两个占位符 ?，但只将一个变量绑定(bind)到语句。尝试第二次绑定(bind)它，以便计数匹配。

mysqli_stmt_bind_param($stmt, "ss", $param_term, $param_term);