根据过滤器优化搜索结果

问题描述：

我正在一个电子商务网站上.在显示可用手机时，用户可以通过以下过滤器过滤结果:

I am working on an e-commerce website. While displaying the available mobiles, user can filter the results by the following filters:

品牌名称

三星
诺基亚
HTC
(还有更多)

价格范围

$ 100- $ 200
$ 200- $ 300
(依此类推)

类别

Android手机
低成本手机
音乐手机
3G手机
...(还有更多)

我想使用以下GET参数生成一个SQL查询，该查询将在每次应用过滤器时执行.假设当前过滤器是Brand=Samsung，那么我的链接将是 http://xyz.com/mobiles. php?brand = samsung

I want to use the following GET parameters to generate a sql query which will execute everytime a filter is applied. Suppose the current filter is Brand=Samsung then my link will be http://xyz.com/mobiles.php?brand=samsung

对于上述过滤器，用于生成SQL查询的PHP代码如下(使用大量的if/else语句和isset()函数):

For the above filters, PHP code for generating the SQL query is as follows (using lots of if/else statements and isset() function):

$sql = "SELECT * from items ";
if(isset($_GET['brand']))
{
     $sql = $sql . "where brand=".$_GET['brand']." ";
/*similar statements*/
}

请不要继续讲上述PHP语句的准确性，我没有提到完整的代码.最后，我生成了以下将提供结果的SQL:

Please don't go on the accuracy of the above PHP statements, I have not mentioned the full code. Finally I have the following SQL generated which will provide the result:

SELECT * FROM ITEMS 
WHERE 
     BRAND=SAMSUNG;

此SQL查询将得出匹配的产品，我将在网页上相应显示结果.请回答以下问题:

This SQL query will result the matching products and I will display the results on webpage accordingly. Please answer the following questions:

在使用了上述过滤器(品牌)之后，假设还应用了价格过滤器.如何我可以知道brand过滤器已经在那里我可以将用户重定向到

After the above filters (brand), suppose price filter is also applied. How can I know that brand filter is already there so that I can redirect the user to

http://xyz.com/mobiles.php? brand = samsung& priceMin = 100& priceMax = 200

代替以下URL

http://xyz.com/mobiles.php?priceMin=100& priceMax = 200

即我怎样才能将价格标准附加到网址上?

i.e. how can i just append the price criteria to the url?

是否有可用的软件/库/代码来过滤产品?

Is there any software/library/code available to filter the products?

是否有更好的方法来过滤出产品或有更好的方法来过滤产品比我上面提到的方法生成SQL?

Is there any better way to filter out products or any better way to generate the SQL than the method I mentioned above?

我在Windows计算机上使用的是PHP，MySQL，Apache.

I am using PHP, MySQL, Apache on Windows machine.

答

我很想建立一个为每个查询参数调用一个函数的调度表.这使您可以创建安全查询参数的白名单.我还将使用参数化语句来帮助防止SQL注入(某些现有代码不受保护). PDO 使使用参数化语句变得容易.

I'd be tempted to build a dispatch table that calls a function for each query parameter. This allows you to create a whitelist of safe query parameters. I would also use parameterized statements to help guard against SQL injection (something that your existing code is not protected against). PDO makes using parameterized statements easy.

一开始似乎不需要为每个查询参数创建单独的函数，但这意味着您可以将所有条件放在单独的文件中，从而使主查询函数保持整洁.它还使将来的增强功能更易于实现.

Creating a separate function for each query parameter may seem unnecessary at first, but it means that you can put all your conditions in a separate file, thus keeping your main query function tidy. It also makes future enhancements easier to implement.

以下是一个令人费解的粗略示例.这并不是要准备好将其剪切并粘贴到应用程序中.只是为了让您了解我的意思.在真实的应用程序中，除其他外，您需要包括错误检查，并且可能会将数据库连接内容移到其他地方.

The following is an off-the-top-of-my-head rough example. It's not meant to be ready to cut and paste into an application. It's just to give you an idea of what I mean. In a real app, amongst other things, you'd need to include error checking, and would likely move the database connection stuff elsewhere.

// ** query_params.php **

function query_brand () {
    return "brand = ?";
}

function query_price () {
    return "price BETWEEN ? AND ?";
}

function query_category () {
    return "category = ?";
}

// ** product_search.php **

function search () {

    // Build a test GET array.
    $_GET = array(
        'brand'    => 'HTC',
        'price'    => array(100, 200),
        'category' => 'Android Mobiles'
    );

    // Build a dispatch table of safe query parameters.
    $dispatch = array(
        'brand'    => 'query_brand',
        'price'    => 'query_price',
        'category' => 'query_category'
    );

    // An array to hold the conditions.
    $cond = array();

    // An array to hold the bind values.
    $bind = array();

    foreach ( $_GET as $param => $value ) {
        if( isset($dispatch[$param]) ) {
            $cond[] = call_user_func( $dispatch[$param] );
            $bind[] = $value;
        }
    }

    $sql = "SELECT item, brand, price, category " .
           "FROM products";

    if( count($cond) ) {
        // Combine the conditions into a string.
        $where = implode( ' OR ', $cond );
        $sql .= " WHERE $where";
    }

    // Use PDO to connect to the database.  This should
    // probably be done somewhere else.
    $dbh = new PDO(
        "mysql:host=localhost;dbname=$dbname", $user, $pass,
    );

    // Prepare the SQL statement.
    $stmt = $dbh->prepare( $sql );

    // Execute the statement, passing the values to be
    // bound to the parameter placeholders.
    $stmt->execute( $bind );

    // Fetch and return results...
}

根据过滤器优化搜索结果

相关推荐