PHP Criteria Fliter

Question

I am working on building a PHP based filter that will narrow down a search based on criteria specified by drop downs in the HTML file. In the MySQL file I have a column for a image_url, client, and service. When the search is complete I would like to display the image, client name, and services.

Currently I have the PHP setup to seach based on one specific set of criteria. I would like for it to take into account the extra set of perameters. How can I do this? Thanks in advance!

This is what I have so far.

HTML

<!DOCTYPE HTML>
<html>
 <head>
  <meta charset="UTF-8" />
  <title>Filter</title>
 </head>
<body>
 <br />
<form action="filter.php" method="post">
  Search by:
  <br />
Client:
<select name="client">
  <option value="any">- Any -</option>
  <option value="1">Test 1</option>
  <option value="2">Test 2</option>
  <option value="3">Test 3</option>
</select>

 Services: 
<select name="services">
  <option value="any">- Any -</option>
  <option value="1">Test 1</option>
  <option value="2">Test 2</option>
</select>

 <input type="submit" value="Submit" />
</form>
</body>
</html>

PHP

<?php
            // declare sting variable and set value to link to index.html


            // connect to database and select database 'directory'

            $con = mysql_connect("localhost","username","123456");
            if (!$con)
            {
                  die('Could not connect: ' . mysql_error());
            }

            mysql_select_db("filter", $con);

            $name = 'name';
            if (isset($_REQUEST['client']))
            {
                  $name = trim($_REQUEST['client']);
            } else {
                  echo "Sorry, no search criteria.";

                  exit;
            }

            // contains the string input by the user on the name.html page

            $query = "SELECT * FROM filter WHERE client LIKE '".mysql_real_escape_string($client)."%'";

            $result = mysql_query($query) or die( "Problem executing: ". $query . " " . mysql_error() );

            // if no results are returned from the query, give error message and exit

            if (mysql_num_rows($result) == 0) {
                  echo "Sorry, no matching results.";
                  exit;
            }

            while ($row = mysql_fetch_assoc($result)) {



                       echo "<img src="<?php echo $row["image_url"]; ?>"/> ;
                        echo "<b>Client: </b>";
                  echo $row["client"] ;
                  echo  "&nbsp;";
                  echo "<b>Services: </b>";
                  echo $row["services"];
                  echo "<br>";
                  echo "<br>";

            }
            ?>

score 4 · Accepted Answer · edited May 23 '17 at 12:20

If I understand the question correctly, you are after something like this:

$select = 'SELECT image_url, client, service from filter';
$criteria = array_filter(
    filter_input_array(
        INPUT_POST, 
        array(
            'client' => FILTER_VALIDATE_INT,
            'services' => FILTER_VALIDATE_INT
        )
    )
);
if (!empty($criteria)) {
    array_walk($criteria, function(&$val, $column) {
        $val = sprintf('%s = %d', $column, $val);
    });
    $select .= ' WHERE ' . implode(' AND ', $criteria);
}

Explanation

When you submit your form, your $_POST array will look like this for example:

array(
    'client' => 'any',
    'services' => 1
);

Note that you do not want to use $_REQUEST unless you understood the order in which it is populated.

Ok, so your $_POST array contains the criteria you want to add to your base query:

$select = 'SELECT image_url, client, service from filter';

I'm not sure why you used LIKE when you are going to use the fixed values from the Select options, so let's transform the $_POST array to a simple WHERE condition only. Since you only want the values the base query can accept, we need to do some filtering first:

$criteria = array_filter(
    filter_input_array(
        INPUT_POST, 
        array(
            'client' => FILTER_VALIDATE_INT,
            'services' => FILTER_VALIDATE_INT
        )
    )
);

The filter_input_array will pull the values for client and service from the ´$_POST´ array and check whether their values are integers. The array_filter function takes care of removing any elements for which the validation failed. For the example array above, the $criteria variable will now contain

array(2) {
  ["services"]=>
  string(1) "1"
}

As you can see, 'any' got stripped because it is not an integer. Cool, because now all you got to do is array_walk it

if (!empty($criteria)) {
    array_walk($criteria, function(&$val, $column) {
        $val = sprintf('%s = %d', $column, $val);
    });
    $select .= ' WHERE ' . implode(' AND ', $criteria);
}

