php - 构建动态 PDO Mysql 查询

Question

我尝试使用以下三个选项来过滤结果列表:

主类别、子类别和搜索

其中两个类别选项是下拉列表，搜索是文本框。

这是迄今为止我的代码:

files.php

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

//过滤结果的形式:

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

//调用函数来检索结果:

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

//循环结果:

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

//这是检索结果的函数:

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

我收到的错误是:

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

file_functions.php 的第 74 行正是它的执行位置。

谁能看出我哪里出错了？

Answer 1

如果这只发生在您的搜索字符串为空时，那是因为:

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

仅当$search 变量不是空字符串时才绑定(bind)参数。要解决此问题，您可以将其修改为:

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

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

以便它始终将准备好的语句与搜索字段绑定(bind)。虽然如果空 $search 将被字符串 %% 替换，因此仅返回等于字符串 %% 的值，这看起来有点奇怪，因为我相信你更喜欢 >LIKE 语句，尽管这是一个与此不同的问题。