php - 多功能 PHP 代码 - 似乎无法让它正常工作

Question

我在使用多功能工具的某些功能时遇到了相当大的困难。我希望能够按名称或目录号排序、按类别过滤或进行开放式搜索。这是当前代码:

$sort = ($_POST['sort']) ? $_POST['sort'] : 'name';
$order = ($_POST['order']) ? $_POST['order'] : 'asc';

if($_POST['Search'])
{
    $search = ($_POST['Search']);
    $query_compounds = "select * from compounds where name like'%$search%' or catalog_number like'%$search%' or synonyms like'%$search%' or cas_number like'%$search%' or formula_weight like'%$search%' or molecular_formula like'%$search%'";
}
else if($_POST['category']) {
    $category = ($_POST['category']);
    $query_compounds = "select * from compounds where category = ".$category;
}
else {
    $query_compounds = "select * from compounds order by " . $sort . " " . $order;
}

在页面的后面，调用以下代码:

<form name="SortForm" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" style="float:left;">
    <select name="sort">
        <option <?php if($sort == 'name'){ echo "selected"; }?> value="name">Name</option>
        <option <?php if($sort == 'catalog_number'){ echo "selected"; }?> value="catalog_number">Catalog Number</option>
    </select>
    <select name="order">
        <option <?php if($order == 'asc'){ echo "selected"; }?> value="asc">Ascending</option>
        <option <?php if($order == 'desc'){ echo "selected"; }?> value="desc">Descending</option>
    </select>
    <input name="SortForm" type="submit" id="SortForm" value="Sort">
</form>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" style="float:left;">
    <select name="category">
        <option <?php if($category == 'Compounds'){ echo "selected"; }?> value="Compounds">Compounds</option>
        <option <?php if($category == 'Glucuronides'){ echo "selected"; }?> value="Glucuronides">Glucuronides</option>
        <option <?php if($category == 'Metabolites'){ echo "selected"; }?> value="Metabolites">Metabolites</option>
    </select>
    <input name="category" type="submit" id="category" value="Select">
</form>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="GET" style="float:left;">
    <input id="Search" type="text" placeholder="Type here">
    <input id="Search" type="submit" value="Search" name="Search">
</form>

对于此事的任何帮助，我们将不胜感激。

Answer 1

您需要尽快更改 PHP。请参阅上面有关 SQL 注入(inject)的评论。你的代码很危险。

至于过滤，让 PHP 通过动态构建查询来为您完成繁重的工作。当你发现自己在重复一些事情时——例如您的个人查询 - 这是一个很好的指标，表明您走在错误的道路上。我建议研究像 Laravel 这样的框架。它会处理很多这样的样板文件。它的查询生成器非常棒。

如果您想使用纯 PHP 来执行此操作，请执行以下操作:

$where = array();

if ( isset($_POST['Search']) ) {
    $search = mysql_real_escape_string($_POST['Search']);
    $where['Search'] = "( name LIKE('%".$search."%') 
                        OR catalog_number LIKE('%".$search."%') 
                        OR synonyms LIKE('%".$search."%') 
                        OR cas_number LIKE('%".$search."%') 
                        OR formula_weight LIKE('%".$search."%') 
                        OR molecular_formula LIKE('%".$search."') )";   
}

if ( isset($_POST['category']) ) {
    $category = mysql_real_escape_string($_POST['category']);
    $where['category'] = "category = '".$category."'";
}

if ( count($where) > 0 ) {
    $where = ' WHERE '.implode(' AND ', $where);    
}

$sql = 'SELECT * FROM compounds'.$where.' ORDER BY ...';

请注意，我使用 mysql_real_escape_string() 来清理用户输入只是因为我怀疑您仍在使用 mysql_ 函数。您应该至少使用 mysqli_ 函数。

使用上述策略，您可以根据需要过滤任意数量(或更少)的变量。