Building dynamic PDO Mysql Query

Question

I'm attempting to filter a list of results using the three following options:

Main Category, Subcategory and Search

Where the two category options are drop down lists and the search is a text box.

Here is my code so far:

files.php

    if (!isset($_GET['filter'])){
        $_GET['filter'] = "";
    }
    if (!isset($_GET['search'])){
        $_GET['search'] = "";
    }
    if (!isset($_GET['subcategory'])){
        $_GET['subcategory'] = "";
    }

// The form to filter the results:

<form method="get">
Category: <select name="filter">
    <option <?php if(!isset($_GET['filter'])){echo 'selected';} ?> value="">-- Select Category --</option>
    <option <?php if($_GET['filter'] == "1") {echo 'selected';} ?> value="1">View Vehicles Only</option>
    <option <?php if($_GET['filter'] == "2") {echo 'selected';} ?> value="2">View Lighting Equiptment</option>
</select><br /><br />
<?php 


        if(isset($_GET['filter'])){
            if($_GET['filter'] != ""){
                echo 'SubCategory:  <select name="subcategory">';
                    $sub_categories = getsubcategories($_GET['filter']);
                    foreach ($sub_categories as $cat){
                        echo '<option value = "'.$cat['cat_id'].'">'.$cat['cat_name'].'</option>';
                    }
                echo '</select><br /><br />';
            }
        }

    ?>
    Search Files: <input type="text" name="search" <?php if(isset($_GET['search'])){echo 'value="'.$_GET['search'].'"';}?> placeholder=" Enter a search term..." />
    <br /><br /><center><input type="submit" class="btn btn-default" value="Update Results"/> <a href="files.php" class="btn btn-default">Reset Filters</a></center>
    </form>

// Calling the function to retrieve the results:

$files = getbycategory($_GET['filter'], $_GET['search'], $_GET['subcategory']);

// Looping through the results:

foreach($files as $file){

echo'<div class="col-lg-" id="file-'.$file['part_id'].'">
        <div class="file-list-item first" id="">';
            if ($file['image_url'] == "")
            {
                echo '<img class="file-image" height="120px" width="180px" src="'.baseurl.'/resources/img/no-image.png" />';
            } else {
                echo '<img class="file-image" height="120px" width="180px" src="'.$file['image_url'].'" />';
            }   

            echo '
            <div class="file-text">
                <h3><strong>'.$file['part_name'].'</strong></h3>
                Submitted by: '.$file['submitter'].'<br/>
                Author: '.$file['author'].'<br />
                Category: '.ucfirst($file['subcategory']).'<br />
                Description: '.substr($file['description'],0,45).'...
            </div>
            <div class="download">
                <a target="_blank" href="'.$file['download_url'].'" class="btn-success btn btn-default">Download</a>
                <a href="'.baseurl.'/broken.php?id='.$file['part_id'].'" class="btn btn-default">Report as Broken</a><br /><br />';
                    if($file['is_broken']){
                        echo '<span class="broken"><i data-toggle="tooltip" data-placement="left" id="broken" title="This file has been reported as broken by \'Alcon H\' and is awaiting review." class="fa fa-warning fa-2x"></i></span>';
                    }


                echo '

            </div>
        </div>
    </div>';
};

file_functions.php

// This is the function that retrieves the results:

function getbycategory($category, $search, $subcategory){

    global $db;

    $sm = $db->prepare("SELECT * FROM parts WHERE main_category = :category AND active = 1 AND subcategory = :subcategory AND part_name LIKE :search");

        if ($category == ""){
            $category = '%';
            $sm->bindParam(":category", $category, PDO::PARAM_STR);    
        } else {
            $sm->bindParam(":category", $category, PDO::PARAM_STR);
        }

        if ($subcategory ==""){
            $subcategory = '%';
            $sm->bindParam(":subcategory", $subcategory, PDO::PARAM_STR);
        } else {
            $sm->bindParam(":subcategory", $subcategory, PDO::PARAM_STR);
        }

        if ($search == ""){
        $search = '%'.$search.'%';
        } else {
        $sm->bindParam(":search", $search, PDO::PARAM_STR);
        }

    $sm->execute();
    return $sm->fetchAll();
    }

The error that I am receiving is:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /var/www/html/partsdb/resources/file_functions.php:74\nStack trace:\n#0 /var/www/html/partsdb/resources/file_functions.php(74): PDOStatement->execute()\n#1 /var/www/html/partsdb/files.php(18): getbycategory('', '', '')\n#2 {main}\n  thrown in /var/www/html/partsdb/resources/file_functions.php on line 74, referer: http://localhost/partsdb/files.php

Line 74 of file_functions.php is just where it is executed.

Can anyone see where I am going wrong?

Answer 1

First use Ternary operator with isset().

$category = isset($_GET["category"]) ? $_GET["category"] : "";

Declare an array to hold parameters.

$params = array();

Then set the stub of query into variable.

$sql = "SELECT * FROM parts ";

Set a flag to 0 to switch from WHERE to AND for 2 or more parameters

Start building the rest of your query incrementing $flag adding parameters to $params array

if($category != ""){
        $sql .= " WHERE main_category = ?";
        $params[] = $category;
        $flag++; 
        }

If flag > 0 use AND instead of WHERE

if($search != ""){
       if($flag > 0){
            $sql .= " AND search = ?";

       }else{
            $sql .= " WHERE search = ?";
       }
       $params[] =$search;
       $flag++;

Use "lazy" binding passing data into execute.

$sm->execute($params);

The final code.

<?php
$category = isset($_GET["category"]) ? $_GET["category"] : "";
$search = isset($_GET['search']) ? $_GET['search'] : "";
$subcategory = isset($_GET['subcategory']) ? $_GET['subcategory'] : "";
$params = array();

function getbycategory($category, $search, $subcategory){


    global $db;
    $sql = "SELECT * FROM parts ";
    $flag = 0;
    if($category != ""){
        $sql .= " WHERE main_category = ?";
        $params[] = $category;
        $flag++; 
        }
    if($search != ""){
       if($flag > 0){
            $sql .= " AND search = ?";

       }else{
            $sql .= " WHERE search = ?";
       }
       $params[] =$search;
       $flag++;
        }   
    if($subcategory != ""){
       if($flag > 0){
            $sql .= " AND subcategory = ?";

       }else{
            $sql .= " WHERE subcategory = ?";
       }
       $params[] = $subcategory;
       }   

    echo   $sql; //Remove after testing  
    print_r($params);//Remove after testing     
    $sm = $db->prepare($sql);  
    $sm->execute($params);
    return $sm->fetchAll();
}   
getbycategory($category, $search, $subcategory);



    } 

Reading your code I see $category = '%'; If you require LIKE instead of = you require to prepare FULL literal first.

$category = "%$category%";

Answer 2

If this only happens when your search string is null, that is because:

    if ($search == ""){
        $search = '%'.$search.'%';
    } else {
        $sm->bindParam(":search", $search, PDO::PARAM_STR);
    }

only binds the parameter if the $search variable is not an empty string. To fix that you could modify it as:

    if ($search == ""){
        $search = '%'.$search.'%';
    } 

    $sm->bindParam(":search", $search, PDO::PARAM_STR);

so that it always binds the prepared statement with the search field. Although if empty $search will be replaced with string %% hence only returning values equal to string %% which seems a bit odd, as i believe you would prefer a LIKE statement there, although that is a different issue than this.