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

Barmar · Accepted Answer · 2023-05-02T21:19:25.593

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";

edited May 02 '23 at 21:19

answered May 02 '17 at 20:03

Barmar

741,623
53
500
612

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
@mickmackusa Was `??` a thing when I wrote the answer (exactly 6 yeares ago today) – Barmar May 02 '23 at 15:03
@mickmackusa Prepared statements with dynamic SQL is a little tricky if they're use mysqli, not as hard with PDO. Since the code to make the SQL call is missing, it's hard to show that. Since this code is whitelisting the values, it's less critical, there's no possibility of SQL injection. – Barmar May 02 '23 at 20:58
1

Of course it's implementing validation -- the value it's inserting is the same one in the `switch`. So it's validating that the value is either 0 or 1. – Barmar May 02 '23 at 21:17

Switch case with two SQL requirements

1 Answers1