使用if语句构建PDO MySQL查询 - Using if statements to build a PDO MySQL query

我正试图使用PDO从MySQL数据库中检索行，但希望能够过滤/搜索结果。因此，我创建了一个函数，并使用包含筛选/搜索选项的变量。

这是我到目前为止的代码：

function getbycategory($category, $search){
    global $db;
    $base_query = "SELECT * FROM parts ";
    $category = "WHERE main_category = :category ";
    $search = "WHERE name LIKE :search ";
    if (isset($category)){
        $query = $base_query.$category;
        if (isset($search)){
            $query = $base_query.$category.$search;
        }
    }
    if (!isset($category)){
        $query = $base_query;
        if (isset($search)){
            $query = $base_query.$search;
        }
    }
    $sm = $db->prepare ($query);
    if (isset($category)){
        $sm->bindParam(':category', $category, PDO::PARAM_INT);
    }
    if (isset($search)){
        $sm->bindParam(':search', $search, PDO::PARAM_STR);
    }
    $sm->execute();
    return $sm->fetchAll();
}

然后我使用以下方法调用函数：

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

搜索/过滤器由以下内容定义：

<form method="get">
<select name="filter">
    <option <?php if($_GET['filter'] == "all" OR !isset($_GET['filter'])){echo 'selected';} ?> value="all">View All Files</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>
<input type="submit" value="Filter Results"/><br /><br />
<input type="text" name="search" <?php if(isset($_GET['search'])){echo 'value="'.$_GET['search'].'"';}?> placeholder="Enter a search term" />
<input type="submit" value="Search Results"/>
</form>

然后使用循环结果

foreach($files as $file){
echo'<div class="col-lg-" id="file-'.$file['id'].'">
        <div class="file-list-item first" id="">
            <img class="file-image" height="120px" width="180px" src="'.$file['image_url'].'" />
            <div class="file-text">
                <h3><strong>'.$file['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['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 and is awaiting review." class="fa fa-warning fa-2x"></i></span>';
                    }

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

但是，我的代码不起作用，并且没有显示任何结果。有人能提供什么建议吗？

非常感谢。

更新我已经更新了函数以解决Dave强调的问题。

function getbycategory($category, $search){
    global $db;
    $base_query = "SELECT * FROM parts ";
    $category = "WHERE main_category = :category ";
    $search = " name LIKE :search ";
    if (isset($category)){
        $query = $base_query.$category;
        if (isset($search)){
            $query = $base_query.$category.'AND'.$search;
        }
    }
    if (!isset($category)){
        $query = $base_query;
        if (isset($search)){
            $query = $base_query.'WHERE'.$search;
        }
    }
    $sm = $db->prepare ($query);
    if (isset($category)){
        $sm->bindParam(':category', $category, PDO::PARAM_INT);
    }
    if (isset($search)){
        $sm->bindParam(':search', $search, PDO::PARAM_STR);
    }
    $sm->execute();
    return $sm->fetchAll();
}

可能还有其他问题，但我注意到的是这里生成的查询：

$base_query = "SELECT * FROM parts ";
$category = "WHERE main_category = :category ";
$search = "WHERE name LIKE :search ";
if (isset($category)){
    $query = $base_query.$category;
    if (isset($search)){
        $query = $base_query.$category.$search;
    }
}

当您同时拥有$category和$search时，为：

SELECT * FROM parts WHERE main_category = :category WHERE name LIKE :search

这是无效的，它需要：

SELECT * FROM parts WHERE main_category = :category AND name LIKE :search

编辑：

发现其他问题：

您通过$category和$search:

getbycategory($category, $search){

然后覆盖它们：

     $category = "WHERE main_category = :category ";
     $search = " name LIKE :search ";

然后你绑定它们：

$sm->bindParam(':category', $category, PDO::PARAM_INT);
$sm->bindParam(':search', $search, PDO::PARAM_STR);

所以你会得到这样的查询：

SELECT * FROM parts 
WHERE main_category =  "WHERE main_category = :category " 
AND name LIKE " name LIKE :search "

对于您的同类，当您使用它时，您需要将%添加到变量中以进行通配符匹配：

$sm->bindParam(':search', '%' . $search . '%', PDO::PARAM_STR);