php - 使用 PDO 从多个字段进行高级搜索

Question

我正在尝试使用 PDO 制作高级搜索表单，但无法理解如何在准备好的语句中传递绑定(bind)变量。

我什至不确定这是正确的方法，需要一些帮助才能完成它。

因此，在表单上，​​我有一个输入字段，可在 3 列中进行搜索，另外还有两个下拉菜单，它们是附加搜索，但不是强制性的。这是形式:

<form class="form-horizontal" name="form-horizontal" action="" method="post" role="form">     
    <div class="container-fluid">
        <div class="row">        
            <h2>Enter the text which you want to search</h2>            
                  <div class="col-md-8">
                    <div id="custom-search-input">
                        <div class="input-group col-md-12">
                                <input type="text" class="form-control input-lg" placeholder="Search in title, description or keywords" name="tdkey"/>
                                <span class="input-group-btn">
                                    <button class="btn btn-info btn-lg" type="submit" name="submit">
                                        <i class="glyphicon glyphicon-search"></i>
                                    </button>
                                </span>
                        </div>
                    </div>
                    </div>
                    <div class="col-md-4">
                      <button type="button" class="btn btn-primary btn-lg" data-toggle="collapse" data-target="#filter-panel">
                        <span class="glyphicon glyphicon-cog"></span> Advanced Search
                    </button>               
                </div>

            </div>          
      </div>
          <div id="filter-panel" class="collapse filter-panel">                
        <div class="container-fluid">
          <div class="row">
            <div class="col-md-3">
                <h4>Select by Category</h4>
                    <?php 
                        $pdo = Database::connect();
                        $sql="SELECT field_name FROM form_fields WHERE group_id=6";                         
                        echo '<select class="form-control" name="upload_category" id="select2">';
                        foreach ($pdo->query($sql) as $upload_category){
                            echo '<option value='.$upload_category['field_name'].'>'.$upload_category['field_name'].'</option>';                                
                        }
                        echo '</select>';
                    ?>
            </div> 
            <div class="col-md-3">
                <h4>Select by Program</h4>
                    <?php 
                        $pdo = Database::connect();
                        $sql="SELECT field_name FROM form_fields WHERE group_id=1";                         
                        echo '<select class="form-control" name="upload_program" id="select2">';
                        foreach ($pdo->query($sql) as $row_program){

                            echo '<option value='.$row_program['field_name'].'>'.$row_program['field_name'].'</option>'; 
                        }
                        echo '</select>';
                    ?>
            </div>

这是我在 PHP 部分的尝试

if(isset($_POST["submit"])){
            $query = "";
            $tdkey = $_POST["tdkey"];
            $upload_program = $_POST["upload_program"];
            $upload_category = $_POST["upload_category"];

            if(isset($tdkey)){ // if tdkey is set select here
                $query = "SELECT * FROM document_upload WHERE upload_title LIKE :search_title OR upload_description LIKE :search_description OR upload_keywords LIKE :search_keywords";             

                if(isset($upload_category)){ // also if category is set add this to query
                    $query .= "AND upload_category LIKE :search_category";
                }
                if(isset($upload_program)){ // also if program is set add this
                    $query .= "AND upload_program LIKE :search_program";
                }
            } else if (isset($upload_category)){ // if tdkey isn't set go here 
                $query = "SELECT * FROM document_upload WHERE upload_category LIKE :search_category";
                if(isset($upload_program)){ // add this also if program is set to category
                    $query .= "AND upload_program LIKE :upload_program";
                }
            } else if (isset($upload_program)){ // if only country is set go here
                $query = "SELECT * FROM document_upload WHERE upload_program LIKE :search_program";
            } 
                $stmt = $pdo->prepare($query);
                $stmt->bindValue(":search_title", "%" . $tdkey . "%");
                $stmt->bindValue(":search_description", "%" . $tdkey . "%");
                $stmt->bindValue(":search_keywords", "%" . $tdkey . "%");                                       
                $stmt->bindValue(":search_category", "%" . $upload_category . "%");
                $stmt->bindValue(":search_program", "%" . $upload_program . "%");


                $stmt->execute();                               
                $result = $stmt->fetchAll();            
                if ($stmt->rowCount() > 0) { 
                   //results
                }

错误是

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens Do I need after each query to bound variables? Or there is more "intelligent" way to do this?

主要目标是从文本、描述和关键字字段中搜索，以及是否需要添加一些程序或类别。我认为最好的方法就像我在谷歌上做一些研究时发现的那样 - 动态创建 sql ..

Answer 1

您有一些仅在某些条件下需要的参数，然后尝试将它们全部绑定(bind)。

可能值得设置一个参数数组并添加相关条件下所需的参数。

您可以使用 foreach 将它们绑定(bind)到语句。

类似于:

$query = '';
$params = [];

if(isset($search_term_value)){
  $query= 'SELECT * FROM example WHERE term LIKE :search_term';
  $params['search_term'] = '%'.$search_term_value.'%';
}

$stmt = $pdo->prepare($query);
foreach($params as $key=>$value){
  $stmt->bindValue(':'.$key, $value);
}
$stmt->execute();  

而且，我很确定

AND upload_program LIKE :upload_program 

应该阅读

AND upload_program LIKE :search_program