PHP MySQL Variable Filter - "AND" Acting Like "OR"

Question

Is there a simpler method to using url variables to filter mysql results? I wrote the following code and it works to a degree and I know there has to be a simpler method, but I'm not sure where to start. I'd rather replace this one since it only half works.

$start=0;
$limit=3;

if(isset($_GET['pg']))
{
$pg=$_GET['pg'];
$start=($pg-1)*$limit;
}
else { 
$pg = 1;
}
$sql = mysql_query($query);
if(isset($_GET['signage'])) {
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE signage='1' LIMIT $start, $limit");
}
elseif(isset($_GET['certifications'])) {
    $certification = $_GET['certifications'];
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE certifications='$certification' LIMIT $start, $limit");
}
elseif(isset($_GET['state'])) {
    $state = $_GET['state'];
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE state='$state' LIMIT $start, $limit");
}
elseif(isset($_GET['certifications'], $_GET['signage'])) {
    $certification = $_GET['certifications'];
    $signage = $_GET['signage'];
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE signage='$signage' AND certifications='$certification' LIMIT $start, $limit");
}
elseif(isset($_GET['certifications'], $_GET['signage'], $_GET['state'])) {
    $certification = $_GET['certifications'];
    $signage = $_GET['signage'];
    $state = $_GET['state'];
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE signage='$signage' AND certifications='$certification' AND state='$state' LIMIT $start, $limit");
}
else {
    $result = mysqli_query($conn,"SELECT * FROM pilotOperators LIMIT $start, $limit");
}
while($row = mysqli_fetch_array($result))
{
echo "\n <table border='0' class='resultTable' width='75%'> \n";
echo "<tr> \n";
echo "<td width='120px'>ID: </td> \n";
echo "<td>" . $row['id'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Name: </td> \n";
echo "<td>" . $row['name'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Phone: </td> \n";
echo "<td>" . $row['phone'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Alt. Phone: </td> \n";
echo "<td>" . $row['alt_phone'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Fax: </td> \n";
echo "<td>" . $row['fax'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Email: </td> \n";
echo "<td>" . $row['email'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Website: </td> \n";
echo "<td><a href='" . $row['website'] . "' target='_blank'>" .  $row['website'] . "</a></td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>City: </td> \n";
echo "<td>" . $row['city'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>State: </td> \n";
echo "<td>" . $row['state'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Certifications: </td> \n";
echo "<td>" . $row['certifications'] . "</td> \n";
echo "</tr> \n";
echo "<tr> \n";
echo "<td>Top Sign: </td> \n";
echo "<td>";
if($row['signage'] = 1) {
 echo "Has Top Sign";
}
else {
 echo "Top Sign Not Listed";
}
echo "</td> \n";
echo "</tr> \n";
echo "</table> \n\n";
}
$rows = mysqli_num_rows(mysqli_query($conn, "SELECT * FROM pilotOperators"));
$total=ceil($rows/$limit);
echo "<div id='paginationLinks'> \n";
if($pg>1)
{
echo "<a href='?pg=".($pg-1)."' class='paginationButton'>PREVIOUS</a> \n";
}
if($pg!=$total)
{
echo "<a href='?pg=".($pg+1)."' class='paginationButton'>NEXT</a> \n";
}

echo "<ul class='page'> \n";
 for($i=1;$i<=$total;$i++)
{
if($i==$pg) { echo "<li class='current'>".$i."</li> \n"; }

else { echo "<li><a href='?id=".$i."'>".$i."</a></li> \n"; }
}
echo "</ul> \n";
echo "</div> \n";
mysqli_close($con);

When I said that this code kinda works allow me to explain. When I have multiple variables in my url(I.E. ?signage=VALUE&certifications=VALUE) the code acts like it has OR in place of AND.

Like This: $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE signage='$signage' OR certifications='$certification' LIMIT $start, $limit");

Instead Of This: $result = mysqli_query($conn,"SELECT * FROM pilotOperators WHERE signage='$signage' AND certifications='$certification' LIMIT $start, $limit");

If my URL contains the following &signage=1&certifications=washington it is not only showing me results that have both of those, but it is showing me results of people with Washington certifications OR where signage=1.

I don't have many variables that will be passed. It should only be 3, but my method just seems complicated.

What can I do to make this simpler and why does my AND act like OR when attempting to filter the results.

Answer 1

Q: What can I do to make this simpler?

A: You could do something like the following, to conditionally append a predicate to the SQL statement, and defer running the statement until it's fully built. It looks like you only need to run one SQL statement, so there only needs to be one call to mysqli_query. Populate a variable to contain populate with the SQL text, and have mysqli_query reference the variable.

NOTE: Any potentially unsafe values must be properly escaped to thwart SQL Injection vulnerabilities. (Note the use of the mysqli_real_escape_string function around every value that's being included in the SQL text.)

For example:

# start of SQL statement
$sql = "SELECT * FROM pilotOperators WHERE 1=1";

# append condition for signage (if required)
if(isset($_GET['signage'])) {
   $sql .= " AND signage='1'";
}

# append condition for certifications (if required)
if(isset($_GET['certifications'])) { 
   $certification = mysqli_real_escape_string($conn,$_GET['certifications']);
   $sql .= " AND certifications='$certification'"
}

# append condition for state (if required)
if(isset($_GET['state'])) {
   $state = mysqli_real_escape_string($conn,$_GET['state']);
   $sql .= " AND state='$state'";
}


# append ORDER BY and LIMIT 
$sql .= " ORDER BY 1,2,3";
$sql .= " LIMIT " . mysqli_real_escape_string($conn,$start) 
            . "," . mysqli_real_escape_string($conn,$limit);

#echo "SQL=" . $sql; // for debugging

$result = mysqli_query($conn,$sql);

I've also added an ORDER BY clause so the rows will be returned in a deterministic sequence; replace the positional references to the columns you want.

The WHERE 1=1 is a dummy placeholder, that will always evaluate to TRUE. The purpose of that is so that any subsequent predicates can be added with an AND and we don't need to muck with figuring out whether this is the first predicate being added, and we need to use WHERE instead of AND.

The leading space in each part that's being appended is important. (I just find it easier to handle that as a leading character, rather than remembering to add it at the end, but you could have the space at the end of each part instead).

---

Q: Why is my AND acting like OR?

A: It's not, you're never getting to a statement that has an AND in it. If "certifications" is set, you're executing a SQL statement that contains only a predicate on the certifications column. You never reach the elseif for "certifications" and "state" both being set.

---

FOLLOWUP

There's an error in the code above, a missing semicolon (statement terminator) is missing from one line:

   $sql .= " AND certifications='$certification'" ;
                                                  ^

---

The .= operator appends the value on the right to the current value of the variable on the left. That is, this statement

$x .= "foo";

does the same thing as this statement.

$x = $x . "foo";

---

The variables $certification and $state aren't really necessary. You could remove these two lines:

   $state = mysqli_real_escape_string($conn,$_GET['state']);
   $sql .= " AND state='$state'";

and replace it with this:

   $sql .= " AND state='" . mysqli_real_escape_string($conn,$_GET['state']) . "'";

But breaking that into two separate lines does make it easier to debug and spot mistakes, easy to add an echo or var_dump for debugging...

   $state = mysqli_real_escape_string($conn,$_GET['state']);
   echo "state=" . $state ;
   var_dump($state);
   $sql .= " AND state='$state'";

Answer 2

That is not best thing you should really do, but just like an idea:

$query = 'SELECT * FROM pilotOperators ';

$filters = array('signage', 'certifications', 'state');

$where = false;
foreach($filters as $filter) {
     if (isset($_GET[$filter])) {
         $filter_val = mysqli_real_escape_string($_GET[$filter]);
         if (! $where ) {
           $where = true;
           $query .= ' WHERE '.$filter.'= "'.$filter_val.'"';
         } else {
           $query .= ' AND '.$filter.'= "'.$filter_val.'"';  
         }
     }
}

$query .= " LIMIT $start, $limit";

$result = mysqli_query($conn,$query);

EDIT 1 Ignore some values of filter

$filters = array('signage', 'certifications', 'state');

$filters_ignore_value = array('state'=>'Select State');

$where = false;
foreach($filters as $filter) {
     if (isset($_GET[$filter])) {
         $filter_val = mysqli_real_escape_string($_GET[$filter]);
         if (isset($filters_ignore_value[$filter] && $filters_ignore_value[$filter] == $filter_val) {
             continue;
         }
         if (! $where ) {
           $where = true;
           $query .= ' WHERE '.$filter.'= "'.$filter_val.'"';
         } else {
           $query .= ' AND '.$filter.'= "'.$filter_val.'"';  
         }
     }
}

Answer 3

When I have multiple variables in my url(I.E. ?signage=VALUE&certifications=VALUE) the code acts like it has OR in place of AND

Take the example of signage AND certifications being set: the first if clause will evaluate to true, resulting in that query being executed regardless of whether certifications is set. You might consider re-ordering the if/elseif. For instance:

//check for all
if(isset($_GET['certifications'], $_GET['signage'], $_GET['state'])) {

}elseif (isset($_GET['certifications'], $_GET['signage'])){

}elseif (){

}

Alternatively, as suggested in another answer, build your query up by checking each requisite parameter