This code looks much more complicated than it really is: if there is any values in $criteria, we overwrite the values to columnname = value where value can only be a number. You'll end up with

Array
(
   [services] => services = 1
)

After that we append those values to the $select by imploding each value with AND.

Your $select variable will then contain

SELECT image_url, client, service from filter WHERE services = 1

If your $_POST array contained for example:

$array = array(
    'client' => '3',
    'services' => 1,
);

the resulting $select would be:

SELECT image_url, client, service from filter WHERE client = 3 AND services = 1

If your $_POST array contained two 'any' values, $select would just contain the base query.

The code snippet above can easily be extended to contain additional criteria. You just need to add the allowed keys to the filter function. The remaining code works with an arbitrary amount of array values in $criteria as long as the criteria values are numeric.

If your needs get more complex, for instance because you need to add OR, LIKE and IN in the condition, consider using a query builder instead of hand coding it (not that it would be difficult).

Is this correct? http://bit.ly/xdx4Ni Its a PDF file of the code. It was had to many characters to paste. I originally used like because I have modified my code from a directory search I created. Also to be clear I need to modify columns client and services to be numbers instead of words. Last here is an example of what I am try to achieve. http://bit.ly/wNmzsn . In the mean time I am going to read over this again & try and understand what is going on a little bit better. I haven't seen array_walk or imploding so I think I need to read up on those terms. Thanks for your help. — thebradnet, Jan 31 '12 at 16:42
@thebradnet you have to query the database with $select before you can fetch_assoc from $result. apart from that, it looks like it could work. — Gordon, Jan 31 '12 at 16:55

score 0 · Answer 2 · answered Jan 30 '12 at 20:01

0

You could try adding more code to your SQL sentence, for example.

if(!empty($services)){
    $query .= " AND service='{$service}'";
}

answered Jan 30 '12 at 20:01

gustyaquino

767
1
7
28

score 0 · Answer 3 · answered Jan 30 '12 at 20:04

I did this for one of my sites. Here is the code I had wrote.

function select($sql) 
{
        if(isset($_SESSION['Search'])) 
        {
                $Search = $_SESSION['Search'];
                unset($_SESSION['Search']); // Remove it right away

                $visibleFields = $Search->getVisibleFields();
                $keywords = $Search->getKeywords();

                $sql .= " AND (";

                for($i = 0; $i < count($keywords); ++$i) 
                {
                        for($j = 0; $j < count($visibleFields ); ++$j) 
                        {
                                $sql .= $visibleFields[$j]. " LIKE '%$keywords[$i]%'";
                                $sql .= " OR ";
                        }
                }

        $sql= substr($sql, 0, -4) . ")"; 
        }

        if(isset($_POST['sortby']) && isset($_POST['sortby']) != null) 
        {
                $sql .= " ORDER BY " . $_POST['sortby'];                
        }

        $this->query($sql);
        return $this->getAffectedRows();
}

I have passed a list of keywords and then you just append them to your sql query. Works for me like a charm. This was used for doing a search in an already returned list of information

score 0 · Answer 4 · answered Jan 30 '12 at 20:04

You need to modify your query. Here's a quick example:

$query  = "SELECT * FROM filter WHERE ";
$query .= "client LIKE '".mysql_real_escape_string($_POST['client'])."%'";
if(!empty($_POST['services']){
  $query .= " AND services LIKE '".mysql_real_escape_string($_POST['services'])."%'";
}

score 0 · Answer 5 · answered Jan 30 '12 at 20:09

try doing something like

if($client != "any"){
$client_query = "client LIKE '$client%'";
}

if($services != "any"){
$services_query = "services LIKE '$services%'";
}

if($client_query && $services_query){
$query = "WHERE $client_query AND $services_query";
}
elseif($client_query && !$services_query){
$query = "WHERE $client_query";
}
elseif(!$client_query && $services_query){
$query = "WHERE $services_query";
}

// DO THE SEARCH
$query = "SELECT * FROM filter $query";

If any is selected for both then it will retrieve all records

PHP Criteria Fliter

5 Answers5

Explanation