Thomas Smyth Thomas Smyth - 16 days ago 5
PHP Question

SQL Advanced Search / Ignore empty queries

I'm creating an advanced search function on my website to search through. I have 5 fields that can be used to search for the specific account (Surname, Forename, Username, Registration Date and Account Rank) and an option for all fields must match or 1 (or more) fields must match. The values entered by the user are posted to a PHP file via a Javascript. The PHP file then completes the search.

Currently, the "AND" search works as blank values will not affect the result of the search as the other values still must match. However, the "OR" does not as the empty search boxes will cause other records to show as they match the "LIKE %".$Variable."%" search when the variable is blank.

I need to figure out a way for the system to ignore empty boxes, however, I'm struggling to find a suitable way that would work without causing errors in the SQL code where "OR" is used in the wrong places. So any suggestions would be nice.

Thanks in advance,
Tom

PHP

<?php
//Retrieves variables from Javascript.
$Surname = $_POST["Surname"];
$Forename = $_POST["Forename"];
$Username = $_POST["Username"];
$Joined = $_POST["Joined"];
$Rank = $_POST["Rank"];
$ANDOR = $_POST["ANDOR"];

$data = 0;

include "db/openlogindb.php";
if($DBError == true){
$data = 3;
}
else{

if($ANDOR == "AND"){
$UserSearch = "SELECT username, surname, forename, joined, rank FROM users
WHERE surname LIKE '%".$Surname."%'
AND forename LIKE '%".$Forename."%'
AND username LIKE '%".$Username."%'
AND joined LIKE '%".$Joined."%'
AND rank LIKE '%".$Rank."%'
ORDER BY surname";
}
else if($ANDOR == "OR"){
$UserSearch = "SELECT username, surname, forename, joined, rank FROM users
WHERE surname LIKE '%".$Surname."%'
OR forename LIKE '%".$Forename."%'
OR username LIKE '%".$Username."%'
OR joined LIKE '%".$Joined."%'
OR rank LIKE '%".$Rank."%'
ORDER BY surname";
}
else{
$data = 2;
}

if($data == 0){
$results = mysqli_query($conn, $UserSearch);

if(mysqli_num_rows($results) == 0){
$data = 1;
}
else{
$data = '';

while($row = mysqli_fetch_assoc($results)){
$data .= '<tr><td>'.$row['surname'].'</td><td>'.$row['forename'].'</td><td>'.$row['username'].'</td><td>'.$row['joined'].'</td><td>'.$row['rank'].'</td><td><button type="button" class="btn btn-block btn-primary btn-xs" onClick="ChangePassOpen(\''.$row['username'].'\')">Change Password</button></td></tr>';
}
}
}
}

include "db/closelogindb.php";

echo $data;
?>


HTML / Javascript
http://thomas-smyth.co.uk/admin/accountlist.php

Answer
<?php
// Create the array to store the variables
$array = array();

//Retrieves variables from Javascript.
//Where $conn is your database connection
if (isset($_POST["Surname"])) $array['surname'] = mysqli_real_escape_string($conn, $_POST["Surname"]);
if (isset($_POST["Forename"])) $array['forename'] = mysqli_real_escape_string($conn, $_POST["Forename"]);
if (isset($_POST["Username"])) $array['username'] = mysqli_real_escape_string($conn, $_POST["Username"]);
if (isset($_POST["Joined"])) $array['joined'] = mysqli_real_escape_string($conn, $_POST["Joined"]);
if (isset($_POST["Rank"])) $array['rank'] = mysqli_real_escape_string($conn, $_POST["Rank"]);
if (isset($_POST["ANDOR"])) $ANDOR = mysqli_real_escape_string($conn, $_POST["ANDOR"]);

$data = 0;

include "db/openlogindb.php";
if($DBError == true){
    $data = 3;
}
else{

//Make a variable to check for the last key in the array
$last_key = end(array_keys($array));

if($ANDOR == 'AND'){
        $UserSearch = "SELECT ";
        foreach ($array as $key => $value)
                {
                    $UserSearch .= $key;
                    if ($last_key != $key) $UserSearch .= ', ';
                }
                $UserSearch .= ' FROM users WHERE ';

                foreach ($array as $key => $value)
                {
                    $UserSearch .= $key . ' LIKE %"' . $value . '"%';
                    if ($last_key != $key) $UserSearch .= ' AND ';
                }
        }
    else if($ANDOR == 'OR'){
        $UserSearch = "SELECT ";
        foreach ($array as $key => $value)
                {
                    $UserSearch .= $key;
                    if ($last_key != $key) $UserSearch .= ', ';
                }
                $UserSearch .= ' FROM users WHERE ';

                foreach ($array as $key => $value)
                {
                    $UserSearch .= $key . ' LIKE %"' . $value . '"%';
                    if ($last_key != $key) $UserSearch .= ' OR ';
                }
        }
    else{
        $data = 2;
    }

    if($data == 0){
        $results = mysqli_query($conn, $UserSearch);

        if(mysqli_num_rows($results) == 0){
            $data = 1;
        }
        else{
            $data = '';

            while($row = mysqli_fetch_assoc($results)){ 
                $data .= '<tr><td>'.$row['surname'].'</td><td>'.$row['forename'].'</td><td>'.$row['username'].'</td><td>'.$row['joined'].'</td><td>'.$row['rank'].'</td><td><button type="button" class="btn btn-block btn-primary btn-xs" onClick="ChangePassOpen(\''.$row['username'].'\')">Change Password</button></td></tr>';
            }
        }
    }
}

include "db/closelogindb.php";

echo $data;
?> 
Comments