Advanced search from multiple fields with PDO
<p>I'm trying to make advanced search form with PDO but can't understand how to pass bound variables in prepared statements.</p>

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

<p>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:</p>

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

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

                            echo '&lt;option value='.$row_program['field_name'].'&gt;'.$row_program['field_name'].'&lt;/option&gt;'; 
                        }
                        echo '&lt;/select&gt;';
                    ?&gt;
            &lt;/div&gt;
</code></pre>

<p>And this is my try in PHP part</p>

<pre><code>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-&gt;prepare($query);
                $stmt-&gt;bindValue(":search_title", "%" . $tdkey . "%");
                $stmt-&gt;bindValue(":search_description", "%" . $tdkey . "%");
                $stmt-&gt;bindValue(":search_keywords", "%" . $tdkey . "%");                                       
                $stmt-&gt;bindValue(":search_category", "%" . $upload_category . "%");
                $stmt-&gt;bindValue(":search_program", "%" . $upload_program . "%");


                $stmt-&gt;execute();                               
                $result = $stmt-&gt;fetchAll();            
                if ($stmt-&gt;rowCount() &gt; 0) { 
                   //results
                }
</code></pre>

<p>The error is </p>

<blockquote>
  <p>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?</p>
</blockquote>

<p>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 ..</p>