SELECT ALL after WHERE condition not working

Question

I have a problem with sending and receiving data when I'm trying to use "select all options" as a value in sql query. Is this method is correct or am I doing something wrong? I was trying also "*", "ALL", "%" AND "?"....

php:

SQL = select table1 where name='$selected_options1' and instructor_name='$selected_options2'


$selected_options1=$_POST['selected_name'];

$selected_options2=$_POST['selected_instructor_name'];

html:

<INPUT TYPE=checkbox NAME='selected_name' VALUE='Mat'>Mat
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Ann'>Ann
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Peter'>Peter
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Tom'>Tom
<INPUT TYPE=checkbox NAME='selected_name' VALUE='*'>All names

<INPUT TYPE=checkbox NAME='selected_name' VALUE='Ron'>Ron
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Tim'>Tim
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Greg'>Greg
<INPUT TYPE=checkbox NAME='selected_name' VALUE='Bob'>Bob
<INPUT TYPE=checkbox NAME='selected_name' VALUE='*'>All instructors

Right now my query looks like:

("SELECT * FROM `table1` WHERE instruktor in ('$instruktor1', '$instruktor2', '$instruktor3', '$instruktor4', '$instruktor5', '$instruktor6', '$instruktor7', '$instruktor8', '$instruktor9', '$instruktor10', '$instruktor11', '$instruktor12') AND (instruktor_poczatkowy='$instruktor_poczatkowy1' OR instruktor_poczatkowy='$instruktor_poczatkowy2
 OR instruktor_poczatkowy='$instruktor_poczatkowy3 OR instruktor_poczatkowy='$instruktor_poczatkowy4 OR instruktor_poczatkowy='$instruktor_poczatkowy5
 OR instruktor_poczatkowy='$instruktor_poczatkowy6 OR instruktor_poczatkowy='$instruktor_poczatkowy7 OR instruktor_poczatkowy='$instruktor_poczatkowy8
 OR instruktor_poczatkowy='$instruktor_poczatkowy9 OR instruktor_poczatkowy='$instruktor_poczatkowy10 OR instruktor_poczatkowy='$instruktor_poczatkowy11)
  AND (klub='$klub1' OR klub='$klub2') order by nazwisko");

which still doesn't solve my problem. You can read about it in my comments.

The syntax is `SELECT column-list FROM tableName WHERE (conditions)` — ypercubeᵀᴹ, Jun 23 '12 at 14:24
All your answers below are worth of attention, I'm very grateful. I was experimenting with all your code lines... right now I am struggling with the possibility of co-egsisting multiselection/select all, only a partial selecting like few random option selected and submitted. Also with selecting empty records like: Name A, Name B, Name C or not specifying name. And than what to do If my empty checkbox still are sending zero "0" value in a query if not selected witch causing another problems. — user1248182, Jun 25 '12 at 20:28

score 0 · Answer 1 · edited May 23 '17 at 11:54

SELECT column WHERE name='*' will only select those names that are literally *. You need to drop the condition completely to select everything.

SELECT column FROM table

You can also use 1 (true) as the condition to select everything (SELECT column FROM table WHERE 1).

The logic in PHP would be something like this:

if( $selected_options1 == '*' ) {
    $condition1 = '1';
}
else {
    $condition1 = "name = '$selected_options1'";
}

if( $selected_options2 == '*' ) {
    $condition2 = '1';
}
else {
    $condition2 = "instructor_name = '$selected_options2'";
}

$query = "SELECT column1, column2, etc FROM table1 WHERE $condition1 AND $condition2";

Remember to protect yourself from SQL injection.

score 0 · Answer 2 · answered Jun 23 '12 at 14:36

Check Boxs Should Be:

<form action="checkbox-form.php" method="post">
Which buildings do you want access to?<br />
<input type="checkbox" name="formDoor[]" value="A" />Acorn Building<br />
<input type="checkbox" name="formDoor[]" value="B" />Brown Hall<br />
<input type="checkbox" name="formDoor[]" value="C" />Carnegie Complex<br />
<input type="checkbox" name="formDoor[]" value="D" />Drake Commons<br />
<input type="checkbox" name="formDoor[]" value="E" />Elliot House
<input type="submit" name="formSubmit" value="Submit" />
</form>

How will you get multivalues ...

<?php
  $aDoor = $_POST['formDoor'];
  if(empty($aDoor))
  {
    echo("You didn't select any buildings.");
  }
  else
  {
    $N = count($aDoor);
    echo("You selected $N door(s): ");
    for($i=0; $i < $N; $i++)
    {
      echo($aDoor[$i] . " ");
    }
  }
?>

Query should look like :

$SQL="select * from table1 where name in (name1,name2,name3)";

just make a string of names(comma separated ) and replace above "name1,name2,name3" from the for loop.

best luck.

bmm6o · Answer 3 · 2012-06-24T01:14:05.460

Given the query:

select table1 where name='$selected_options1' and instructor_name='$selected_options2',

there is no value you can put in $selected_options2 that means it should accept everything. You should leave out that part of the where clause in that case, so it becomes:

select table1 where name='$selected_options1'

Usually you would so this by starting with the select part of the statement and adding on where conditions as needed.

More importantly, you've designed your form so that you get back multiple values, and you aren't going to be able to simply insert a single value, right? There are two ways to query sql with this kind of constraint. Either something like:

where x=a or x=b or x=c

or

where x in (a,b,c)

There are sometimes performance considerations for which format to choose, but they are semantically equivalent. In either case, you can't simply use a hard-coded string and insert a single value.

SELECT ALL after WHERE condition not working

3 Answers3