CODEWITHSUNDEEP
phpmysql
user2178521
user2178521

Reputation: 843

Changing a select statement to return all rows under certain conditions

I have a select option where the user can choose country and state.

The user can select an "all states" option:

<select>state</select>

    search all value=''
    CA value='CA'
    NY value='NY'

The query:

WHERE country=:country && state=:state

What value should I given if I want to search all state='' <-- search all

Upvotes: 1

Views: 72

Answers (3)

user2178521
user2178521

Reputation: 843

Thanks guys, i have find a solution

if(isset($_GET['state']) && $_GET['state']!=""){
    $state=$_GET['state'];
    $state_statement=" && state=:state";

}else{
    $state_statement="";
}

WHERE country=:country".$state_statement." &&";


if(isset($_GET['state']) && $_GET['state']!=""){$SQL->bindValue(':state', $state, PDO::PARAM_STR);}

so if user not select or select all - value=""; the statement will not insert into SQL statement

Upvotes: 2

Mikhail
Mikhail

Reputation: 9007

To accommodate "search all" functionality either rewrite your query not to include the filter, or substitute value=value (always true) SQL.

Otherwise you can inject your own SQL and have a conditional WHERE clause.

" ... WHERE 
        CASE WHEN '$state'='all' THEN TRUE
             ELSE state='$state'
             END
  ... "

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

In SQL, you would typically do something like:

WHERE (:country is null or country=:country) and
      (:state is null or state=:state)

Upvotes: 0

Related Questions