Multi-function PHP code - can't quite seem to get it to work

Question

I am having quite a bit of difficulty getting some functions of a multi-functional tool to work. I want to be able to sort by name or catalog number, filter by category, or do an open-ended search. Here is the current code:

$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;
}

Later in the page, the following code is called upon:

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

Any assistance in this matter would be greatly appreciated.

Answer 1

You need to change that PHP ASAP. See the comments above about SQL injection. Your code is dangerous.

As for the filtering, let PHP do the heavy lifting for you by dynamically building the query. When you find yourself repeating stuff - e.g. your individual queries - that's a good indicator that you are on the wrong path. I suggest looking into a framework like Laravel. It takes care of a lot of boilerplate like this. Its Query Builder is terrific.

If you want to do this using plain PHP, then do something like:

$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 ...';

Note that I'm using mysql_real_escape_string() to sanitize the user inputs simply because I suspect you're still using mysql_ functions. You should be using mysqli_ functions at a minimum.

Using the strategy above you can filter by as many (or few) variables as you'd like.