Switch case with two SQL requirements

Question

I have a form select, where I can choose between a weekday and and true/false. I made a switch case statement, where some is working. With my query I can echo out wednesday and if football is 0 correctly. But if the weekday is wednesday and football is 1 I still get the result from 0.

Am I using the switch statement incorrectly?

weekday can be we wednesday or saturday

football can be 0 or 1 (0 = false, 1 = true)

HTML

<select name="weekday" class="form-control selectpicker" >
        <option value=" ">Select Weekday</option>
        <option value="wednesday">Wednesday</option>
        <option value="saturday">Saturday</option>
</select>
<select name="football" class="form-control selectpicker" >
        <option value=" " >Practice</option>
        <option value="1">Yes</option>
        <option value="0">No</option>
</select>

PHP

$sql = "SELECT id, weekday, football FROM footballTable";

      if(isset($_POST['weekday'], $_POST['football'])) {
        switch($_POST['weekday'], $_POST['football']){
          case 'wednesday':
              $sql .= " WHERE weekday LIKE 'Wednesday' AND football = '0' OR football = '1'";
          break;
          
          
          case 'saturday':
              $sql .= " WHERE weekday LIKE 'Saturday'";
          break;

        }
      }
      $sql .= " ORDER BY RAND ( ) LIMIT 3";

You have `case 'wednesday':` twice. Only the first one will run. — Barmar, May 02 '17 at 19:32
Thank you for the comments. I just updated the code. Is it a possibility to use an OR between 0 and 1? — Mimi, May 02 '17 at 19:35
Are there any other possible value for `football`? If not, why do you need to test them? And when you mix `AND` and `OR` you should use parentheses to make sure it's grouped the way you want. See http://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter — Barmar, May 02 '17 at 19:36
Why do you write `LIKE 'Wednesday'` instead of `= 'Wednesday'`? `LIKE` is used when you want to match a pattern. — Barmar, May 02 '17 at 19:39
@Barmar: I tried to use your suggestion, but with this statement I get everything printed out that contains wednesday, not matter if the football is 0 or 1. I changed `LIKE` to `=` now. — Mimi, May 02 '17 at 19:43
You're still not using `$_POST['football']` so you only select the rows that match the user's choice. — Barmar, May 02 '17 at 19:45
@Barmar: I tried to edit my code again. Football should be used now, right? I cannot understand the link you posted: "Search Form with One or More (Multiple) Parameters ". I cannot see anything with switch case in that threat. There is used if statements and prepared statements. Not anything with Switch case. — Mimi, May 02 '17 at 19:54
My link just shows how to build the `WHERE` clause dynamically. Why does it matter if you use `switch` or `if`? — Barmar, May 02 '17 at 19:57
Putting `$_POST['football']` into the `switch` statement still doesn't make it use it in deciding how to create the query. You want to make it use `AND football = 0` when `$_POST['football'] == '0'`, or `AND football = 1` WHEN `$_POST['football'] == '1'`, right? — Barmar, May 02 '17 at 19:58
You need one `switch` statement for `$_POST['weekday']`, and another one for `$_POST['football']`. — Barmar, May 02 '17 at 19:59

score 1 · Accepted Answer · answered May 02 '17 at 20:03

1

You need separate switch/case statements for the two variables.

$sql = "SELECT id, weekday, football FROM footballTable WHERE 1=1";
switch (@$_POST['weekday']) {
    case 'wednesday':
        $sql .= " AND weekday = 'Wednesday'";
        break;
    case 'saturday':
        $sql .= " AND weekday = 'Saturday'";
        break;
}
switch (@$_POST['football']) {
    case '0':
    case '1':
        $sql .= " AND football = '{$_POST['football']}'";
        break;
}
$sql .= " ORDER BY RAND() LIMIT 3";

answered May 02 '17 at 20:03

Barmar

669,327
51
454
560

Thank you for the answer Barmar. I just tried to play around with your code, but I am getting 0 result printed out. I will try to play some more around with the code. I do not need the `if(isset($_POST['weekday'], $_POST['football'])) { }` ? – Mimi May 02 '17 at 20:27
The `@` prevents a warning if you try to use the unset variables. If the variables aren't set, it will not match any of the cases, so it's the same as putting `if(isset())` around the whole thing. It's just a simplification. – Barmar May 02 '17 at 20:29
Perfect. I learned a lot from you now, and I got it to work. I will make a coffee and sit and read your code through, so I make sure to understand it 100% until next time. Thank you a lot. – Mimi May 02 '17 at 20:33

Switch case with two SQL requirements

1 Answers1