Building a better search query php/mysql

Question

I'm currently trying to put together a fairly simple search form - all checkboxes - but running into issues putting together the query. I'm able to return results if 1 location is selected, 1 experience or only 1 language. If I select a combination of any my results are spotty at best. My intention is to return all results for users with:

experience a OR b OR c AND location a OR b OR b OR d AND languages a OR b AND approved

Right now, if I only select a bunch of locations, no other criteria, I get no results.

What type of query should I be looking at when trying to search through 20+ languages, 50+ locations, and a few other requirements? And how should I go about building it? Am I on the right track?

$adv_search_query = "SELECT 
users.*, general.*, languages.*, experience.*
FROM users
    LEFT OUTER JOIN languages ON users.user_id = languages.user_id
    LEFT OUTER JOIN general ON users.user_id = general.user_id
    LEFT OUTER JOIN experience ON users.user_id = experience.user_id
WHERE (";

if(!empty($_POST['location'])) {
    foreach($_POST['location'] as $location) {
        $location_input = " general.neighborhood LIKE '%" . $location . "%' OR";
    }
    $adv_search_query .= trim($location_input, 'OR');
    $adv_search_query .= ") ";
}

if(!empty($_POST['languages']) && !empty($_POST['location'])) {
    $adv_search_query .= "AND (";
}

if(!empty($_POST['languages'])) {
    foreach($_POST['languages'] as $language) {
        $language_input = " languages." . $language . " = 1 OR";
    }
    $adv_search_query .= trim($language_input, 'OR');
    $adv_search_query .= ") ";
}

if(!empty($_POST['experience']) && !empty($_POST['location'])) {
    $adv_search_query .= "AND (";
}

if(!empty($_POST['experience'])) {
    foreach($_POST['experience'] as $exp) {
        $exp_input = " experience." . $exp . " = 1 OR";
    }
    $adv_search_query .= trim($exp_input, 'OR');
    $adv_search_query .= ") ";
}

if (isset($_POST["approved"])) {
    $approved = " users.approved = 1 OR";
} else { $approved = ""; }

if (isset($_POST["pending"])) {
    $pending = " users.approved = 2 OR";
} else { $pending = ""; }

if (isset($_POST["incomplete"])) {
    $incomplete = " users.approved = 0 OR";
} else { $incomplete = ""; }

if(isset($_POST['approved']) || isset($_POST['pending']) || isset($_POST['incomplete'])) {
    $status_input = "AND (" . $approved . " " . $pending . " " . $incomplete . "";
    $adv_search_query .= trim($status_input, 'OR');
    $adv_search_query .= ") ";
}

$adv_search_query .= "AND users.admin_level = 0";

Tables

table.users
user_id  first_name   last_name   admin_level   user_approved  
1        nick         jones       0             1      
2        johnny       rocket      0             1      
3        sally        fields      0             2    

table.general
user_id  city        state      zip     neighborhood
1        baltimore   maryland   00125   hamsterdam
2        lakeland    maine      11542   treemont
3        sonic       new york   11763   highville

table.languages
user_id  french  german  italian  spanish
1        0       1       0        1
2        0       0       1        1
3        1       1       1        1

table.experience
user_id  waldorf  kumon  homeschooling 
1        0       1       0
2        0       0       1
3        1       1       1

Start using IN rather than OR (it'll make your life easier) and won't lead to issues with confusing queries mixing AND and OR — Mark Baker
– Mark Baker, Commented Oct 15, 2014 at 15:37
Thanks will do. That will still give me the same shoddy results though unless there is another usage rather than just replacing OR. — Klav
– Klav, Commented Oct 15, 2014 at 15:47

gmarintes · Accepted Answer · 2014-10-15 16:10:17Z

1

First, be aware that your code is susceptible to SQL injection in the:

 general.neighborhood LIKE

part.

In this type of SQL query building, "array()" and "implode" are your friends:

$experience_valid_values = array('exp1', 'exp2');
$experience_conditions = array();
if(!empty($_POST['experience'])) {
    foreach($_POST['experience'] as $exp) {
        if (in_array($exp, $experience_valid_values)) {
            $experience_conditions[] = 'experience.' . $exp . '=1';
        }
    }
}

$language_valid_values = array('english', 'japanese', 'spanish', 'chinese');
$language_conditions = array();
if(!empty($_POST['language'])) {
    foreach($_POST['languages'] as $language) {
        if (in_array($language, $language_valid_values)) {
            $language_conditions[] = 'language.' . $language . '=1';
        }
    }
}

$conditions = array();
if (!empty($experience_conditions)) {
    $conditions[] = '(' . implode(' OR ', $experience_conditions) . ')';
}
if (!empty($language_conditions)) {
    $conditions[] = '(' . implode(' OR ', $language_conditions) . ')';
}

$sql = 'SELECT *
        FROM users
            LEFT OUTER JOIN experience ON users.user_id = experience.user_id
            LEFT OUTER JOIN languages ON users.user_id = languages.user_id
        WHERE
       ';
$sql .= implode(' AND ', $conditions);

Using "array()" and "implode" will make your code shorter and easier to read. This is just a short example I am hoping will give you the idea.

answered Oct 15, 2014 at 16:10

gmarintes

1,30812 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Klav Over a year ago

Nice! This is pretty clean. Thanks. I'll get modifying and see how it turns out. Did you have any thoughts into my locations keyword search? I'm sure I can go with the same method but $location_input = " general.neighborhood LIKE '%" . $location . "%' OR";seems to be an issue

gmarintes Over a year ago

You will be imploding 'general.neighborhood LIKE ?' and you'll need to $neighborhood_param_values[] = '%' . $location . '%'; You need to create a prepared statement from $sql and pass $neighborhood_param_values when you execute it

Klav Over a year ago

Thanks -- giving this a go

gmarintes Over a year ago

Have fun! Please accept the answer to remove this from unanswered list.

Collectives™ on Stack Overflow

Building a better search query php/mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related