php - 如何根据特定领域进行搜索？

Question

我要求在我们的网站中添加搜索部分，然后他们说您像这样单独添加它。正如您在下图中看到的那样，我做了所有事情并且它正在工作。

我现在想要的是当我写下作者姓名或姓氏，然后选择最新更新、日期范围或所有可用日期，然后点击搜索并获取结果。我可以通过单独搜索获得结果，但是当我输入作者姓名并选择日期时，我无法获得准确的结果。

这里是按字段代码搜索:

<?php 
    $user_id = $_SESSION['projectUserId'];
        $text = $_POST['text'];
        $field = $_POST['field'];
        if($field == 'authors'){
            $userQuerys = "SELECT * FROM user where name like '%$text%' or middlename like '%$text%' or surname like '%$text%'";
            $users = mysqli_query($link, $userQuerys);
            $userids = '';
            while($a = mysqli_fetch_object($users))
            {
                $userids .= $a->user_id.',';
            }
            $userids = rtrim($userids,",");
        } 

          if($_SESSION['projectUserType'] == 1 or $_SESSION['projectUserType'] == 4)
          {
                if($field == 'authors'){
                $querys = "SELECT p.* FROM publication p left join pub_author a on a.pub_id=p.id where p.private = '0' and a.user_id in ('$userids')";
                }else{
                $querys = "SELECT p.* FROM publication p where p.private = '0' and $field = '$text'";
                }
          }
          elseif($_SESSION['projectUserType'] == 3) {
              $dep = $_SESSION['userDepartmantId'];
              if($field == 'authors'){
                $querys = "SELECT p.* FROM publication p left join pub_author a on a.pub_id=p.id where p.private = '0' and a.user_id in('$userids') and pub_department_id='$dep_id'";
                }else{
                $querys = "SELECT p.* FROM publication p  where p.private = '0' and and $field = '$text'";
                }
          }
          elseif($_SESSION['projectUserType'] == 5){
              $deps = $_SESSION['userDepartmantId'];
              $query = "SELECT *  from uni_department where id='$dep_id'";
              $faculties = mysqli_query($link, $query);
              while($a = mysqli_fetch_object($faculties))
              {
                  $fac_id = $a->faculty_id;
              }
              $query = "SELECT * from uni_department where faculty_id ='$fac_id'";
              $faculties = mysqli_query($link, $query);
              while($a = mysqli_fetch_object($faculties))
              {
                  $facDeps .= $a->id . ',';
              }
              $ids = rtrim($facDeps,",");
              if($field == 'authors'){
                $querys = "SELECT p.* FROM publication p left join pub_author a on a.pub_id=p.id where p.private = '0' and a.user_id in('$userids') and  p.pub_department_id IN ($ids)";
                }else{
                $querys = "SELECT p.* FROM publication p where p.private = '0' and $field = '$text' and pub_department_id IN ($ids)";
                }
          }
          else{
              echo '<script type="text/javascript">';
              echo 'window.location = "unauthorized.php"';
              echo '</script>';
          }
            $results5 = mysqli_query($link, $querys);
            $num_rows5 = mysqli_num_rows($results5);
            echo ' '.$num_rows5;
?>

这里是按年份代码搜索:

<?php 
    $search = $_POST['search'];

    $user_id = $_SESSION['projectUserId'];
          $year = $_POST['year'];
          $year2 = $_POST['year2'];
          $yearfirst = "$year-01-01";
          $yearlast= "$year2-12-31";
      if($_SESSION['projectUserType'] == 1 or $_SESSION['projectUserType'] == 4)
      {
            if($search == 1 ) { //latest added
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' order by p.year ASC limit 1";
            } elseif($search == 2){
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and p.year>='$yearfirst' and p.year<='$yearlast'";
            } else {
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0'";
            }
      }
      elseif($_SESSION['projectUserType'] == 3) {
          $dep = $_SESSION['userDepartmantId'];
            if($search == 1 ) { //latest added
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and pub_department_id='$dep_id' order by p.year ASC limit 1";
            } elseif($search == 2){
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and p.year>='$yearfirst' and p.year<='$yearlast' and pub_department_id='$dep_id'";
            } else {
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and pub_department_id='$dep_id'";
            }
      }
      elseif($_SESSION['projectUserType'] == 5){
          $deps = $_SESSION['userDepartmantId'];
          $query = "SELECT *  from uni_department where id='$dep_id'";
          $faculties = mysqli_query($link, $query);
          while($a = mysqli_fetch_object($faculties))
          {
              $fac_id = $a->faculty_id;
          }
          $query = "SELECT * from uni_department where faculty_id ='$fac_id'";
          $faculties = mysqli_query($link, $query);
          while($a = mysqli_fetch_object($faculties))
          {
              $facDeps .= $a->id . ',';
          }
          $ids = rtrim($facDeps,",");
            if($search == 1 ) { //latest added
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and and pub_department_id IN ($ids) order by p.year ASC limit 1";
            } elseif($search == 2){
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and p.year>='$yearfirst' and p.year<='$yearlast' and pub_department_id IN ($ids)";
            } else {
                $querys = "SELECT p.* FROM publication p left join pub_author a on p.id=a.pub_id where p.private = '0' and pub_department_id IN ($ids)";
            }

      }
      else{
          echo '<script type="text/javascript">';
          echo 'window.location = "unauthorized.php"';
          echo '</script>';
      }
            $results5 = mysqli_query($link, $querys);
            $num_rows5 = mysqli_num_rows($results5);
            echo ' '.$num_rows5;
?>

这是通用表单代码:

<form id="formValidation" action="publication_search_list2.php" method="post" enctype="multipart/form-data" >
        <fieldset>
        <div style="display:block; padding:20px; background-color:#f5f5f5;">
            <h2>Search By Field</h2>
                <input type="text" name="text" id="text" class="small-input" />
                <select name="field" id="field" class="small-input">
                        <option value="-1">Select Field</option>
                        <option value="authors">Authors</option>
                        <option value="title">Publication Title</option>
                        <option value="isbn">ISBN</option>
                        <option value="issue">ISSUE</option>
                </select>
                  <p>
                      <input class="button" type="submit" value="Search" />
                  </p>

              <!-- End 1. Step -->
        </div>
        </fieldset>
          <div class="clear"></div>
          <!-- End .clear -->
        </form>

    <div style="display:block; padding:20px; background-color:#f5f5f5;">        
        <form id="formValidation" action="publication_search_list3.php" method="post" enctype="multipart/form-data" >
        <h2>Search By Year</h2>
            <input type="radio" name="search" value="1">Search Latest Update<br>
            <input type="radio" name="search" value="2">Year Range
                <select name="year" id="year" class="small-input">
                    <option value="-1">Select</option>
                    <?php
                    $date = date(Y);
                    $rows= $date;
                    for($rows = $date; $rows>=$date-100; $rows--)
                    {
                      ?>
                      <option value="<?php echo $rows; ?>"><?php echo $rows; ?></option>
                    <?php
                    }
                    ?>
                </select>-<select name="year2" id="year2" class="small-input">
                    <option value="-1">Select</option>
                    <?php
                    $date = date(Y);
                    $rows= $date;
                    for($rows = $date; $rows>=$date-100; $rows--)
                    {
                    ?>
                    <option value="<?php echo $rows; ?>"><?php echo $rows; ?></option>
                    <?php
                    }
                    ?>
                </select>
                <br><input type="radio" name="search" value="3">All Available Years
                <p>
                  <input class="button" type="submit" value="Search" />
                </p>
        </form>
    </div>

Answer 1

尝试在填充 2 个或更多输入时设置大小写，即

if($author_is_filled){
$sql= 'Select ...from ... where...';
   if($search_range){
   $sql .= 'and range = '.$search_range;
   }
   if($date1){
   $sql .= 'and date1 ="'.$date1.'"';
   }
//...
}

这样你就可以执行单个sql。 =)