使用PDO从多个领域进行高级搜索

Question

I'm trying to make advanced search form with PDO but can't understand how to pass bound variables in prepared statements.

I'm not even sure that this is right approach and will need some help to finish it.

So on the form I have one input field which search in 3 columns + two more dropdowns which are additions search but not mandatory. This is the form:

<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>

And this is my try in PHP part

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
                }

The error is

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?

Main goal is to search from field where is text, description and keywords and if need to add some program or category. And I think the best approach is like this what I found while I do some research on google - dynamically creating sql ..

Answer 1

You have some params that are only required for certain conditions and then are trying to bind them all regardless.

It might be worth setting up a params array and adding params that you need in the relevant conditions.

You can use a foreach to bind them to the statement.

Something like:

$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();  

Also, I'm pretty sure

AND upload_program LIKE :upload_program 

should read

AND upload_program LIKE :search_program