Selecting all values if nothing is selected from drop-down list

Question

I have a from by which I generate data after submitting the drop-down values of form to another page.

<form method="post" action="details.php">

<select name="Station">
<option value="All">All</option>
<option value="Home">Home</option>
<option value="Office">Office</option>
</select>

<select name="Section">
<option value="All">All</option>
<option value="Living">Living Room</option>
<option value="Dinning">Dinning Room</option>
<option value="Bathroom">Bathroom</option>
</select>

<input type="submit">
</form>

Based on the above form I have Station and Section Columns in a MySQL database with the values that I mentioned.

It works fine if I select Home from Station and Bathroom from Section, But if I select All from Station and Bathroom from Section then I get an error because I don't have a record with the value All in column named Station. Instead I have records with values Home and Office (by All I mean that both Home and Office should be generated).

My question is how to generate details of both stations when I select All from drop-down list.

In my details.php page I have:

$execItems = $conn->query("SELECT StationName, SectionName FROM profiles WHERE Station = '$Station' AND Section = '$Section'");

It works fine if I select Home or Office from Station, but if I select All from Station then it won't work because there is no value by the name of All in Station column (By All I meant that both Home and Office should be selected.).

Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 17 '17 at 19:56
Thanks, I am using this just for learning purposes for now afterward i will use them. By the way do you know any solution for my question. :) — Tesla, Apr 17 '17 at 20:01
Strawberry did give you a clue. With empty values for the two dropdowns, the SQL statement would be ```SELECT StationName, SectionName FROM profiles WHERE Station = '' AND Section = ''``` If there isn't a value, don't test for it in the WHERE clause. — Sloan Thrasher, Apr 17 '17 at 20:24
@SloanThrasher ok i changed the value of All from drop-down to empty, and now when i select all and hit the submit button still i don't receive any data. SELECT StationName, SectionName FROM profiles WHERE Station = '' AND Section = '' The result is blank. — Tesla, Apr 17 '17 at 20:32
You could store your WHERE statement in another variable and concatenate (or not) depending on the value chosen. You could also do something like `WHERE (station = '$Station' OR '$Station' = 'ALL') AND (Section = '$Section' OR '$Section' = 'ALL')`. There's a few ways to skin this cat in both php and your sql. — JNevill, Apr 17 '17 at 21:17
@JNevill i don't have any data with ALL. I just have Home and Office in Station and i want that if none of them where selected and then both Home and Office data should be loaded. — Tesla, Apr 18 '17 at 07:21
you need dynamic where clause based on input is empty or not — JYoThI, Apr 18 '17 at 12:17

JNevill · Answer 1 · 2017-04-18T13:13:40.190

0

While a dynamically written WHERE clause would be a better way to go, a quick fix is to adjust the WHERE clause to account for ALL being passed in from the form.

WHERE (station = '$Station' OR '$Station' = 'ALL') AND (Section = '$Section' OR '$Section' = 'ALL')

If someone is to choose, for station the form value ALL and the value Dining for the Section form, then this WHERE clause becomes:

WHERE (station = 'ALL' or 'ALL' = 'ALL') AND (Section = 'Dining' or 'Dining' = All)

That first OR will generate a TRUE because 'All' = 'All' and the second will return TRUE in the event that the record being tested has a section value of Dining.

edited Apr 18 '17 at 13:13

answered Apr 18 '17 at 12:06

JNevill

46,980
4
38
63

There is no `All` value in `Station` column to retrieve data like: `SELECT StationName From profiles WHERE Station = 'All'` Instead i have `Home` and `Office` values, so i need that when `All` is selected from drop-down then both `Home` and `Office` row should be retrieved. – Tesla Apr 18 '17 at 12:27
I've updated this answer to better explain what I was talking about in the comments. It's a little odd to be passing in hard coded values for testing by the database, which is why a dynamically generated statement is a better choice, but this will suffice as well. – JNevill Apr 18 '17 at 13:15

score 0 · Answer 2 · answered Apr 18 '17 at 12:30

0

you need to build dynamic where clause based on input like this

<?php

 $where ="1=1";

 if(isset($_POST['Station']) && !empty($_POST['Station']) && $_POST['Station'] !='All')
  {
      $where .=" and station = '$Station'";
  }

  if(isset($_POST['Section']) && !empty($_POST['Section']) && $_POST['Section'] !='All')
  {
      $where .=" and  section= '$section'";
  }

  "SELECT StationName, SectionName FROM profiles WHERE".$where

answered Apr 18 '17 at 12:30

JYoThI

11,977
1
11
26

I used it like this : [link](https://jsfiddle.net/4sgh3co4/) but i got error `Fatal error: Call to a member function fetch_array() on a non-object in line 93` which is `while($infoItems = $execItems->fetch_array()){` – Tesla Apr 18 '17 at 13:14
echo "SELECT Station, Section FROM profiles WHERE".$where; exit; do like this before execute to know the error in your query @Ahmad – JYoThI Apr 19 '17 at 04:01

Selecting all values if nothing is selected from drop-down list

2 Answers2