CODEWITHSUNDEEP
phpmysqlwhere-clause
ugnuku
ugnuku

Reputation: 161

Conditions check in WHERE clause

I am trying to build a query and this is its WHERE clause so far:

WHERE ( s.subject_name LIKE '%$keyword%' OR
        c.city_name LIKE '%$keyword%' OR
        o.option_name LIKE '%$option%' OR
        d.district_id = $districtId OR
        c.city_id = $cityId
       )

My actual conditions are:

These conditions come from my search page. $keyword is the value from a text box and a keyword is required to start searching. That's why I said it always has a value. Other 3 values come from select boxes that allows to select the items when they are needed.

I tried to make the query, its ok. But I am really confused with the WHERE clause. Above code is from my WHERE clause so far. But I can not get my desired result.

Upvotes: 0

Views: 133

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

If you want to achieve this in pure SQL, this is what you need (or at least it should be close enough):

WHERE (
    ( s.subject_name LIKE '%$keyword%' OR c.city_name LIKE '%$keyword%' )
    AND ('$option' = '' OR (
        o.option_name LIKE '%$option%' AND (
            '$districtId' = '' OR (
                d.district_id = $districtId AND (
                    '$cityId' = '' OR c.city_id = $cityId
                )
            )
        )
    )
)

However, this is by far sub-optimal. Instead, you should check the contents of user input in PHP and run a different query depending on this input. You could also build the query dynamically:

$query = "SELECT ...";

$query .=
    " WHERE ( ( s.subject_name LIKE '%$keyword%' OR c.city_name LIKE %$keyword%' )";
if ( !empty($option) ) {
    $query .= " AND o.option_name LIKE '%$option%'";
    if ( !empty($districtId) ) {
        $query .= " AND d.district_id = $districtId";
            if ( !empty($cityId) ) {
                $query .= " AND c.city_id = $cityId";
            }
    }
}
$query .= ')';

Upvotes: 1

Related Questions