php - 其中 LIKE 返回 0 并带有准备好的语句

Question

我有这个简单的高级搜索表单的代码，我花了几个小时想弄清楚我做不到..

//==============connect============//
$mysqli = new mysqli("localhost", "root", "AWAAWA@19", "SDE");

//==============connect============//


 $nationality=$_POST['nationality'];
 $birthplace=$_POST['birthplace'];
 $dob=$_POST['dob'];
 $study=$_POST['study'];
 $status=$_POST['status'];
 $kids=$_POST['kids'];
 $smoking=$_POST['smoking'];
 $covered=$_POST['covered'];
 $hobbies=$_POST['hobbies'];

//============ For Pagination ============
    $where .= "nationality LIKE ?";
    $params[] = $nationality;
    $type_string .="s";
if(empty($birthplace)){
    $where .= " and birthplace LIKE ?";
    $params[] = $birthplace;
    $type_string .="s";
}
if(isset($dob)){
     $where .= " and dob LIKE ?";
     $params[] = $dob;
    $type_string .="s";
}
if(isset($study)){
     $where .= " and study LIKE ?";
     $params[] = $study;
    $type_string .="s";
}
if(isset($status)){
     $where .= " and status LIKE ?";
     $params[] = $status;
    $type_string .="s";
}
if(isset($kids)){
     $where .= " and kids LIKE ?";
     $params[] = $kids;
    $type_string .="s";
}
if(isset($smoking)){
     $where .= " and smoking LIKE ?";
     $params[] = $smoking;
    $type_string .="s";
}
if(isset($covered)){
     $where .= " and covered LIKE ?";
     $params[] = $covered;
    $type_string .="s";
}
if(isset($hobbies)){
     $where .= " and hobbies LIKE ?";
     $params[] = $hobbies;
    $type_string .="s";
}

$from_section = 'app_female';
$sql_search= "SELECT id,name,nationality,birthplace,status,study
FROM $from_section
where $where 
order by id desc";
$stmt_search = mysqli_stmt_init ($mysqli);
if (!mysqli_stmt_prepare($stmt_search,$sql_search)) {
      printf("Error: %s.\n", $stmt_search->error);

}
else {
  mysqli_stmt_bind_param($stmt_search,$type_string, ...$params);
  mysqli_stmt_execute($stmt_search);
  mysqli_stmt_store_result($stmt_search);
  mysqli_stmt_bind_result($stmt_search, $id,$name,$nationality,$birthplace,$status,$study);
  echo $count_search = mysqli_stmt_num_rows($stmt_search);
}

mysqli_close($mysqli);

即使我在数据库中的记录与给定的搜索词匹配，它也总是返回 0.. 我在这里做错了什么？我在以前的项目中使用了代码，现在它不起作用或者我错过了什么？

标签： php

Answer 1

考虑避免通过条件构建任何 SQL，但对所有搜索组合使用一个 SQL 语句。具体来说，使用三元运算符将变量分配给非空$_POST值或null. 检查变量可能是多余的isset。$_POST然后在 SQL 中，运行COALESCE以检索第一个非空值：$_POST值或列本身（即，无过滤器）：

// ASSIGN VARIABLES TO POST VALUES ELSE ASSIGN AS null
$nationality = empty($_POST['nationality']) ? null : $_POST['nationality'];
$birthplace = empty($_POST['birthplace']) ? null : $_POST['birthplace'];
$dob = empty($_POST['dob']) ? null : $_POST['dob'];
$study = empty($_POST['study']) ? null : $_POST['study'];
$status = empty($_POST['status']) ? null : $_POST['status'];
$kids = empty($_POST['kid']) ? null : $_POST['kids'];
$smoking = empty($_POST['smoking']) ? null : $_POST['smoking'];
$covered = empty($_POST['covered']) ? null : $_POST['covered'];
$hobbies = empty($_POST['hobbies']) ?  null : $_POST['hobbies'];

$params = [$nationality, $birthplace, $dob, $study, $status,
           $kids, $smoking, $covered, $hobbies];
$type_string = "sssssssss";

// USE ONE SQL THAT IF VARIABLE IS NULL, SET COLUMN EQUAL TO ITSELF (I.E., NO FILTER)
$from_section = 'app_female';
$sql_search = "SELECT id, name, nationality, birthplace, status, study
               FROM $from_section
               WHERE COALESCE(nationality, 'empty') = COALESCE(?, nationality, 'empty')
                 AND COALESCE(birthplace, '1900-01-01') = COALESCE(?, birthplace, '1900-01-01')
                 AND COALESCE(dob, '1900-01-01') = COALESCE(?, dob, '1900-01-01')
                 AND COALESCE(study, 'empty') = COALESCE(?, study, 'empty')
                 AND COALESCE(status, 'empty') = COALESCE(?, status, 'empty')
                 AND COALESCE(kids, 'empty') = COALESCE(?, kids, 'empty')
                 AND COALESCE(smoking, 'empty') = COALESCE(?, smoking, 'empty')
                 AND COALESCE(covered, 'empty') = COALESCE(?, covered, 'empty')
                 AND COALESCE(hobbies, 'empty') = COALESCE(?, hobbies, 'empty')
               ORDER BY id desc";

$stmt_search = mysqli_stmt_init($mysqli);
mysqli_stmt_prepare($stmt_search, $sql_search)
mysqli_stmt_bind_param($stmt_search, $type_string, ...$params);
...